short Array

I need to write a short vb program which will look up my c:\customer directory and send select csv files by email to my customer.

My directory c:\customer has multiple csv files all csv files have different names. Each csv file has a name and this name is unique to the customer. eg. If the file name = 123.csv then i need to email these files to a customer number 1.

ie each customer has a corresponding unique number which identifies them.

When the program reads through my directory and finds multiple CSV files named 123.csv then these multiple csv files need to be sent via email to customer 1.

If csv file is named 456.csv then these csv files need to be sent via email to customer 2.

I be told i need to create a text file which contains a list of unique codes with their corresponding customer email addresses.

ie text file would read... "123, customer1, [email protected]"

Im not sure where to start with my vb code.
Can someone help me get up & running?
thanks in advance

Open your "codes" file, read in string by string and use Split with the comma delimiter to split the string into a UDT.

Use Dir on the folder containing the csv's to find the current files.

Once found, search the forums for email protocol type stuff.

Make sure you put a textbox on the form that the user can enter the location of the csv files.

Create the text file manually. Once that is done, go onto the next steps.


Create a VB program that reads in the text file and stores the information in an array.


Use a loop to step through the array.
For each line of the array, create a mail message.
Also use the DIR function to get a list of files from the directory for that customer. Add each of these files to the e-mail message as an attachment.
Send the mail message.


OR do the loop the other way around:
Use the DIR function to step through all files in the folder. For each file get the filename and use this to get the customers name and e-mail address from the array. Create the mail message, add the attachment and send the message.

wow! that was a fast! you guys are so fast at responding, it's great!..

I've created my text file, the location of the csv files will always be c:\customer.

Can you provide me with example syntax as to how the array is done.
I have already got the correct syntax for auto emailing. i just need help with reading from the directory & referring to the text file - before i email...

realli appreciate it.

Quote:

---

Originally posted by gilly
...Can you provide me with example syntax as to how the array is done....

---

Can you be more specific as to what help you need?

create a global UDT:

VB Code:

Type CUST
    strCSV as String
    strCUSTNAME as String
    strCUSTEMAIL as String
End Type
Public Dim arrCUST() as CUST
 
Dim nFile as Integer
Dim str as String
Dim temparr() as String
Dim i as Integer
 
nFile = FreeFile
Open app.Path & "\Customers.txt" for Input as #nfile
 
While not EOF(nfile)
    Line Input #nfile, str
    temparr = Split(str, ",")
    redim preserve arrCUST(i+1)
    arrCUST(ubound(arrCUST)-1).strCSV = temparr(0)
    arrCUST(ubound(arrCUST)-1).strCUSTNAME = temparr(1)
    arrCUST(ubound(arrCUST)-1).strCUSTEMAIL = temparr(2)
Loop
 
Close #nfile

... didn't test it, but it should be close.

edit: I'll let someone else handle the Dir function. I have to get some of my own work done ;)

ta guys,

martinliss.. i need vb code which will look up c:\customer & select particular csv files from that directory based on their name, then email to relevant people.

eg.
If c:\customer contains 123.csv then i need to grab these files and email only 123.csv files to customer 1.

If 456.csv exists then i need to grab these files & email to customer 2.


My text file will act as a look up - which customer to send which files to...

thanks for your help,

VB Code:

strSearchName = "c:\customer\*.csv"
f = Dir(strSearchName, vbReadOnly)
    While f <> ""
            MsgBox "Processing file: " & f
'further code
 
            f = Dir()
    Wend

ober5861 , i get a syntax error on your line - red text

Public Dim arrCUST() as CUST

any idea whats wrong?

how do i create a global UDT?

VB Code:

Type CUST
    strCSV as String
    strCUSTNAME as String
    strCUSTEMAIL as String
End Type

That is the UDT (User Defined Type).

Remove Dim from

VB Code:

Public Dim arrCUST() as CUST

And it should work.

sorry for the hassle,
now i get a compile error "invalid outside procedure...points to this line

VB Code:

nFile = FreeFile

