Sub exampleSendMail()
'declare varibles (for interaction with Notes)
Dim domSession As New NotesSession
Dim domNotesDbDir As NotesDbDirectory
Dim domNotesDBMailFile As NotesDatabase
Dim domNotesDocumentMemo As NotesDocument
Dim domNotesRichText As NotesRichTextItem
Dim richStyle As NotesRichTextStyle
Dim notesPassword As String
'set locations for mailbox
Const busManMailDBServerName As String = "serverName"
Const busManMailFileName As String = "dbfileName.nsf"
'declare varibles (for choosing attachment file)
Dim latestDateFormat As String
Dim attachmentFileName As String
'declare varibles (for setting up mail message)
Dim recipient(200) As String
Dim ccRecipient(200) As String
Dim bccRecipient(10) As String
Dim txtSubject As String
'declare varibles (for distribution list)
Dim rec As Recordset
Dim Count As Integer
'export report and save locally
[Reporting Code].DBTTowerReport
'start a session to notes
'domSession.Initialize (notesPassword)
domSession.Initialize
'open database
Set domNotesDbDir = domSession.GetDbDirectory(busManMailDBServerName)
Set domNotesDBMailFile = domNotesDbDir.OpenDatabase(busManMailFileName)
'create a new memo
Set domNotesDocumentMemo = domNotesDBMailFile.CreateDocument
Set domNotesRichText = domNotesDocumentMemo.CreateRichTextItem("Body")
Set richStyle = domSession.CreateRichTextStyle
'open query with eMail distribution list
Set rec = CurrentDb.OpenRecordset("Distribution List")
'counter to build recipient array
Count = 0
'add eMail addresses from table to array
Do While rec.EOF = False
recipient(Count) = rec("Email")
Count = Count + 1
rec.MoveNext
Loop
'close recordset
rec.Close
Set rec = Nothing
'e-mail subject line
txtSubject = "Report to " & Format(latestdate(), "d mmmm")
'set up mail parameters
Call domNotesDocumentMemo.AppendItemValue("Form", "Memo")
Call domNotesDocumentMemo.AppendItemValue("SendTo", recipient)
Call domNotesDocumentMemo.AppendItemValue("CopyTo", ccRecipient)
Call domNotesDocumentMemo.AppendItemValue("BlindCopyTo", bccRecipient)
Call domNotesDocumentMemo.AppendItemValue("Subject", txtSubject)
Call domNotesDocumentMemo.AppendItemValue("Logo", "StdNotesLtr50")
Call domNotesDocumentMemo.AppendItemValue("Principal", "mailbox name")
'attempt to insert image into start of mail message
Dim infoBanner As Object
Dim objXL As Excel.Application
Dim objBook As Excel.Workbook
Set objXL = CreateObject("Excel.application")
Set objBook = objXL.Workbooks.Add
Set infoBanner = objBook.ActiveSheet.Pictures.Insert(Application.CurrentProject.Path & "\banner\infoBanner.jpg")
infoBanner.Copy
'I think I understand upto here but and struggling to be able to paste the image into the mail message
Dim test As Object
Set test = domNotesDBMailFile.currentdocument
Call test.gotofield("body")
Call test.Paste
objXL.Application.CutCopyMode = False
Set infoBanner = Nothing
objXL.Quit
Set objBook = Nothing
Set objXL = Nothing
'ok from here
Call domNotesRichText.AddNewLine(2)
Call domNotesRichText.AppendText("Email Text")
Call domNotesRichText.AddNewLine(2)
'format date correctly
latestDateFormat = Format(latestdate(), "yyyy-mm-dd")
'file path and name of attachment
attachmentFileName = Application.CurrentProject.Path & "\Report Exports\Report - " & latestDateFormat & ".xls"
'insert attachment
Call domNotesRichText.EmbedObject(EMBED_ATTACHMENT, "", attachmentFileName, "")
Call domNotesRichText.AddNewLine(2)
'send eMail
domNotesDocumentMemo.Send False
'clear memory
Set domNotesDbDir = Nothing
Set domNotesDBMailFile = Nothing
Set domNotesDocumentMemo = Nothing
Set domNotesRichText = Nothing
Set richStyle = Nothing
End Sub
