Emailing report and xl font color RRS feed

  • Question

  • Good day, folks-

    Is there a way to programmatically change the color of the font of the text inside the excel attachment which is created when using vba to email a report from Access?

    The text in the xl attachments are a very very light gray, and very difficult to see.  Sad as it sounds, not all of my users are savvy enough with excel to be able to change the font color when they open the spreadsheet.


    Sunday, October 18, 2015 6:03 AM


  • 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"
        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
        End If
        Exit Sub
        MsgBox Err.Description, vbExclamation
        Resume ExitHandler
    End Sub

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, October 18, 2015 8:50 AM