excelvbalotus-notes

Attach multiple PDFs in Lotus Notes VBA


i have been searching online for an answer for quite a while but i can't make my project work in any way. As the title says, i am trying to attach multiple pdfs in an Email through Vba. The location of the files is stored in 2 separate cells (G1 and F1). I have tried debugging the code multiple times and it always shows and error in the line marked below, the error is: "Run Time Error 7368: Application-defined or object-defined error" EDIT: if i try to run the macros without adding attachments in the sub "callemail" the file run smoothly, so i think there is a problem with how i declare both file paths


Public Sub SendNotesMail(Subject As String, Attachment As String, Recipient As String, ccRecipient1 As String, ccRecipient2 As String, ccRecipient3 As String, ccRecipient4 As String, BodyText As String, SaveIt As Boolean)
'Set up the objects required for Automation into lotus notes
    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 Session As Object 'The notes session
    Dim EmbedObj As Object 'The embedded object (Attachment)
    'Start a session to notes
    Set Session = CreateObject("Notes.NotesSession")
    '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
    'Set up the new mail document
    Set MailDoc = Maildb.CREATEDOCUMENT
    MailDoc.Form = "Memo"
    MailDoc.sendto = Recipient
    MailDoc.CopyTo = Array(ccRecipient1, ccRecipient2, ccRecipient3, ccRecipient4)
    MailDoc.Subject = Subject
    MailDoc.body = BodyText
    MailDoc.SAVEMESSAGEONSEND = SaveIt
     'Set up the embedded object and attachment and attach it
    If Attachment <> "" Then
        Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment")
        Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", Attachment, "Attachment")
'ERROR        MailDoc.CREATERICHTEXTITEM ("Attachment")   'ERROR
    End If
    'Send the document
    MailDoc.PostedDate = Now() 'Gets the mail to appear in the sent items folder
    MailDoc.send 0, Recipient
    'Clean Up
    Set Maildb = Nothing
    Set MailDoc = Nothing
    Set Session = Nothing
    Set EmbedObj = Nothing
    
    
End Sub

And here is the call to the sub :



Sub callemail()

Dim pathA As String
Dim pathB As String
Dim att As String

pathA = ThisWorkbook.Worksheets("Foglio1").Range("G1").Value2
pathB = ThisWorkbook.Worksheets("Foglio1").Range("F1").Value2

att = Array(pathA, pathB)

Call SendNotesMail("SCH61", att, "a@gmail.com", "b@gmail.com", "c@gmail.com", "d@gmail.com", "e@gmail.com", "test", True)
End Sub

And i would also like to know how to position both attachments at the top of the email body. Thanks in advance and best regards


Solution

  • this morning i finally figured it out, here is the updated code for Subs:

    Public Sub SendNotesMail(Subject As String, Attachment As String, Attachment1 As String, Recipient As String, ccRecipient1 As String, ccRecipient2 As String, ccRecipient3 As String, ccRecipient4 As String, BodyText As String, SaveIt As Boolean)
    'Set up the objects required for Automation into lotus notes
        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 Session As Object 'The notes session
        Dim EmbedObj As Object 'The embedded object (Attachment)
        'Start a session to notes
        Set Session = CreateObject("Notes.NotesSession")
        'Next line only works with 5.x and above. Replace password with your 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
        'Set up the new mail document
        Set MailDoc = Maildb.CREATEDOCUMENT
        MailDoc.Form = "Memo"
        MailDoc.sendto = Recipient
        MailDoc.CopyTo = Array(ccRecipient1, ccRecipient2, ccRecipient3, ccRecipient4)
        MailDoc.Subject = Subject
        MailDoc.body = BodyText
        MailDoc.SAVEMESSAGEONSEND = SaveIt
         'Set up the embedded object and attachment and attach it
        If Attachment <> "" Then
            Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment")
            Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", Attachment, "Attachment")
            MailDoc.CREATERICHTEXTITEM ("Attchment")
        End If 'Added next 6 lines as well as Dim Attachment1 as String in the sub
        Set EmbedObj = Nothing
        If Attachment1 <> "" Then
            Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment1")
            Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", Attachment1, "Attachment1")
            MailDoc.CREATERICHTEXTITEM ("Attchment1")
        End If  'Finished new part, now old code
        'Send the document
        MailDoc.PostedDate = Now() 'Gets the mail to appear in the sent items folder
        MailDoc.send 0, Recipient
        'Clean Up
        Set Maildb = Nothing
        Set MailDoc = Nothing
        Set Session = Nothing
        Set EmbedObj = Nothing
    End Sub
    

    And the other part:

    Sub callemail()
    
    Dim pathA As String
    Dim pathB As String
    Dim att As String
    
    pathA = ThisWorkbook.Worksheets("Foglio1").Range("G1").Value2
    pathB = ThisWorkbook.Worksheets("Foglio1").Range("F1").Value2
    
    
    
    Call SendNotesMail("SCH61", pathA, pathB, "a@gmail.com", "b@gmail.com", "c@gmail.com", "d@gmail.com", "e@gmail.com", "test", True)
    End Sub