did you Dim nfile?

freefile is just a call to find the first open filenumber.

i'm lost!! very weak at vb.
Yes ive dim nfile.

im not sure where to slot jordanchris' code into yours. heres what ive done..

VB Code:

Type CUST
    strCSV As String
    strCUSTNAME As String
    strCUSTEMAIL As String
End Type
Public arrCUST() As CUST
 
Dim nfile As Integer
Dim str As String
Dim temparr() As String
Dim i As Integer
Dim strsearchname As String
Dim nfile As Integer
 
strsearchname = "c:\customer\*.csv"
f = Dir(strsearchname, vbReadOnly)
    While f <> ""
            MsgBox "Processing file: " & f
'further code
 
            f = Dir()
    Wend
 
nfile = FreeFile
Open App.Path & "\Customers.txt" For Input As #nfile
 
While Not EOF(nfile)
    Line Input #nfile, str
    temparr = Split(str, ",")
    ReDim Preserve arrCUST(i + 1)
    arrCUST(UBound(arrCUST) - 1).strCSV = temparr(0)
    arrCUST(UBound(arrCUST) - 1).strCUSTNAME = temparr(1)
    arrCUST(UBound(arrCUST) - 1).strCUSTEMAIL = temparr(2)
Loop
 
Close #nfile

One thing - you can't have multiple files with the same name in the same directory, i.e.

Quote:

---

I've created my text file, the location of the csv files will always be c:\customer

---

That is, you can only have one 123.csv file in that directory.

All code must be in a Sub or Function and the error you are getting indicates that you have not done that. All the code from Dim nfile As Integer on down needs to be in a sub or function.

salvelinus, my mistake - i have a file called 123T.csv and 123D.csv - i need to email both of these files. ...both need to be sent to same customer.

Quote:

---

Originally posted by gilly
i'm lost!! very weak at vb.
Yes ive dim nfile.

im not sure where to slot jordanchris' code into yours. heres what ive done..

---

I'm not sure about the freefile thing... are you running VB6, Service Pack 5?

JordanChris' code goes after mine... they are 2 seperate functions.

And you will want to put my code in a sub by itself. And declare the UDT at the top of the module.

Quote:

---

Originally posted by gilly
salvelinus, my mistake - i have a file called 123T.csv and 123D.csv - i need to email both of these files. ...both need to be sent to same customer.

---

Then you may need to use something like Left() or Instr() to get only the matching part of the filename, i.e. "123", or use a wildcard. This might be simpler for you if you used a db instead of .csv files, but maybe that's not an option here.

It'd be easiest to use a wildcard with Dir:

VB Code:

Dir("C:\Customer\*123*.csv")

ober5861 will Private sub form_load() work at the top of your code? ive tried it, but it keeps telling me that ive got a compile error "invalid outside proc".
It will not allow me to write private sub... after your dim xxx as string, or before the dim part....

VB Code:

Private Sub Form_Load()
Dim nFile as Integer
Dim str as String
Dim temparr() as String
Dim i as Integer
 
nFile = FreeFile
Open app.Path & "\Customers.txt" for Input as #nfile
 
While not EOF(nfile)
    Line Input #nfile, str
    temparr = Split(str, ",")
    redim preserve arrCUST(i+1)
    arrCUST(ubound(arrCUST)-1).strCSV = temparr(0)
    arrCUST(ubound(arrCUST)-1).strCUSTNAME = temparr(1)
    arrCUST(ubound(arrCUST)-1).strCUSTEMAIL = temparr(2)
Loop
 
Close #nfile
 
End Sub

The other stuff should (UDT definition and declaration of the UDT array) should be above all of that. Also, put "Option Explicit" as the very first line of your form module.

im really sorry, i tried this, see below , & i get an error"cannot define public user type within a private module".

ive got vb 6.0 sp 5.

VB Code:

Option Explicit
 Type CUST
    strCSV As String
    strCUSTNAME As String
    strCUSTEMAIL As String
End Type
Public arrCUST() As CUST
 
