loop through customer file and send mail

I have this question before can some one help
How can I loop through a mail programme to send e_mail to customer

I have workbook that runs against data file let say If my data file is Toyota_Q3-2003 it split the file name for customer name where is Toyota and put it in a cell then runs and produce 3 report for that customer Ex,
Toyota Quarterly Q3-2003-All.xls
Toyota Quarterly Q3-2003-customerl.xls
Toyota Quarterly Q3-2003-error.xls

If I run my work for other customers let say FORD or MAZDA it also produce reports for above customer
Ford Quarterly Q3-2003-All.xls
Ford Quarterly Q3-2003-customerl.xls
Ford Quarterly Q3-2003-error.xls

Mazda Quarterly Q3-2003-All.xls
Mazda Quarterly Q3-2003-customerl.xls
Mazda Quarterly Q3-2003-error.xls

Question

If I have another text file (cust_mail_address) that contain all e_mail address of above customers as Example
Mazda mazda@Yahoo.com
Toyota Toyota@yahoo.com
Ford Ford@hotmail.com

how can I use my mail programe to send each of above reports to the customers example all 3 report for customer FORD send to ford@hotmail.com and all 3 reports for Toyota reports to toyota@yahoo.com

This is my mail program ( Iam usinf lotus notes mail)

Sub Main
Dim oSess As Object
Dim oDB As Object
Dim oDoc As Object
Dim oItem As Object
Dim direct As Object
Dim Var As Variant
Dim flag As Boolean

Set oSess = CreateObject("Notes.NotesSession")
Set oDB = oSess.GETDATABASE("", "")
Call oDB.OPENMAIL
flag = True
If Not (oDB.ISOPEN) Then flag = oDB.OPEN("", "")

If Not flag Then
MsgBox "Can't open mail file: " & oDB.SERVER & " " & oDB.FILEPATH
GoTo exit_SendAttachment
End If
On Error GoTo err_handler

'Building Message
Set oDoc = oDB.CREATEDOCUMENT
Set oItem = oDoc.CREATERICHTEXTITEM("BODY")
oDoc.Form = "Memo"
oDoc.subject = "This is the subject"
oDoc.sendto = "FORD@hotmail.com"
oDoc.body = "This is test text in the body of the email"
oDoc.postdate = Date

'Attaching DATABASE
Call oItem.EmbedObject(1454, "", "c:\mydata\Ford Quarterly Q3-2003-customers.xls”)
Call oItem.EmbedObject(1454, "", "c:\mydata\Ford Quarterly Q3-2003-All.xls”)
Call oItem.EmbedObject(1454, "", "c:\mydata\Ford Quarterly Q3-2003-Errors.xls”)
oDoc.visable = True
'Sending Message
oDoc.SEND False
exit_SendAttachment:
On Error Resume Next
Set oSess = Nothing
Set oDB = Nothing
Set oDoc = Nothing
Set oItem = Nothing
'Done
Exit Sub
err_handler:
If Err.Number = 7225 Then
MsgBox "File doesn't exist"
Else
MsgBox Err.Number & " " & Err.Description
End If
On Error GoTo exit_SendAttachment
End Sub

What's wrong when u complied your code??Plz give some wrong detail to us..

There is noting wrong with send mail program it works as long as I hard code the customer mail address and hard code customer data file as I have shown in the program.
MY question is
I do not want each time to hard code the customer mail address and customer data file to send the mail, I want to know how can I make the send mail program more intelligent to send the mail for customers for given report or report produce for the customer, As previous I have mentioned.
another word is that I do not want each time to edit the program and type the customer e_mail address and customer data file.
HOW CAN I do this ???

OK! I see!I recommend u to save the customer infomation into the db.If U don't save it into the db,U can save it into a text file.So When u will send mail,u can write code to read customer infomation to send mail with customer's mail address.
U can use filesystem object in the vb to read or write text file.

how can I do this could you please show me howto

Read Text From a File Using the File System sample:
Private Function ReadTextFile(fName As String) As String

Dim FSO As New FileSystemObject
Dim FSTR As Scripting.TextStream
Dim ret As Long
If FSO.FileExists(fName) Then
Set FSTR = FSO.OpenTextFile(fName)
ReadTextFile = FSTR.ReadAll
FSTR.Close
Set FSTR = Nothing
Else
ret = MsgBox("File Not Found", vbCritical)
End If
Set FSO = Nothing
End Function
' Using
' Private Sub Command1_Click()
' Text1.Text = ReadTextFile("c:\autoexec.bat")
' End Sub

U can find it in the msdn..

if you want to save it to Access, and then use ADO to manipulate it, you can also declare an Access application by adding a reference to the Access library. The access object has a method called "copyfromexcel", or something similar to that. I've got the code at home, and can submit it after work if you want to see it. It allows you to populate a table with data from Excel, and works just like importing data from an external source in Access. I use it to set up temporary "staging" tables in Access applications.

I am lost in dark
ok I have tried your codes but I get error as follow
Compiler error
User defined type not defined

As per your suggestion I have created a text file in c:\cust_mail.txt that contain

Mazda mazda@Yahoo.com
Toyota Toyota@yahoo.com
Ford Ford@hotmail.com



After all I do not know where to implement your codes with in my mail macro program, could you please advise.

if you're getting a user-defined type error, it's most likely because you didn't include the reference to the proper library - maybe the FSO library, or Outlook. Go to Project->References and check to make sure you have selected "file systems object" and "Outlook xx.x"(whatever version is on your machine)

filesystem object is in the microsoft scripting runtime reference.

Could you please show me with in my macro I have tried it but does not work