Using excel to send email through lotus notes - variable data and open/close notes

[seawolf68]

Rick,
This is what I do but I am unable to attach a spreadsheet.

Call CreateLotusWorkSpace
    wsEmailBody.Activate
    ' strNameTo = EmailTO
    strNameTo = ""
    strNameCC = EmailCC
    Call SendEmail(strNameTo, strNameCC, FName)
    Call CloseLotusWorkSpace

Public Sub CreateLotusWorkSpace()
    Dim lnRetVal As Long
    ' Check if Lotus Notes is open or not.
    lnRetVal = FindWindow("NOTES", vbNullString)
    If lnRetVal = 0 Then
        MsgBox "Please make sure that Lotus Notes is open!", vbExclamation
        Exit Sub
    End If
    ' Instantiate the Lotus Notes COM's objects.
    Set oWorkSpace = CreateObject("Notes.NotesUIWorkspace")
End Sub

Public Sub CloseLotusWorkSpace()
    ' Release objects from memory.
    Set oWorkSpace = Nothing
    Set oUIDoc = Nothing
End Sub

Sub SendEmail(stTo As String, stCC As String)
    Dim stBody As String
    Dim stSubject As String
    Dim rnBody As Range
    Dim lnRetVal As Long
    stBody = ""
    stSubject = "Daily Report"
    ' A named range in the activesheet is in use.
    Set rnBody = ActiveSheet.Range("A1:A22")
    rnBody.Copy
    ' Using LotusScript to create the e-mail.
    Set oUIDoc = oWorkSpace.COMPOSEDOCUMENT("", "", "Memo")
    Set oUIDoc = oWorkSpace.CURRENTDOCUMENT
    Call oUIDoc.FIELDSETTEXT("EnterSendTo", stTo)
    Call oUIDoc.FIELDSETTEXT("EnterCopyTo", stCC)
    Call oUIDoc.FIELDSETTEXT("Subject", stSubject)
    Call oUIDoc.FIELDSETTEXT("Body", stBody)
    Call oUIDoc.GOTOFIELD("Body")
    Call oUIDoc.Paste
    Call oUIDoc.EMBEDOBJECT(1454, "", ActiveSheet.Range("C1:C1"))
    Call oUIDoc.Send
    Call oUIDoc.Close
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With
End Sub