How do I Paste into an E-mail via visual basic?

[calandrelli]

I have an Excel report that can open up Notes and mail a copy of itself to a predefined list of users. Each user only needs a small part of the report. I can get Excel to copy the different parts of the report via VBA, but I can't figure out how to paste them into an email as rich text. Any items?

 

[broadthought]

I use 'sendkeys' method to issue keystrokes to active Lotus new message to attach various files and paste text into messages

 

[Klopper]

Below is a VBA procedure that sends an email via Lotus Notes (which I found elsewhere on tek-tips) which works well with Access 97 and Notes R5.

Hope it helps,
Klopper



Public Sub EmailWithNotes(strSendTo As String, strSubject As String, Optional strCCTo As String, _
Optional strBody As String, Optional strAttachPath As String)

Dim objSession As Object
Dim objDB As Object
Dim objDoc As Object
Dim objAttach As Object
Dim objEmbed As Object
On Error GoTo err_EmailWithNotes
' -Creates a Lotus Notes Session if there isn't one already running
Set objSession = CreateObject("Notes.NotesSession&quot
' -Set db to a database not yet named
Set objDB = objSession.GETDATABASE("", "&quot
' -Sets database to default mail database
Call objDB.OPENMAIL
' -Creates a new mail document
Set objDoc = objDB.CREATEDOCUMENT
' -Insert passed variables into mail document.
Call objDoc.REPLACEITEMVALUE("SendTo", strSendTo)
Call objDoc.REPLACEITEMVALUE("Subject", strSubject)
' -Note: This code only functions for CCing or BCCing to one addresss each.
Call objDoc.REPLACEITEMVALUE("CopyTo", strCCTo)
' -Note: This code does not leave a copy of the email that
' is being created in the sent box, so to have a copy
' BCC is used to send copy to self
Call objDoc.REPLACEITEMVALUE("BlindCopyTo", objSession.UserName)
Call objDoc.REPLACEITEMVALUE("Body", strBody)
If Not strAttachPath = "" Then
Set objAttach = objDoc.CREATERICHTEXTITEM("Attachment&quot
' -Note: More than one attachment can be sent, but than this code
' needs to be copied elsewhere and the following line needs to
' be executed with a new attachment path for every attachment.
Set objEmbed = objAttach.EMBEDOBJECT(1454, "", strAttachPath)
End If
' -Send new mail document
Call objDoc.SEND(False)
' -Reset objects back to nothing to clear memory
exit_EmailWithNotes:
Set objDoc = Nothing
Set objDB = Nothing
Set objSession = Nothing
Set objAttach = Nothing
Set objEmbed = Nothing
Exit Sub
err_EmailWithNotes:
MsgBox Err.Description, , Err.Number
GoTo exit_EmailWithNotes
End Sub