Note:
- Using Excel 97
- VBA Project includes Reference of 'Lotus Notes Automation Classes' (notes32.tlb)
Using Excel VBA, I am trying to send an e-mail (via Lotus Notes) that includes text and an attachment.
While I have got this to essentially work, I am having problems resolving the following:
1) How to get the text portion of the e-mail to be formatted Red and Bold. While you will see that my procedure below has the code setting the Style and Item, this does not work.
2) How can I have a copy of the sent email placed in a 'Sent Mail' folder -
not I have tried the PUTINFOLDER and SAVEMESSAGEONSEND methods but neither
have been successful.
3) The procedure fails if Lotus Notes is not open. Any ideas of how to get around this, ie check to see if Notes is open, and if not then open the application.
Any help would be greately appreciated.
Below is the procedure I am using:
Sub MailCreditCardWorkbooks()
Dim objNotesSession As Object
Dim objNotesDB As Object
Dim objNotesDoc As Object
Dim objNotesRTI As Object
Dim objNotesRTS As Object
Dim strCCPeriod As String
Dim strPath As String
Dim strCCYear As String
Dim strCCMonth As String
Dim strFileName As String
Dim strMailAddress As String
Dim rngMyCC As Range
strCCPeriod = UCase(Format(Range("rnPeriodFrom"), "MMM YY"))
strPath = "S:\Life\Wellington\Accounts Payable\Credit Cards\Send to Card Holder\"
strCCYear = Format(Range("rnPeriodFrom"), "YYYY")
strCCMonth = Format(Range("rnPeriodFrom"), "MM")
'Final Path location
strPath = strPath & strCCYear & "\" & strCCMonth & "\"
Set objNotesSession = CreateObject("Notes.NotesSession")
Set objNotesDB = objNotesSession.GETDATABASE("", "")
Call objNotesDB.OPENMAIL
For Each rngMyCC In Range("rnCreditCards")
'Only e-mail Workbooks for Credit Cards with a balance
If Not (rngMyCC.Offset(0, 1).Value = 0) Then
strFileName = rngMyCC.Offset(0, -2)
strMailAddress = CStr(rngMyCC.Offset(0, 2))
Application.StatusBar = "Sending Credit Card File to: " & strFileName & " (" & strMailAddress & ")"
'Create new mail message
Set objNotesDoc = objNotesDB.CREATEDOCUMENT
objNotesDoc.SendTo = strMailAddress
objNotesDoc.Subject = strFileName & " - Credit Card Analysis: " & strCCPeriod
objNotesDoc.ReturnReceipt = "1"
Set objNotesRTS = objNotesSession.CREATERICHTEXTSTYLE
Set objNotesRTI = objNotesDoc.CREATERICHTEXTITEM(objNotesDoc, "body")
objNotesRTS.NOTESCOLOR = 2
objNotesRTS.FontSize = 12
objNotesRTS.Bold = True
Call objNotesRTI.APPENDSTYLE(objNotesRTS)
Call objNotesRTI.APPENDTEXT(strFileName & " - Credit Card Analysis: " & strCCPeriod)
Call objNotesRTI.ADDNEWLINE(2)
Call objNotesRTI.APPENDTEXT("Please complete the attached workbook for the analysis of " _
& "your Credit Card transactions for the month of: " _
& strCCPeriod)
Call objNotesRTI.ADDNEWLINE(2)
Call objNotesRTI.EMBEDOBJECT(1454, "", strPath & strFileName & ".XLS")
'Send message
Call objNotesDoc.SEND(False)
'Clear objects
Set objNotesDoc = Nothing
Set objNotesRTI = Nothing
Set objNotesRTS = Nothing
End If
Next rngMyCC
Application.StatusBar = False
'Clear objects
Set objNotesDB = Nothing
Set objNotesSession = Nothing
End Sub
Print this page
