I wrote a macro in Excel that works out quite well (btw I work on Lotus Notes 8.5, not 9). I'm able to copy and paste given range (to be precise a pivot table) as bitmap but the problem is that not the whole are is copied, only a part of a table.

Here is the code, what's wrong with pasting? Why can't I copy the whole table, just a part of it? Variables rows and columns are ok and return proper values.

Public Sub Lotus_Mail()

Dim NSession As Object
Dim NUIWorkSpace As Object
Dim NDatabase As Object
Dim NDoc As Object
Dim NUIdoc As Object
Dim Subject As String
Dim SendTo As String, CopyTo As String
Dim pivots As Range
Dim Month As String
Dim text1 As Range
Dim text2 As Range
Dim i As Integer


Dim arrHUBs(1 To 8) As String

arrHUBs(1) = "a"
arrHUBs(2) = "b"
arrHUBs(3) = "c"
arrHUBs(4) = "d"
arrHUBs(5) = "e"
arrHUBs(6) = "f"
arrHUBs(7) = "g"
arrHUBs(8) = "h"

Week = DatePart("ww", Date, vbMonday, vbFirstFourDays)
Month = MonthName(DatePart("m", Date), False)

On Error Resume Next

For x = 1 To 8

SendTo = Application.WorksheetFunction.VLookup(arrHUBs(x),      Sheets("Mail").Range("A2:C9"), 2, 0)
CopyTo = Application.WorksheetFunction.VLookup(arrHUBs(x), Sheets("Mail").Range("A2:C9"), 3, 0)
Subject = "Summary " & arrHUBs(x) & " - " & Month & ": week " & Week

'area to select (pivot table)
rows = Sheets("sheet").Cells(Rows.Count, 21).End(xlUp).Row
columns = Sheets("sheet").Cells(6, Columns.Count).End(xlToLeft).Column
Set pivots = Sheets("sheet").Range(Cells(4, 19), Cells(rows, columns))
'Set pivots = Sheets("sheet").PivotTables("Pivot1")  ???this line doesn't work, any other way to select pivot and paste to Lotus?

Set text1 = Sheets("Mail").Range("A12")
Set text2 = Sheets("Mail").Range("A13")

'Lotus step by step
Set NSession = CreateObject("Notes.NotesSession") 
Set NUIWorkSpace = CreateObject("Notes.NotesUIWorkspace")
Set NDatabase = NSession.GetDatabase("", "")
If Not NDatabase.IsOpen Then NDatabase.OPENMAIL

'creating mail
Set NDoc = NDatabase.CreateDocument

    With NDoc
    .SendTo = SendTo
    .CopyTo = CopyTo
    .Subject = Subject

    'Email body text, including a placeholder which will be replaced by Excel table

    .body = text1 & vbLf & vbLf & _
        "{IMAGE_PLACEHOLDER}" & vbLf

    .Save True, False
End With

'Edit the new document using Notes UI to copy and paste pivot table into it

Set NUIdoc = NUIWorkSpace.EDITDocument(True, NDoc)

With NUIdoc
    Sheets("sheet").Select

    'Find the placeholder in the Body item 

    .GotoField ("Body")        
    .FINDSTRING "{IMAGE_PLACEHOLDER}"
    '.DESELECTALL 'Uncomment to leave the placeholder in place (cells are inserted immediately before it)

    'Copy pivot table (being a range) as a bitmap to the clipboard and paste into the email

    pivots.CopyPicture xlBitmap
    .Paste 'maybe any paste special option exists?
    Application.CutCopyMode = False

    '.Send
    '.Close
End With

Set NSession = Nothing

Next x
End Sub