Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003
    Posts
    31

    Red face Unanswered: send mail for multiple users

    I am using following macro with in my Excel workbook as macro and works find on notes mail

    My Excel work book generate 3 file for each customers example
    Ford Quarterly Q4-2003-All.xls
    Ford Quarterly Q4-2003-Customer.xls
    Ford Quarterly Q4-2003-Errors.xls
    Toyota Quarterly Q4-2003-All.xls
    Toyota Quarterly Q4-2003-Customer.xls
    Toyota Quarterly Q4-2003-Errors.xls

    Also I have master file all_customer_master that contain customer name and there e_mail address Example
    Toyota Toyota@hotmail.com
    Mazda Mazda@yahoo.com
    Ford Ford@yahoo.com

    How can loop or use my master file (all_customer_master) for GIVEN report (or produced by workbook excel to send report to the customer.
    Let say If I have all 6 report how can I send mail (3 report as per above) to Toyota and Ford
    If No report for Mazda then no mail
    Also how can send a copy to my self myself@hotmail.com



    mail program for 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 = "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

  2. #2
    Join Date
    Sep 2003
    Posts
    31
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •