PDA

View Full Version : Need Help on copy paste as image in lotusnotes from excel



Jokia
10-11-2010, 08:41 PM
Hi,

I find this site as a great solution for most of our excel and access questions.

I have a task to be completed by sending emails to my client through Lotusnotes. I have automated that part because the amount of emails to be sent is very high( around 1000 mails)

But now we need to copy and paste an excel range ( A1 to H30) in to the lotusnotes new memo as an image.I know how to attache an image through VBa, but if I can get any help on copy pasting that range as an Image that would be much appreciated.

Many Thanks

Kenneth Hobs
10-12-2010, 04:06 PM
Welcome to the forum!

You can copy a range with:
Range("A1:B10").CopyPicture xlScreen, xlBitmap

I have not tested this below but something like it might give you another idea. Note the use of the Microsoft Forms object.

Otherwise, I would just use the method above and a SendKeys() method. Of course with Vista+, UAC needs to be disabled when using VBA's SendKeys().
Sub Send_Range_Lotus()
'Using late binding for Lotus
'A reference to Microsoft Forms 2.0 object library must be set.

Dim noSession As Object, noDatabase As Object, noDocument As Object
Dim stSubject As Variant, stTitle As String
Dim vaRecipient As Variant, vaMsg As Variant
Dim rnBody As Range
Dim Data As DataObject

stSubject = "Standardmessage, automation e-mail."

Do
vaRecipient = Application.InputBox( _
Prompt:="please entrer the recipient mailaddress:" & _
vbCrLf & "(excel@microsoft.com or just the name)", _
Title:="Recipient", _
Type:=2)
Loop While vaRecipient = ""

If vaRecipient = False Then Exit Sub

Do
vaMsg = Application.InputBox( _
Prompt:="Please enter the message-text:", _
Title:="Message", _
Type:=2)
Loop While vaMsg = ""

On Error Resume Next
Set rnBody = Application.InputBox("Please enter the range:", _
, Selection.Address, , , , 8)
If rnBody Is Nothing Then Exit Sub
On Error GoTo 0

Set noSession = CreateObject("Notes.NotesSession")
Set noDatabase = noSession.GETDATABASE("", "")

If noDatabase.IsOpen = False Then noDatabase.OPENMAIL

Set noDocument = noDatabase.CREATEDOCUMENT
rnBody.Copy
Set Data = New DataObject
Data.GetFromClipboard

With noDocument
.Form = "Memo"
.sendto = vaRecipient
.Subject = stSubject
'.Body = Data.GetText
.Body = "yo" & Data.GetText
.SAVEMESSAGEONSEND = True
End With

noDocument.PostedDate = Now()
noDocument.Send 0, vaRecipient

Set noDocument = Nothing
Set noDatabase = Nothing
Set noSession = Nothing

AppActivate "Microsoft Excel"
Application.CutCopyMode = False

MsgBox "E-mail have been created.", vbInformation

End Sub