Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    sydney
    Posts
    47
    Rep Power
    16

    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
  2. #2
  3. Inherits Programmer.Slacker
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Aug 2003
    Location
    Between my Id and your Ego
    Posts
    2,351
    Rep Power
    0
    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.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    sydney
    Posts
    47
    Rep Power
    16

    Question send mail for multiple users given customer_master file


    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
  6. #4
  7. Inherits Programmer.Slacker
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Aug 2003
    Location
    Between my Id and your Ego
    Posts
    2,351
    Rep Power
    0
    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?
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    sydney
    Posts
    47
    Rep Power
    16
    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
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    76
    Rep Power
    16
    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
    Last edited by melinda; October 19th, 2003 at 10:14 PM.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jul 2003
    Posts
    1,152
    Rep Power
    17
    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..
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    76
    Rep Power
    16
    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.
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    76
    Rep Power
    16
    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 ??
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    76
    Rep Power
    16
    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 ??
    Last edited by melinda; October 20th, 2003 at 10:26 PM.
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jul 2003
    Posts
    1,152
    Rep Power
    17
    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!.
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    76
    Rep Power
    16

    Angry


    I have tried "*" and did not work
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    sydney
    Posts
    47
    Rep Power
    16
    How can I give relation of name of excel files for customers
    please show us how to do this.
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    32
    Rep Power
    15
    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.
  28. #15
  29. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    sydney
    Posts
    47
    Rep Power
    16
    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 ??

Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo