Send Lotus Notes mail via VBA

[system]

Author: Michael E. Mayhew, Sr., IT Consultant

Further discussion on this utility should take place in this topic.

Author Notes:

I recently had to write a “down and dirty” vb module in a report that would export from a BO 6 report to Excel and then attach the file to a Lotus Notes Email. Finally, the program would send the spreadsheet to the end-user’s In-Box.

I notice there was a “send-mail” vba download in Bob’s Download, but it did not particularly apply to Lotus Notes. So, I thought I would share this code with others who may want to generate an email from Lotus Notes. The code does not contain any error handling, so you may want to add your own to its content

Sub SendMailAttachment()

'******************************************************************************
' Code Created 01/20/2004, MEMSr.
' This Module was created so the end-user can automatically save as an Excel
' file after report is refreshed.  The Excel document is then attached to Lotus
' Notes and an email is sent to their inbox for review.  After review the Excel
' document can be forward to additional recipents.  Note:  You must have Lotus
' Notes loaded on the local machine in order to run the macro.  Also, you will
' need to have the Lotus Notes Library Reference added to the vb editor before
' the code will compile.
'******************************************************************************

    MsgBox "You are about to generate an email which will contain the " & _
            "Excel version of your report.  When the process is complete " & _
            "look in your Lotus notes In-box.  If you want to cancel this " & _
            "operation, please click cancel on next window.", _
            vbInformation, "IMPORTANT"
    
    Dim strBOdocument As String
    Dim strBOUserDocsPath As String
    
    strBOUserDocsPath = busobj.ActiveDocument.Path & "\"
    
    strBOdocument = Application.ActiveDocument.Name
    
    Application.ActiveDocument.SaveAs (strBOUserDocsPath & strBOdocument & ".xls")
    
    '  The code below is used to send Lotus Notes email attachment for the
    '  newly created Excel spreadsheet(s).  Note:  Lotus Notes does not have to
    '  be open at the time, but must be loaded on the local machine.
    
    Dim domSession As New NotesSession
    Dim domNotesDBMailFile As NotesDatabase
    Dim domNotesDocumentMemo As NotesDocument
    Dim domNotesRichText As NotesRichTextItem
    Dim strAttachment As String
    
    domSession.Initialize ("")    'Prompts user for Lotus Notes Password
    
    Set domNotesDBMailFile = domSession.GetDatabase("", "names.nsf")
    Set domNotesDocumentMemo = domNotesDBMailFile.CreateDocument
    
    Call domNotesDocumentMemo.AppendItemValue("Form", "Memo")
    Call domNotesDocumentMemo.AppendItemValue("SendTo", domSession.CommonUserName)
    Call domNotesDocumentMemo.AppendItemValue("Subject", strBOdocument)
    
    Set domNotesRichText = domNotesDocumentMemo.CreateRichTextItem("Body")
    
    strAttachment = strBOUserDocsPath & strBOdocument & ".xls"
    
    Call domNotesRichText.EmbedObject(EMBED_ATTACHMENT, "", strAttachment, "")
    
    domNotesRichText.AppendText (InputBox("Enter any additional text you want ", _
    "to appear in your email E-mail Body"))
    
    domNotesDocumentMemo.Send (False)

End Sub

You will also need a “ThisDocument” module which would trigger the sub. Two alternative for that code are:

Private Sub Document_AfterRefresh()
    ThisDocument.ExecuteMacro ("SendMailAttachment")
End Sub

Private Sub Document_AfterRefresh()
    SendMailAttachment
End Sub

---

BOB Downloads (BOB member since 2003-05-05)