I use the following code to do something similar. See what you can use from it.
Code:
Dim varApprovalRequest, varReportName, varSendTo, varSendFrom, varHTMLBody As String
Dim varUsername, varSubjectText, varSendCC As String
Dim objOutlook, objEmail As Object
varApprovalRequest = DLookup("data", "table", "child = 'ApprovalRequestMessage'")
varReportName = DLookup("data", "qry_drcconfig", "child = 'ApprovalRequetForm'")
varSendTo = Me.ComboApprover.Column(2)
varSendFrom = DLookup("email", "tbl_contact", "ID = " & [TempVars]![varDRCContactID])
varSubjectText = "Design Review Project Approval Request: " & [TaskSR]
varEmailTemplate = DLookup("data", "qry_drcconfig", "child = 'ApprovalRequestTemplate'")
varSendCC = DLookup("data", "qry_drcconfig", "child = 'ApprovalCopyToList'")
DoCmd.OutputTo acOutputReport, varReportName, acFormatHTML, varApprovalRequest
Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItemFromTemplate(varEmailTemplate)
With objEmail
.To = varSendTo
.cc = varSendCC
.Subject = varSubjectText
'.Body = "The body doesn't matter, just the attachment"
.HTMLBody = varHTMLBody
.Attachments.Add varApprovalRequest
.Display
.SentOnBehalfOfName = varSendFrom
'.Send - this didn't work on my setup but .Display did
'.ReadReceiptRequested
End With
There are a couple of things with this code. The first is that it uses an HTML file generated from a report as the body. The second is that I have a qry that I get the information from called qry_drcconfig. You will want to modify your code to work with your data.
Good luck.