Private Sub Form_Load()
Dim nFile As Integer
Dim str As String
Dim temparr() As String
Dim i As Integer
 
nFile = FreeFile
Open App.Path & "\codes.txt" For Input As #nFile
 
While Not EOF(nFile)
    Line Input #nFile, str
    temparr = Split(str, ",")
    ReDim Preserve arrCUST(i + 1)
    arrCUST(UBound(arrCUST) - 1).strCSV = temparr(0)
    arrCUST(UBound(arrCUST) - 1).strCUSTNAME = temparr(1)
    arrCUST(UBound(arrCUST) - 1).strCUSTEMAIL = temparr(2)
Loop
 
Close #nFile
 
End Sub

Then add a new module to your project and define it in there.

In a form:

VB Code:

Option Explicit
 [b]Private [/b]Type CUST
    strCSV As String
    strCUSTNAME As String
    strCUSTEMAIL As String
End Type
[b]Private [/b]arrCUST() As CUST
 
Private Sub Form_Load()
Dim nFile As Integer
Dim str As String
Dim temparr() As String
Dim i As Integer
 
nFile = FreeFile
Open App.Path & "\codes.txt" For Input As #nFile
 
While Not EOF(nFile)
    Line Input #nFile, str
    temparr = Split(str, ",")
    ReDim Preserve arrCUST(i + 1)
    arrCUST(UBound(arrCUST) - 1).strCSV = temparr(0)
    arrCUST(UBound(arrCUST) - 1).strCUSTNAME = temparr(1)
    arrCUST(UBound(arrCUST) - 1).strCUSTEMAIL = temparr(2)
[b]Wend [/b]'Loop
 
Close #nFile
 
End Sub

