For me this was the shortest possible way to get Rich Type Formatting output to an Excel spreadsheet and attached to an email. Below is an example for FYSA
Code:
Public Sub Send_HBRoll_Click()
Dim xlApp As Excel.Application
Dim WkBkA As Excel.Workbook
On Error GoTo ErrorMsgs
DoCmd.OpenQuery "qryDailyUpdate"
DoCmd.OpenQuery "qryRollupUpdate"
NIE = DLookup("[NIE]", "[tblChangeRequest]")
Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
Set xlApp = New Excel.Application
xlApp.Visible = False
With objOutlookMsg
.Subject = NIE & " HB Weekly Rollup - " & Tod
.Body = SigBlock
DoCmd.OutputTo 3, "rptHBRollup", acFormatPDF, "C:\Temp\" & NIE & " HB Weekly Rollup - " & Tod & ".pdf", , 0
DoCmd.OutputTo 3, "rptAnnexB", acFormatPDF, "C:\Temp\" & NIE & " Annex B - " & Tod & ".pdf", , 0
DoCmd.OutputTo acOutputQuery, "qryWeeklySumm", "HTML(*.html)", "C:\Temp\" & NIE & " Weekly Summation - " & Tod & ".html", False, "", 1200, acExportQualityPrint
Set WkBkA = Workbooks.Open("C:\Temp\" & NIE & " Weekly Summation - " & Tod & ".html")
WkBkA.Sheets(1).Rows(1).Delete
WkBkA.SaveAs ("C:\Temp\" & NIE & " Weekly Summation - " & Tod), 51
.Attachments.Add ("C:\Temp\" & NIE & " HB Weekly Rollup - " & Tod & ".pdf")
.Attachments.Add ("C:\Temp\" & NIE & " Annex B - " & Tod & ".pdf")
.Attachments.Add "C:\Temp\" & NIE & " Weekly Summation - " & Tod & ".xlsx"
.To = "HB Rollup"
.Display
Kill "C:\Temp\" & NIE & " HB Weekly Rollup - " & Tod & ".pdf"
Kill "C:\Temp\" & NIE & " Annex B - " & Tod & ".pdf"
Kill "C:\Temp\" & NIE & " Weekly Summation - " & Tod & ".html"
Excel.Application.Quit
Kill "C:\Temp\" & NIE & " Weekly Summation - " & Tod & ".xlsx"
End With
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
Set objOutlookAttach = Nothing
Set WkBkA = Nothing
Exit Sub
ErrorMsgs:
If Err.Number = "287" Then
MsgBox "You selected No to the Outlook security warning. Rerun the procedure and click Yes to access e-mail addresses to send your message."
Else
MsgBox Err.Number & " " & Err.Description
End If
End Sub