We help IT Professionals succeed at work.

VBA - Send lotus notes 9 mail from Excel with attachment

Hi guys,

I'm trying to send mail from EXcel with an attachment, but its not working for me.... Line 39 is not working
Sub CreateEmail()

        Const EMBED_ATTACHMENT As Long = 1454

        Dim Notes As Object
        Dim Maildb As Object
        Dim objNotesDocument As Object
        Dim objNotesField As Object
        Dim Attachment As Object
        
        Dim stSubject As String
        Dim stBody As String
        Dim vaRecipients As String
        
        
    
        vaRecipients = "xxx@yyy.dk"
        stSubject = "Subject title " & Format(Now(), "mm-yyyy")
        stBody = "lalalala"
        stAttachment = "C:\Test\test.txt"
        

        Set Notes = CreateObject("Notes.NotesSession")
        Set Maildb = Notes.GETDATABASE("", "")
        Maildb.OPENMAIL
        Set objNotesDocument = Maildb.CREATEDOCUMENT

        Set objNotesField = objNotesDocument.APPENDITEMVALUE("SendTo", vaRecipients)
        Set objNotesField = objNotesDocument.APPENDITEMVALUE("Subject", stSubject)
        Set objNotesField = objNotesDocument.APPENDITEMVALUE("SaveMessageOnSend", True)
        Set objNotesField = objNotesDocument.APPENDITEMVALUE("PostedDate", Now())
        Set objNotesField = objNotesDocument.APPENDITEMVALUE(EMBED_ATTACHMENT, "", stAttachment, "")
        
        Set workspace = CreateObject("Notes.NotesUIWorkspace")
        Call workspace.EDITDOCUMENT(True, objNotesDocument)
        Set uidocument = workspace.CurrentDocument
        Call uidocument.GotoField("Body")
        Call uidocument.InsertText(stBody)
        Call uidocument.EmbedAttachment(EMBED_ATTACHMENT, "", stAttachment)
          
    MsgBox "Mail created"

    AppActivate "Notes"

Open in new window


kindly advise
Comment
Watch Question

ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Hi,

Try changing variable:
Dim Attachment As Object
to
Dim stAttachment As String
CERTIFIED EXPERT
Top Expert 2016

Commented:
Hi,

pls try
Sub CreateEmail()

        Const EMBED_ATTACHMENT As Long = 1454

        Dim Notes As Object
        Dim Maildb As Object
        Dim objNotesDocument As Object
        Dim objNotesField As Object
        Dim Attachment As Object
        
        Dim stSubject As String
        Dim stBody As String
        Dim vaRecipients As String
        
        
    
        vaRecipients = "xxx@yyy.dk"
        stSubject = "Subject title " & Format(Now(), "mm-yyyy")
        stBody = "lalalala"
        stAttachment = "C:\Test\test.txt"
        

        Set Notes = CreateObject("Notes.NotesSession")
        Set Maildb = Notes.GETDATABASE("", "")
        Maildb.OPENMAIL
        Set objNotesDocument = Maildb.CREATEDOCUMENT

        Set objNotesField = objNotesDocument.APPENDITEMVALUE("SendTo", vaRecipients)
        Set objNotesField = objNotesDocument.APPENDITEMVALUE("Subject", stSubject)
        Set objNotesField = objNotesDocument.APPENDITEMVALUE("SaveMessageOnSend", True)
        Set objNotesField = objNotesDocument.APPENDITEMVALUE("PostedDate", Now())

        Set AttachME = objNotesDocument.CREATERICHTEXTITEM("Attachment")
        Set EmbedObj = AttachME.EMBEDOBJECT(EMBED_ATTACHMENT, "", stAttachment, "Attachment")
        objNotesDocument .CREATERICHTEXTITEM ("Attachment")          

    MsgBox "Mail created"

    AppActivate "Notes"

Open in new window

Regards

Author

Commented:
@Shums - Did not change aything
@Rgonzo1971 - It gives me and error at line 35

---------------------------
Microsoft Visual Basic for Applications
---------------------------
Compile error:

Invalid or unqualified reference
---------------------------
OK   Help  
---------------------------

and .CREATERICHTEXTITEM is highlighted
CERTIFIED EXPERT
Top Expert 2016

Commented:
Sorry can't help further
Sjef BosmanGroupware Consultant
CERTIFIED EXPERT

Commented:
Remove line 35.

What exactly do you want? Do you want to prepare a mail and show it in Notes before the user sends it, or do you want to send a mail directly from VBA (via Notes) with the attachment? Each needs a different approach...

Author

Commented:
@Sjef - I want to create a mail from excel in Notes 9 with a file attach that is located on the creating computer, other then that it should insert the mail signature in the bottom as it would do normallay if the mail was manually created.
Groupware Consultant
CERTIFIED EXPERT
Commented:
You could try with
        Call uidocument.Import(stAttachment)
instead of the EmbedAttachment in the example in your question, although I assume the whole file is imported in the mail and not attached to it... There are 2 other ways: call a Formula agent that uses the EditInsertFileAttachment command, or use the keyboard library to attach the file "on the fly" (which is ugly, I know).