oops on the while loop. I meant to put a "Do" in front of the while. I always forget that for some reason. :(

brillant thanks so much for all your help.
I'll test it now..

If possible id be grateful if someone could comment the code - just so i can learn from it.

many thanks

I think this code is screaming out to be implemented using class modules :D

Woka

Quote:

---

Originally posted by gilly
...If possible id be grateful if someone could comment the code - just so i can learn from it.

---

VB Code:

Option Explicit
' Create a user-defined data type containing 3 elements
Private Type CUST
    strCSV As String
    strCUSTNAME As String
    strCUSTEMAIL As String
End Type
' Dim an array of the CUST datatype
Private arrCUST() As CUST
 
Private Sub Form_Load()
Dim nFile As Integer
Dim str As String
Dim temparr() As String
Dim i As Integer
 
' Get a file number to be used for file I/O
nFile = FreeFile
' Open a file that is assumed to be in the same folder as the exe
Open App.Path & "\codes.txt" For Input As #nFile
 
' Loop through all the lines in the file until end of file. I changed this
' to a Do loop because While/Wend is old fashioned.
Do Until EOF(nFile)
    ' Read a line into the str variable
    Line Input #nFile, str
    ' Split the line into pieces separated by spaces
    temparr = Split(str, ",")
    ' Increase the size of arrCUST as needed. It starts off at zero
    ' (which will hold 1 record) because i is initially = 0
    ' NOTE: I corrected the code from this point down
    ReDim Preserve arrCUST(i)
    ' Move the pieces of the line into arrCUST. UBound(arrCUST) calculates
    ' to the highest available record
    arrCUST(UBound(arrCUST)).strCSV = temparr(0)
    arrCUST(UBound(arrCUST)).strCUSTNAME = temparr(1)
    arrCUST(UBound(arrCUST)).strCUSTEMAIL = temparr(2)
    ' Add 1 so that the ReDim can increase the array.
    i = i + 1
Loop
 
' Close the file you opened
Close #nFile
 
End Sub

OK, here's a small demo on how to use classes in this sort of situation.
Alas, my code isn't as commented as well as Martins, I don't deserve god status :(

My code also has a SAVE function in the class Customers, and an ADD function. This allows you to add more customers at run time and then save them back to the file, thought this may be handy for you.

Some people may say that using class modules for this is overkill. But I am not a fan of large arrays of UDTs as if later down the line you have to change something then you can be pretty stuck and a rewrite is required. Class modules are slightly slower, but you won't see the difference, and they take up a little bit more resources. BUT on a plus they can have much more functionality as you can add code inside the class itself, like the Load and Save functions. This means that the code in your forms is kept to a minimum and is easier to read.
This is what classes were designed for, as well as other things, and really do help your application.
Watch, everyone will disagree with me now :D

Hope this helps.

Woka

i get script out of range when i run this code...what is wrong?

points to line ...
arrCUST(UBound(arrCUST)).strCUSTEMAIL = temparr(2)

Heres the code that im using...

VB Code:

Option Explicit
' Create a user-defined data type containing 3 elements
Private Type CUST
    strCSV As String
    strCUSTNAME As String
    strCUSTEMAIL As String
End Type
' Dim an array of the CUST datatype
Private arrCUST() As CUST
 
Private Sub Form_Load()
Dim nFile As Integer
Dim str As String
Dim temparr() As String
Dim i As Integer
Dim strsearchname As String
Dim F As String
 
' Get a file number to be used for file I/O
nFile = FreeFile
' Open a file that is assumed to be in the same folder as the exe
Open App.Path & "\accountcodes.txt" For Input As #nFile
 
' Loop through all the lines in the file until end of file.
Do Until EOF(nFile)
    ' Read a line into the str variable
    Line Input #nFile, str
    ' Split the line into pieces separated by spaces
    temparr = Split(str, ",")
    ' Increase the size of arrCUST as needed. It starts off at zero
    ' (which will hold 1 record) because i is initially = 0
    
    ReDim Preserve arrCUST(i)
    ' Move the pieces of the line into arrCUST. UBound(arrCUST) calculates
    ' to the highest available record
    arrCUST(UBound(arrCUST)).strCSV = temparr(0)
    arrCUST(UBound(arrCUST)).strCUSTNAME = temparr(1)
    arrCUST(UBound(arrCUST)).strCUSTEMAIL = temparr(2)
    ' Add 1 so that the ReDim can increase the array.
    i = i + 1
Loop
 
' Close the file you opened
Close #nFile
 
 
strsearchname = "c:\output\*.csv"
F = Dir(strsearchname, vbReadOnly)
    While F <> ""
            MsgBox "Processing file: " & F
'further code
 
            F = Dir()
    Wend
 
End Sub

The code assumes that the lines that you are reading have at least three parts, each separated by a comma. That assumption was made because it looked like you were trying to get three pieces of information from each line.

WOW !! It found the files!

I just need to figure out how to email each file depending on the customer. I get an error using the code below ..." failure on opening attachment" runtime error 32012" and it stops at .send. dont think it like *.csv

What other method can i use?

VB Code:

MAPISession1.SignOn
 
           
    With MAPIMessages1
        .SessionID = MAPISession1.SessionID
        .Compose
        .RecipAddress = "myemail@com"
        .ResolveName
        
 
        
        .AddressResolveUI = True
        
        .MsgSubject = "customer files
        .MsgNoteText = "Please find attached files
 
' Now add an attachment
        .AttachmentIndex = 0
        .AttachmentPosition = 0
        .AttachmentPathName = sFolderPath & "\*.csv"
        
 
        .Send
    End With
MAPISession1.SignOff

Errrrr...

VB Code:

.AttachmentPathName = sFolderPath & "\*.csv"

Should that be just a path without the .CSV bit on the end?
Isn't there another property to add attachments?

Woka

This line is good:
.AttachmentPathName = sFolderPath & "\*.csv"

BUT the * is no good.

Make sure sFolderPath does not end in a "\", as you are adding another one.
Make sure the * is replaced with the actual name of the file you are adding. Using DIR is a good approach to get the actual filename and to make sure the file actually exists.


And if you want to attach multiple files, you will need to do more work. The .AttachmentPosition needs to be changed for each attachment.