That's not possible - at least not easily - of you send the report directly using DoCmd.SendObject.
You could do the following:
Export the report to a workbook on your hard disk using DoCmd.OutputTo.
Use Automation to:
- start Excel
- open the workbook
- change the font color
- send the workbook
- close the workbook
- quit Excel
Finally, delete the workbook from your hard disk using the Kill command.
For example:
Sub SendXL()
Dim xlApp As Object
Dim xlWbk As Object
Dim blnStart As Boolean
Dim strFile As String
' Create an Excel application object
On Error Resume Next
Set xlApp = GetObject(Class:="Excel.Application")
If xlApp Is Nothing Then
Set xlApp = CreateObject(Class:="Excel.Application")
If xlApp Is Nothing Then
MsgBox "Can't start Excel!", vbExclamation
Exit Sub
End If
blnStart = True
End If
On Error GoTo ErrHandler
' Change name of exported workbook as desired
strFile = Environ("Temp") & "\Report.xlsx"
' Export report, change name of report as needed
DoCmd.OutputTo acOutputReport, "rptMyReport", acFormatXLSX, strFile
' Open exported workbook
Set xlWbk = xlApp.Workbooks.Open(strFile)
' Set font of worksheet
xlWbk.Worksheets(1).UsedRange.Font.Color = vbBlack
' Mail workbook, change recipient and subject as needed
xlWbk.SendMail "you@somewhere.com", "Sending report"
ExitHandler:
On Error Resume Next
' Close workbook without saving
xlWbk.Close SaveChanges:=False
' Delete workbook
Kill strFile
' Quit Excel if we started it
If blnStart Then
xlApp.Quit
End If
Exit Sub
ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub
Regards, Hans Vogelaar (http://www.eileenslounge.com)
