I am using Excel VBA code to send emails via Lotus Notes, using a FOR Loop. I am running into 2 problems:
1) After sending the Document, Lotus Notes keeps the window open. I'm not sure why the Save wouldn't close the window.
2) I have pasted an excel range into the body of the memo. When it sends the email, it appears to transform the body into a jpg file attachment. I would like to leave the picture displayed in the body of the memo.
Here's my code:
Set oWorkspace = CreateObject("Notes.NotesU
IWorkspace
")
Set oSession = CreateObject("Notes.NotesS
ession")
Set oDatabase = oSession.GETDATABASE(notes
Server, notesDBName)
If oDatabase.IsOpen Then
Else
oDatabase.OpenMail
End If
For i = 3 To 100
sSendTo = Trim(uIfNull(Sheets("EmpHD
R").Cells(
i, 12).value))
' Setup Document
Set oDoc = oDatabase.CREATEDOCUMENT()
With oDoc
.Form = "Memo"
.sendto = sSendTo
.Subject = "TEST"
End With
sRange = "B3:M50"
Sheets("Test").Select
Sheets("Test").Range(sRang
e).Select
Selection.Copy
Set uiDoc = oWorkspace.EDITDOCUMENT(Tr
ue, oDoc)
Call uiDoc.GOTOFIELD("Body")
Call uiDoc.Paste
Call uiDoc.Save
Set oDoc = uiDoc.DOCUMENT
Call oDoc.Save(True, True)
With oDoc
.SAVEMESSAGEONSEND = False
.send 0, sSendTo
End With
'*************************
**********
**********
**********
**********
**********
**********
*
' clear clipboard
Sheets("Test").Select
Sheets("Test").Range("A1")
.Select
Selection.Copy
'*************************
**********
**********
**********
**********
**********
**********
*
' Cleanup
Set oDoc = Nothing
Set uiDoc = Nothing
Next i
Set oWorkspace = Nothing
Set oSession = Nothing
Set oDatabase = Nothing
Start Free Trial
