How to send mail from VB to multiple users

I am using this macro to send mail within my work book

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 = "username@server.com"
oDoc.body = "This is test text in the body of the email"
oDoc.postdate = Date

'Attaching DATABASE
Call oItem.EmbedObject(1454, "", "c:\missing.txt")
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


I have created an excel work book that create some report file
Example c:\mydata\Toyota Quarterly Q3-2003-customers.xls
c:\mydata\Toyota Quarterly Q3-2003-All.xls
c:\mydata\Toyota Quarterly Q3-2003-Errors.xls
c:\mydata\Ford Quarterly Q3-2003-customers.xls
c:\mydata\Ford Quarterly Q3-2003-All.xls
c:\mydata\Ford Quarterly Q3-2003-Errors.xls

I have also a file (master_customer) that contain name of customer name and there e_mail address for example
Toyota totyota@hotmail.com
Ford Ford@hotmail.com
Mazda mazda@yahoo.com

Using above mail VB program how can I send mail only for generated customer report by using master_customer file (mail address) and a copy of all reports to myself and copy for management (only reports that have *-ALL.xls) example Toyota Quarterly Q3-2003-All.xls and Ford Quarterly Q3-2003-All.xls
My mail is mymail@yahoo.com
Manager mail is manager@hotmail.com

well, I think I could find a few ways to do this... One would be to simply loop through your users and assemble a comma delimited string of email addresses. That way your email line would look like this

oDoc.sendto = strEmails

and strEmails would look like this

Someone@nowhere.com, NoWhere@somebody.com, You@my.com....etc.

it should be pretty simple to accomplish if you know who your sending emails to. You could also loop through the entire code, and assign a new email address each time, or build a strEmails string and put it in the CC Field.

Sorry but I do not understand what happened with the master file ??
I need to match for given or produced report
Example c:\mydata\Toyota Quarterly Q3-2003-customers.xls
c:\mydata\Toyota Quarterly Q3-2003-All.xls
c:\mydata\Toyota Quarterly Q3-2003-Errors.xls
c:\mydata\Ford Quarterly Q3-2003-customers.xls
c:\mydata\Ford Quarterly Q3-2003-All.xls
c:\mydata\Ford Quarterly Q3-2003-Errors.xls

How to Pickup name of customer from master_customer file with his e_maill address then pass it to mail program

sorry.. Joseph - I guess I really don't understand the question. You are trying to pull the email addresses from the master customer file based on what conditions?

Ok let me ask the question differently

I have work book that produce 3 report for given customer let me say when is run for customer Ford it produce report and when is run for Toyota is also produce report for Toyota and so on … Now I have multiple customer file.

I have another dynamic text file that is called master_customer contain customer name and there mail address.

HERE IS COMMING
Currently I send mail to one customer at the time , given the report name, all are hard coded within send mail macro for example for the customer ford I have to type the following in in the macro and run it, IT IS WORKING OK
oDoc.sendto = ford@hotmail.com
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”)

QUESTION:
What I need to do is this I do not want each time for sending mail type customer mail address and there respected report name .

I want to put some intelligent in the mail macro as such when I run my workbook for given customer in this example customer FORD put all his reports in the filed required as shown above
And then read the master_customer file and find the mail address for that customer in this case is FORD if is Toyota or if is Mazda ** or ………

I hope I am clear this time
Here is once again my mail macro
I am using notes mail only

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

I have exactly the same question joseph
Your codes works for me thanks joseph

Can any one help us with the codes ??
I have even check MS I could not find any thing

It maybe is possible to implement it.If u put the customer file in a fixed dir with regular filename,now u can search the special filename in the fixed dir.And if found it,U can send the mail with thses file..

Yes, but how
if my file name are customer88_2003.xls and customer77_2003
how can I send mail to customer88 or customer77 could you please show me
have used the code that was provided for sending mail by joseph
it works fine if I hard code them I mean If I type user mail address and report name in the field as per code.
but I want to automate this process

I think the previous question is not answered yet.

In the script of the send mail I have to type every single file name for attachment.

But if I want to do Some thing global does not work why?
Call oItem.EmbedObject(1454, "", "c:\mydata\*customers.xls”)

Is there a way to do this ??

In the script of the send mail I have to type every single file name for attachment.

But if I want to do Some thing global does not work why?
with error File does not exist?

Call oItem.EmbedObject(1454, "", "c:\mydata\*customers.xls”)


Is there a way to do this ??

U can give the relation of name of the excel files and customers..If they are in the same directory,u can use vb script's filesystem object to find each file,and add them into your mail automaitically!.

I have tried "*" and did not work

How can I give relation of name of excel files for customers
please show us how to do this.

Here's how i've done this:

i made a file with:
Name, FileName, Email
Name, FileName, Email
.
.
.

when it comes to the point of "send" i create a form with a listbox in which i load all the "Names".
after selection and a click on an OK button, the rest is taken out of the file ("FileName","Email") and inserted into the code.

I get no were with this please show me your code how do you do this,
I have provided you the program macro that I am using for sending mail, please show me your code and where I can use your code to modify my macro ??