Microsoft Access: Create a Button to Email Reports

Jun 22

4 min read

Key Takeaways

This video tutorial explains how to create a macro in Microsoft Access to automate the process of emailing a report. The video demonstrates the steps to attach a macro to a command button on a report, configure the macro to send the report as a PDF (or other formats), specify recipients and subject, and even set up an error handler to notify the user if issues arise during the email process.

The instructor shows how to open the email draft for review before sending, providing a step-by-step guide for users to implement this functionality within their Access databases.

Study Guide

This guide covers the process of creating a macro in Microsoft Access to automate the emailing of reports. It focuses on using the Macro Builder to configure various settings for sending reports as attachments, handling errors, and customizing the email message.

Key Concepts:

Macros: Automated sequences of actions performed in Access.

Report Design View: The mode used to modify the layout and elements of a report, including adding buttons.

Property Sheet: A panel in Design View used to view and modify the properties of selected objects, such as buttons and reports.

Event Tab: A tab within the Property Sheet where you can associate actions (like macros) with specific events (like clicking a button).

On Click Event: An event that occurs when a user clicks on a button.

Macro Builder: A tool within Access for creating and editing macros by selecting actions from a list.

On Error Action: A macro action that specifies what should happen if an error occurs during the execution of the macro.

Email Database Object Action: A macro action used to create and send an email with an Access object (like a report) attached.

Arguments: Settings or parameters that configure the behavior of a macro action. Examples include Object Type, Object Name, Output Format, To, CC, BCC, Subject, Message Text, and Edit Message.

Output Format: The file format for the attached report (e.g., PDF, HTML, Excel).

Edit Message Argument: Controls whether the default email application opens for editing before sending (Yes) or if the email is sent automatically without editing (No).

Template File Argument: Used to specify an HTML template file when the output format is HTML.

If Action: A macro action used to create conditional logic, allowing the macro to perform different actions based on a condition.

Conditional Expression: A statement that evaluates to true or false, used with the If action to control the flow of the macro.

Macro Error.Number: A system variable that stores the error number if an error occurs during macro execution. A value greater than zero indicates an error.

Macro Error.Description: A system variable that stores the system-generated description of an error.

Message Box Action: A macro action used to display a pop-up message to the user.

Beep Argument: Controls whether the system makes a sound when a message box appears.

Type Argument (Message Box): Determines the icon displayed in the message box (e.g., Information, Critical, Warning, None).

Title Argument (Message Box): Sets the title bar text of the message box. Leaving it blank defaults to "Microsoft Access".

Join One of Our Many On Demand Courses

Quiz

In which view of a report do you add and configure the button that will run the email macro?
Which tab in the Property Sheet is used to associate a macro with a button click?
When using the Macro Builder, what action is typically added first to define how errors are handled?
Which macro action is used to send an Access report as an email attachment?
What argument within the "Email Database Object" action do you use to specify the file format of the attached report?
What is the purpose of setting the "Edit Message" argument to "Yes" in the "Email Database Object" action?
What is the conditional expression used in an "If" action to check if an error occurred during the macro execution?
How do you display the system-generated description of an error in a message box using the "Message Box" action?
What does leaving the "Title" argument of the "Message Box" action blank result in?
After creating and saving the macro, what steps are needed to test it?

Quiz Answer Key

You add and configure the button in the Report Design View.
The Event tab in the Property Sheet is used to associate a macro with a button click.
The "On Error" action is typically added first to define how errors are handled.
The "Email Database Object" action is used to send an Access report as an email attachment.
The "Output Format" argument is used to specify the file format of the attached report.
Setting "Edit Message" to "Yes" opens the default email application so you can edit the message before sending.
The conditional expression [MacroError].[Number]<>0 is used to check if an error occurred.
You display the system-generated error description by entering [MacroError].[Description] in the "Message" field of the "Message Box" action.
Leaving the "Title" argument blank results in the message box title being "Microsoft Access".
To test the macro, you save and close the macro, save the report, switch to Report View, and click the button.