Hi All,

I need a macro that will copy cells from a spreadsheet, locate a stationary email, or document within stationary (a template email) - it is on a specific mailbox database, which should be opened and then whatever range was copied from the spreadsheet to be pasted (as a bitmap) into the document body.

I have tried to amalgamate bits of code, but I have had no luck.

There is a specific address to the actual document and if I use the below script it brings it up fine! The problem I'm facing is I'm unable to paste the copied range.

Sub LotusNotes_CVXDocLib()
Application.ScreenUpdating = False
On Error Resume Next
AppActivate "Lotus Notes"
If Not Err.Number = 0 Then
Err.Clear
MsgBox "Notes is Not Running", vbInformation, "WSM-Marketing"
Else
Application.ActiveWorkbook.FollowHyperlink Address:="Notes://home/4A2565BF00171D6E/4525608C115E322585255D7C00545AF9", NewWindow:=True
End If
Exit Sub
End Sub
That code brings up the actual document, but I cannot paste.

There is another piece of coding that I have found on the net.

As long as the document is visible it will copy and paste cells, but has issues and comes up with errors.

Sub EditSelectedMail()
Dim NSession As Object
Dim NDatabase As Object
Dim NUIWorkspace 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

Set NUIdoc = NUIWorkspace.EDITDOCUMENT(True)
With NUIdoc

    'Find the marker text in the Body item
    .GotoField ("Body")
    .FINDSTRING "**PASTE EXCEL CELLS HERE**"

    'Copy Excel cells to clipboard
    Sheets("Sheet1").Range("A1:E6").Copy

    'Create a temporary Word Document
    Set WordApp = CreateObject("Word.Application")
    WordApp.Visible = False                                 
    WordApp.Documents.Add

    'Paste into Word document and copy to clipboard
    With WordApp.Selection
        .PasteSpecial DataType:=10      
        'Enum WdPasteDataType: 10 = HTML; 2 = Text; 1 = RTF
        .WholeStory
        .Copy
    End With

    'Paste from clipboard (Word) to Lotus Notes document
    .Paste
    Application.CutCopyMode = False

    'WordApp.Quit SaveChanges:=False
    Set WordApp = Nothing

End With  
End Sub
This however still brings up errors.

I know this second code, takes the range and pastes it into word and then pastes that back into the lotus notes document...

Can you please help?

Thank you