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

[rroach]

I am trying to use an excel vba to enter variable data on the body of the email and to open a and close the session with VBA. I have been successful at generating the new session and attaching the file, but I cannot insert the variable data and close the session.

Can anyone help?

Below is my code I have so far.

Dim objNotesSession As Object
Dim objNotesMailFile As Object
Dim objNotesDocument As Object
Dim objNotesField As Object

Function SendMail()
    Msg10 = "Test"
    On Error GoTo SendMailError
    EMailSendTo = "rick.roach@maps-na.com" '' Required - Send to address
    EMailCCTo = ""                      '' Optional
    EMailBCCTo = ""                     '' Optional
    ' Establish Connection to Notes
    Set objNotesSession = CreateObject("Notes.NotesSession")
    ' Provide password if logged out
    Call Session.Initialize("krroach")
    ' Establish Connection to Mail File
    ''                                    .GETDATABASE("SERVER", "FILE")
    Set objNotesMailFile = objNotesSession.GETDATABASE("", "")
    ' Open Mail
    objNotesMailFile.OPENMAIL
    ' Create New Memo
    Set objNotesDocument = objNotesMailFile.CREATEDOCUMENT
    ' Create 'Subject Field'
    Set objNotesField = objNotesDocument.APPENDITEMVALUE("Subject", EmailSubject)
    ' Create 'Send To' Field
    Set objNotesField = objNotesDocument.APPENDITEMVALUE("SendTo", EMailSendTo)
    ' Create 'Copy To' Field
    Set objNotesField = objNotesDocument.APPENDITEMVALUE("CopyTo", EMailCCTo)
    ' Create 'Blind Copy To' Field
    Set objNotesField = objNotesDocument.APPENDITEMVALUE("BlindCopyTo", EMailBCCTo)
    ' Create 'Body' of memo
    Set objNotesField = objNotesDocument.CREATERICHTEXTITEM("Body")
    With objNotesField
        .APPENDTEXT "This e-mail is generated by an automated process - this is a test."
        .ADDNEWLINE 1
        .APPENDTEXT "Please follow established contact procedures should you have any questions."
        .ADDNEWLINE 2
        .APPENDTEXT "PROCESS INSTABILITY ALERT"
        .ADDNEWLINE 3
        .APPENDTEXT Msg10
        .ADDNEWLINE 4
        .APPENDTEXT "PART NON-CONFORMANCE ALERT"
        .ADDNEWLINE 5
    End With
    ' Attach the file   --1454 indicate a file attachment
    ' objNotesField = objNotesField.EMBEDOBJECT(1454, "", "C:\Temp\test.xls")
    objNotesField = objNotesField.EMBEDOBJECT(1454, "", ActiveWorkbook.FullName)
    'Send the e-mail
    objNotesDocument.Send (0)
    ' Release storage
    Set objNotesSession = Nothing
    Set objNotesSession = Nothing
    Set objNotesMailFile = Nothing
    Set objNotesDocument = Nothing
    Set objNotesField = Nothing
    ' Set return code
    SendMail = True
    Exit Function
    SendMailError:
    Msg = "Error # " & Str(Err.Number) & " was generated by " _
    & Err.Source & Chr(13) & Err.Description
    MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
    SendMail = False
End Function

Thanks in advance for any help.

Rick