Macro for sending different mails to different people and attaching different files to them in lotus notes

[praveen2825]

Hi, Can any one help me in this. I am very new to vB as well as Macros.

I would like to send different mails to different people with different attachments.

Please find my excell sheet coloums for your understanding

'firstname' 'lastname' 'emailid' 'attachmentpath' 'phonenumber'

I would be very greatful if anyone can help me in this.

Regards,
SAI

 

[John_w]

Try this (put the code in a standard module).

Public Sub Send_Lotus_Notes_Emails()

    Dim NSession As Object
    Dim NDb As Object
    Dim NDocument As Object
    Dim NRTItem As Object
    Dim lastRow As Long, r As Long
    
    Set NSession = CreateObject("Notes.NotesSession")
    Set NDb = NSession.GetDatabase("", "")
    NDb.OPENMAIL
    
    lastRow = Cells(Rows.Count, "A").End(xlUp).row

    For r = 2 To lastRow
        Set NDocument = NDb.CreateDocument
        With NDocument
            .Form = "Memo"
            .Subject = "This is the email subject"
            .sendto = Cells(r, "C").Value
            .body = "To: " & Cells(r, "A").Value & " " & Cells(r, "B").Value & vbLf & _
                "Phone Number: " & Cells(r, "E") & vbLf & vbLf & _
                "The rest of the email body text."
            
            If Dir(Cells(r, "D").Value) <> "" Then
                Set NRTItem = .CreateRichTextItem("Attachment")
                NRTItem.EmbedObject 1454, "", Cells(r, "D").Value
            Else
                MsgBox "File " & Cells(r, "D").Value & " doesn't exist so can't attach it to email."
            End If
            
            .SaveMessageOnSend = True
            .send False
        End With
    Next

    Set NSession = Nothing
    Set NDb = Nothing
    Set NDocument = Nothing
    Set NRTItem = Nothing

End Sub

 

[sankarsr]

Hi John,

It is giving the error at ".send False"

Error:

Run-time error '-2147417851 (80010105)';
Automation error
The server threw an exception

 

[John_w]

Try this version instead. This uses early binding of the Lotus objects so you must set a reference in the VBA project as noted at the top of the code.

Public Sub Send_Lotus_Notes_Emails2()

    'References: Lotus Domino Objects

    Dim NSession As Domino.NotesSession
    Dim NMailDb As Domino.NotesDatabase
    Dim NDocument As Domino.NotesDocument
    Dim NRTItemBody As Domino.NotesRichTextItem
    Dim dataSheet As Worksheet
    Dim lastRow As Long, r As Long
    Dim FromName As String, FromEmail As String
    
    Set dataSheet = ThisWorkbook.Worksheets("Sheet1")
    
    FromName = "FirstName Surname"     'CHANGE THIS
    FromEmail = "email@address.com"    'CHANGE THIS
    
    'Start a Notes session using Lotus Domino Objects (COM classes)
    
    Set NSession = New Domino.NotesSession                  'COM, early binding
    'Set NSession = CreateObject("Lotus.NotesSession")      'COM, late binding
       
    'Get default mail database
    
    With NSession
        .Initialize ""              'supported in COM only
        .ConvertMime = False        'do not convert MIME to rich text
        Set NMailDb = .GetDatabase(.GetEnvironmentString("MailServer", True), .GetEnvironmentString("MailFile", True))
        If Not NMailDb.IsOpen Then NMailDb.Open
    End With
    
    lastRow = dataSheet.Cells(Rows.Count, "A").End(xlUp).row

    For r = 2 To lastRow
    
        Set NDocument = NMailDb.CreateDocument
        With NDocument
            .ReplaceItemValue "Form", "Memo"
            .ReplaceItemValue "Subject", "This is the email subject"
            Debug.Print dataSheet.Cells(r, "C").Value, .GetItemValue("Subject")(0)
            .ReplaceItemValue "SendTo", dataSheet.Cells(r, "C").Value
            
            'Set sender name and email address
            
            .ReplaceItemValue "From", FromName & " <" & FromEmail & ">"                     'name and email address
            .ReplaceItemValue "Principal", FromName & " <" & FromEmail & "@NotesDomain>"   'name and email address
            '.ReplaceItemValue "Principal", FromEmail & "@NotesDomain"                      'just email address
                        
            'Create the email body
            
            Set NRTItemBody = .CreateRichTextItem("Body")
            With NRTItemBody
                .AppendText "Start of the email body text."
                .AddNewLine 2
                .AppendText "To: " & dataSheet.Cells(r, "A").Value & " " & dataSheet.Cells(r, "B").Value
                .AddNewLine
                .AppendText "Phone Number: " & dataSheet.Cells(r, "E")
                .AddNewLine 2
                .AppendText "End of the email body text." & vbCrLf
                
                'Attach the file if it exists
                
                If Dir(dataSheet.Cells(r, "D").Value) <> "" Then
                    .EmbedObject EMBED_ATTACHMENT, "", dataSheet.Cells(r, "D").Value, "Attachment"
                End If
            End With
            
            .SaveMessageOnSend = True
            .Send False
        End With
        
    Next

    Set NRTItemBody = Nothing
    Set NDocument = Nothing
    Set NMailDb = Nothing
    Set NSession = Nothing

End Sub

 

[sankarsr]

Thank you Joy. Previous code itself worked.

I have declared a variable ST as string and first took email id from excel to ST. Assigned the value of ST to SENDTO property and it started working again. Seems like data type issue, which is triggering the runtime error.

 

[sankarsr]

Thank you John. Previous code itself worked.

I have declared a variable ST as string and first took email id from excel to ST. Assigned the value of ST to SENDTO property and it started working again. Seems like data type issue, which is triggering the runtime error.

 

[Adekxl]

Dear John,

generally, from a week i faith with such problem. I am looking for possibility to send email via lotus with range of sheet as email body.
On others site I've asked you about how switch off spell check i your code (unfortunately uidoc requires "spell check" on the end).
Here i see interesting code but the question is, how save range of sheet as jpg or bmp (picture) and how to add it as a email body.
I will grateful if you help me with this.

 

[John_w]

send email via lotus with range of sheet as email body.
On others site I've asked you about how switch off spell check i your code (unfortunately uidoc requires "spell check" on the end).
Here i see interesting code but the question is, how save range of sheet as jpg or bmp (picture) and how to add it as a email body.
I will grateful if you help me with this.

From the same thread, try the code at http://www.mrexcel.com/forum/excel-...t-body-through-lotus-notes-2.html#post3393210 or http://www.mrexcel.com/forum/excel-...t-body-through-lotus-notes-2.html#post3854546

The code doesn't save the range of cells as jpg or bmp; it simply copies to the clipboard and pastes that into the email body.