Emailing reports as attachments from Microsoft Access

[ Email | Access Tips | Access | Main ] Last updated 2010-09-29

These alternatives require that you print a report for just one record.  Or a set of records.  MS built this functionality in the forms wizard but, for unknown reasons, don't have it in the report wizard.  See ACC2000: How to Print a Single Record from a Form in a Report - 209560.  Unfortunately while this approach works for standard report previewing/printing it doesn't work for creating reports as files.

The syntax to create a report as a file is:

DoCmd.OutputTo acOutputReport, "Name of Report in MDB", "Snapshot Format", _
    "Path/Name of report on hard drive", False

The Access 97 online help mentions acFormatActiveXServer, acFormatHTML, acFormatIIS, acFormatRTF, acFormatTXT and acFormatXLS as the available options for the OutputFormat parameter.

However you can not pass a filter or where clause as you would in a standard OpenReport command in VBA when outputting the report to a file.   Therefore you must change the filter in the reports OnOpen event. The below example uses the value of a form text box as the flilter.

Me.Filter = "ShipmentsID=" & Forms![Select Load List]![LoadID]
Me.FilterOn = True

Alternatively in Access 2003 and newer you can pass OpenArgs to the report.

docmd.OpenReport "Name of report",acViewNormal,,,,"1234"

Now the filter in the reports OnOpen Event might look like

Me.Filter = "ShipmentsID=" & Me.OpenArgs
Me.FilterOn = True

Output formatting to include graphics, lines and such

Outputting a report in the above available formatting options, including RTF, does not include the graphics, lines and some other formatting  options.  Also Excel formatting does very interesting things with the headers and footers and just about everything else.  You may want to create queries just for outputting data in Excel format for clarity of data or create special reports with a minimum of formatting, headers and footers

The below options, snapshot viewer, PDF and Stephen Lebans' Report Utilities are the only method I know of to create reports as files and maintain all lines, formatting and graphics. Each approach has it's strengths and weaknesses including pricing.

Snapshot Viewer.

Snapshot is an Access only format of the printed report. For reasons known only to the IT department they may not be willing to install a Snapshot viewer on their PCs. They will allow Adobe Acrobat Viewer though. The differences between installing a Microsoft report viewer and the Adobe Acrobat Viewer escape me. <smile>

You can't make changes to these files.

PDF

Yet another option is PDF. There are at least several different methods of creating PDF files which are at my Creating PDF files page.   One option, supplied by fellow MVP Stephen Lebans, is free and works very well..  Access 2007 and newer can create PDF files directly.  Lebans solution no longer works in Access 2010 as the ability to create snapshot files has been removed which Lebans utility requires.

Excel

See Modules: Transferring Records to Excel with Automation

RTF

I'm not aware of any other options for RTF and I probably wouldn't recommend them as RTF format is rather ugly.

[ Email | Access Tips | Access | Main ]

   Auto FE Updater distribute new and updated Front End databases to your users with several mouse clicks.

Granite Fleet Manager - the best designed fleet maintenance tracking and management system available