Paste Clipboard into Lotus Notes E-mail body

[b3ndee46]

Hi there, I am really new, and already hopeless

Can anybody please tell me, why my code is pasting correctly the image from clipboard into e-mail body?
I am trying to code by myself, sometimes I win, sometime I don't

I would highly appreciate if anybody could teach me where my logic gone wrong.

Sub OpenNewEmailInNotes()
Dim NotesApp As Object
Dim NotesUIWorkspace As Object
Dim NotesUIDoc As Object
Dim rng As Range
Dim emailBody As String
Set NotesApp = CreateObject("Notes.NotesSession")
Set NotesUIWorkspace = CreateObject("Notes.NotesUIWorkspace")
Set NotesUIDoc = NotesUIWorkspace.ComposeDocument("", "", "Memo")
NotesUIDoc.FieldSetText "EnterSendTo", "Email"
NotesUIDoc.FieldSetText "Subject", "Mreport"
Set rng = ThisWorkbook.ActiveSheet.Range("A1:H20")
ThisWorkbook.Activate
rng.CopyPicture Appearance:=xlScreen, Format:=xlPicture
emailBody = "Report attached" & vbCrLf & "<br><br><img src='cid:clipboard_image'>"
NotesUIDoc.FieldAppendText "Body", emailBody
NotesUIDoc.Paste
Set NotesUIDoc = Nothing
Set NotesUIWorkspace = Nothing
Set NotesApp = Nothing
End Sub

Thank you in advance!
ben

 

[John_w]

Try this:

Public Sub Email_Excel_Cells()

    Dim NSession 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 NDoc = NUIWorkSpace.ComposeDocument("", "", "Memo")
    Set NUIdoc = NUIWorkSpace.currentdocument
    
    With NUIdoc
        .FieldSetText "EnterSendTo", "email@address.com, email2@address.com"
        .FieldSetText "EnterCopyTo", "copy@address.com"
        .FieldSetText "Subject", "Mreport"
        .FieldSetText "Body", "Report attached." & String(2, vbCrLf)
        .GotoField "Body"
        ThisWorkbook.ActiveSheet.Range("A1:E19").Copy
        .Paste
        Application.CutCopyMode = False
        '.Send
        '.Close
    End With
    
    Set NUIdoc = Nothing
    Set NDoc = Nothing
    Set NUIWorkSpace = Nothing
    Set NSession = Nothing
    
End Sub

PS Please use VBA code tags - the VBA icon in the message toolbar.

 

[b3ndee46]

John_w, You are my hero! Thank you!!​

Try this:

Public Sub Email_Excel_Cells()

    Dim NSession 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 NDoc = NUIWorkSpace.ComposeDocument("", "", "Memo")
    Set NUIdoc = NUIWorkSpace.currentdocument
   
    With NUIdoc
        .FieldSetText "EnterSendTo", "email@address.com, email2@address.com"
        .FieldSetText "EnterCopyTo", "copy@address.com"
        .FieldSetText "Subject", "Mreport"
        .FieldSetText "Body", "Report attached." & String(2, vbCrLf)
        .GotoField "Body"
        ThisWorkbook.ActiveSheet.Range("A1:E19").Copy
        .Paste
        Application.CutCopyMode = False
        '.Send
        '.Close
    End With
   
    Set NUIdoc = Nothing
    Set NDoc = Nothing
    Set NUIWorkSpace = Nothing
    Set NSession = Nothing
   
End Sub

PS Please use VBA code tags - the VBA icon in the message toolbar.

 

[John_w]

Great that it works for you! Please mark the thread as solved.