Hi,
I found the following code at http://www.fabalou.com/VBandVBA/lotusnotesmail.asp. I’ve tweaked it a bit, and would like to have the “.SendTo =” portion of the code to read from Sheet1.Range(“A3”), which is in Excel of course. As the code is currently, I have to hard code an email address into this area. Any ideas would be appreciated as always!
Thanks!
Lana
Sub TestingLotusNotesEmail()
Dim noSession As Object, noDatabase As Object, noDocument As Object
Dim obAttachment As Object, EmbedObject As Object
Dim stSubject As Variant, stAttachment As String
Dim vaRecipient As Variant, vaMsg As Variant
Const EMBED_ATTACHMENT As Long = 1454
‘Retrieve the path and filename of the active workbook.
stAttachment = ActiveWorkbook.FullName
‘Initiate the Lotus Notes COM’s Objects.
Set noSession = CreateObject(“Notes.NotesSession”)
Set noDatabase = noSession.GETDATABASE(“”, “”)
‘If Lotus Notes is not open then open the mail-part of it.
If noDatabase.IsOpen = False Then noDatabase.OPENMAIL
‘Create the e-mail and the attachment.
Set noDocument = noDatabase.CreateDocument
Set obAttachment = noDocument.CreateRichTextItem(“stAttachment”)
Set EmbedObject = obAttachment.EmbedObject(EMBED_ATTACHMENT, “”, stAttachment)
‘Add values to the created e-mail main properties.
With noDocument
.Form = “Memo”
.SendTo = “jane.doe@corp.com”
.Subject = “Phantom Stock Reports”
.Body = “Hi there Lana. This is fun!”
.SaveMessageOnSend = True
End With
‘Send the e-mail.
With noDocument
.PostedDate = Now()
.Send 0, vaRecipient
End With
‘Release objects from the memory.
Set EmbedObject = Nothing
Set obAttachment = Nothing
Set noDocument = Nothing
Set noDatabase = Nothing
Set noSession = Nothing
‘Activate Excel for the user.
AppActivate “Microsoft Excel”
MsgBox “The e-mail has successfully been created and distributed.”, vbInformation
End Sub
