VBA to Lotus Notes - How to Add Attachment

Question

i'm currently using the code below with some modifications to create my message but it's been a struggle to add to it attachment capabilities, can someone please help me with this one?

Sub Notes_Email_Excel_Cells()
Dim NSession As Object
Dim NDatabase As Object
Dim NUIWorkSpace As Object
Dim NDoc As Object
Dim NUIdoc As Object

Set NSession = CreateObject("Notes.NotesSession")
Set NUIWorkSpace = CreateObject("Notes.NotesUIWorkspace")
Set NDatabase = NSession.GetDatabase("", "")

If Not NDatabase.IsOpen Then
    NDatabase.OPENMAIL
End If

'Create a new document

Set NDoc = NDatabase.CreateDocument

With NDoc
    .SendTo = "[email protected]"
    .CopyTo = ""
    .subject = "Pasted Excel cells " & Now

    'Email body text, including marker text which will be replaced by the Excel cells

    .body = "Text in email body" & vbNewLine & vbNewLine & _
        "**PASTE EXCEL CELLS HERE**" & vbNewLine & vbNewLine & _
        "Excel cells are shown above"

    .Save True, False
End With

'Edit the just-created document to copy and paste the Excel cells into it

Set NUIdoc = NUIWorkSpace.EDITDocument(True, NDoc)

With NUIdoc

    'Find the marker text in the Body item

    .GotoField ("Body")
    .FINDSTRING "data"

    'Replace it with the Excel cells

    Sheets("Sheet1").Range("A1:E6").Copy
    .Paste
    Application.CutCopyMode = False

    '.Send
    '.Close
End With

Set NSession = Nothing
End Sub

I do believe that i might have some success adding the following lines, but i still have to test it at work, as i'm posting from my PC:

Dim notesRichTextItem As NotesRichTextItem
'[...]
Set notesRichTextItem = doc.CreateRichTextItem("Body")
'[...]
Call notesRichTextItem.EmbedObject(EMBED_ATTACHMENT, "", "File path")

I haven't really understood the EmbedObject parameters, as from what i've found on IBM Knowledge center didn't clear all of my doubts ( here ), as you can find below

Defined in

NotesRichTextItem

Syntax

Set notesEmbeddedObject = notesRichTextItem.EmbedObject( type%, class$, source$, [ name$ ] )

Therefore it should be something like

notesEmbeddedObject = notesRichTextItem.EmbedObject(1454,"",**FILEPATH**,"")

Instead of my previous

    Call notesRichTextItem.EmbedObject(EMBED_ATTACHMENT, "", "File path")

but how will i add it to the end of the message? or even, how will i call it in the code? :(

nempoBu4 nempoBu4 · Accepted Answer · 2015-01-23T04:05:23

You should convert your initialization of Body field to NotesRichTextItem initialization.
Instead of:

.body = "Text in email body" & vbNewLine & vbNewLine & _
    "**PASTE EXCEL CELLS HERE**" & vbNewLine & vbNewLine & _
    "Excel cells are shown above"

Write this:

Set notesRichTextItem = .CreateRichTextItem("Body")
notesRichTextItem.AppendText("Text in email body")
notesRichTextItem.AddNewline(2)
notesRichTextItem.AppendText("**PASTE EXCEL CELLS HERE**")
notesRichTextItem.AddNewline(2)
notesRichTextItem.AppendText("Excel cells are shown above")
notesRichTextItem.AddNewline(1)
'And here comes the attachment:
Call notesRichTextItem.EmbedObject(EMBED_ATTACHMENT, "", "File path")

VBA to Lotus Notes - How to Add Attachment

1 Answers