Greetings,
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. The code for that is:
Private Sub Document_AfterRefresh()
ThisDocument.ExecuteMacro ("SendMailAttachment")
End Sub
or Simply:
Private Sub Document_AfterRefresh()
SendMailAttachment
End SubI hope you find this information helpful. Please note that variations of this code may be needed in order to accommodate your Lotus Notes platform.
MayhewM 
(BOB member since 2003-10-22)
, but I believe the credit goes to Michael on this one.
) so that the file in the first email is replaced with the next file, and so on, and so on.
(BOB member since 2002-08-16)