Thu Aug 26, 2010 10:08 pm: Please read an important announcement regarding the ASUG Influencer Summit at the fall conference in Orlando. You will not want to miss the opportunity to address ASUG management directly.
General Notice: There is 1 job opening posted on www.BOBsJobs.net, "EBI Systems Architect-Ohio"
General Notice: There are 56 ASUG sponsored events in the next 45 days, see calendar for details. |
| Author |
Message |
BOB Downloads
Download Manager
Joined: 05 May 2003
Posts: 40
|
 Posted: Sat Feb 21, 2004 11:42 am
Post subject: Send Lotus Notes mail via VBA |
|
|
Author: Michael E. Mayhew, Sr., IT Consultant
Further discussion on this utility should take place in this topic.
Author Notes:
| Quote: | 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 |
| Code: | 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 |
| Quote: | | You will also need a "ThisDocument" module which would trigger the sub. Two alternative for that code are: |
| Code: | Private Sub Document_AfterRefresh()
ThisDocument.ExecuteMacro ("SendMailAttachment")
End Sub
Private Sub Document_AfterRefresh()
SendMailAttachment
End Sub |
|
|
| Back to top |
|
|
|
|
