Need help using VBA to email attachments through Lotus Notes 9.0

[creelmank]

Hi,

I'm a total novice when it comes to Excel VBA. Most of my codes have come from scouring the internet and plugging and playing until I get the desired result. I had a script that created multiple emails from a list in Excel and sent an attachment to each email within the list. This code was working fantastic until my company upgraded to Lotus Notes 9.0. Now, whenever I run the macro I receive run-time errors. I've done some digging and recognize the issue is that I'm using OLE classes and I need to be using COM classes. However, I can't seem to figure out what I need to change other than changing the following:

 Set Session = CreateObject("Notes.NotesSession") to Set Session = CreateObject("Lotus.NotesSession").

But this is not nearly enough to fix the issue.

My original code is listed below:

Sub email()


Dim rng As Range
Dim cell As Range
Set rng = Worksheets("Agents").Range("EMAILLIST")
For Each cell In rng
 With Worksheets("START")
.Range("EMAILCODE").Value = cell.Value
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
     "C:\TEMP\ " & Worksheets("START").Range("EMAILFILENAME").Value & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties _
        :=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
    Dim Maildb As Object 'The mail database
    Dim UserName As String 'The current users notes name
    Dim MailDbName As String 'THe current users notes mail database name
    Dim MailDoc As Object 'The mail document itself
    Dim AttachMe As Object 'The attachment richtextfile object
    Dim AttachMe2 As Object 'The attachment richtextfile object
    Dim Session As Object 'The notes session
    Dim EmbedObj As Object 'The embedded object (Attachment)
    Dim EmbedObj2 As Object 'The embedded object (Attachment)
    Dim sPDFPath As String
    Dim attach1 As String
    Dim attach2 As String
    Dim sRecipient As String
    Dim sSubject As String
    Dim StrBody As String
    Dim Reply As String
    Dim Principal As String
    Dim lSheet As Long
    Dim lSheets As Long
    Dim wb As Workbook
    Dim ws As Worksheet
    
    'Record base path to PDF
    Set wb = ActiveWorkbook
    sPDFPath = wb.Path & Application.PathSeparator
    sSubject = Worksheets("START").Range("EMAILSUBJECT")
    StrBody = Worksheets("START").Range("BODYHEADER") & vbLf _
    & vbLf _
    & vbLf _
    & Worksheets("START").Range("BODYLINE1") & Chr(10) & Chr(10) & Worksheets("START").Range("BODYLINE2") & Chr(10) & Chr(10) & Chr(10) & Chr(10) & "Kind regards" & Chr(10) & Chr(10) & Chr(10) & Chr(10) & "Jane Doe" & Chr(10) & Chr(10) & "Director Business Development"

    
    'Start a session to notes
    Set Session = CreateObject("Notes.NotesSession")
    'Next line only works with 5.x and above. Replace password with your password
    'Session.Initialize ("Password")
    
    'Get the sessions username and then calculate the mail file name
    'You may or may not need this as for MailDBname with some systems you
    'can pass an empty string or using above password you can use other mailboxes.
    UserName = Session.UserName
    MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
    
    'Open the mail database in notes
    Set Maildb = Session.GETDATABASE("", MailDbName)
     If Maildb.IsOpen = True Then
          'Already open for mail
     Else
         Maildb.OPENMAIL
     End If

                'Record recipient
                sRecipient = Worksheets("START").Range("EMAILCONTACT")
                Reply = Worksheets("START").Range("BDREMAIL")
                Principal = "Jane Doe <JaneDoe@abccompany.com@NotesDomain>"
        
                'Create the new mail document
                Set MailDoc = Maildb.CREATEDOCUMENT
                MailDoc.Form = "Memo"
                MailDoc.Principal = Principal
                MailDoc.sendto = sRecipient
                MailDoc.Subject = sSubject
                MailDoc.Replyto = Reply
                MailDoc.body = StrBody
                MailDoc.SAVEMESSAGEONSEND = True
                
                'Set up the embedded object and attachment and attach it
                attach1 = "C:\TEMP\ " & Worksheets("START").Range("EMAILFILENAME").Value & ".pdf"
                Set AttachMe = MailDoc.CREATERICHTEXTITEM("Attachment")
                Set EmbedObj = AttachMe.EMBEDOBJECT(1454, "", attach1, "Attachment")



                'Send the document
                MailDoc.PostedDate = Now() 'Gets the mail to appear in the sent items folder
                MailDoc.SEND 0, sRecipient
                
                'Release variables used
                Set MailDoc = Nothing
                Set EmbedObj = Nothing
                Set AttachMe = Nothing
                Set EmbedObj2 = Nothing
                Set AttachMe2 = Nothing

    
    'Clean Up
    Set Maildb = Nothing
    Set Session = Nothing
      End With
                
                Next

End Sub

At this point I'm just a little lost on how I would need to tweak this code in order for it to work. I would appreciate all of the help I can get.

Thank you so much!

 

[creelmank]

Bump

 

[John_w]

Try adapting the code in this thread - http://www.mrexcel.com/forum/excel-questions/891760-send-active-sheet-range-cells-mail.html