asked on

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

Shums Faruk

Hi,

Try changing variable:
Dim Attachment As Object
to
Dim stAttachment As String

Rgonzo1971

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

Harsh Kumar

ASKER

@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

Rgonzo1971

Sorry can't help further

Sjef Bosman

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...

Harsh Kumar

ASKER

@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.

ASKER CERTIFIED SOLUTION

Sjef Bosman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial