Sending e-mails through lotus notes

[balvinder]

Hi,

I'am using excel 2007 & i want to send e-mails to different users using lotus notes 8.5 along with some summary. Till now I'am succeed to send e-mails to different users by clicking command button using below code.

Problem: I'am extracting my base data from MS access using data connection in excel file. In 2nd sheet of workbook i have Agent_ID's & E-mail ID's of all agents which is also present in data file i'am extracting from MS-Access.
I want to use Agent_ID to bifurcate data from base data & want to send e-mail to corresponding e-mail ID including data attachment with summary of policy status & count (e.g. Pivotable summary) . Please help me with rest part.

Sub Button2_Click()

    Dim Session As Object
    Dim EmbedObj1 As Object

  ' Open and locate current LOTUS NOTES User

    Set Session = CreateObject("Notes.NotesSession")
        UserName = Session.UserName
    MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
    Set Maildb = Session.GETDATABASE("", MailDbName)
    If Maildb.IsOpen = True Then
    Else
        Maildb.OPENMAIL
    End If

  ' Create New Mail and Address Title Handlers

    Set MailDoc = Maildb.CreateDocument

    MailDoc.Form = "Memo"
    '   Select range of e-mail addresses
    Recipient = Sheets("E-Mail").Range("A3:A3").Value
    MailDoc.SendTo = Recipient
    
    ans = MsgBox("Would you like to Copy (cc) anyone on this message?" _
        , vbQuestion & vbYesNo, "Send Copy")
    
        If ans = vbYes Then
            ccRecipient = InputBox("Please enter the additional recipient's e-mail address" _
                , "Input e-mail address")
            MailDoc.CopyTo = ccRecipient
        End If
            
    MailDoc.Subject = "ECS Data For Your Customers - MAX NEW YORK LIFE INSURANCE"
        MailDoc.Body = "Dear Mr.Sanjay, Greetings from Max New York Life Insurance Company"
        
'   Select Workbook to Attach to E-Mail

    MailDoc.SaveMessageOnSend = True
        attachment1 = "D:\SMS Scripts-MyMoney.xls" '    Required File Name

    If attachment1 <> "" Then
        On Error Resume Next
            Set AttachME = MailDoc.CREATERICHTEXTITEM("attachment1")
            Set EmbedObj1 = AttachME.embedobject(1454, "attachment1", "D:\SMS Scripts-MyMoney.xls", "") 'Required File Name
        On Error Resume Next
    End If

    MailDoc.PostedDate = Now()
        On Error GoTo errorhandler1
    MailDoc.Send 0, Recipient

    Set Maildb = Nothing
        Set MailDoc = Nothing
            Set AttachME = Nothing
        Set Session = Nothing
    Set EmbedObj1 = Nothing

errorhandler1:

    Set Maildb = Nothing
        Set MailDoc = Nothing
            Set AttachME = Nothing
        Set Session = Nothing
    Set EmbedObj1 = Nothing

    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
    
End Sub

 

[balvinder]

Hi,

any hint or suggestions are highly appreciated....