Others

How to Send Emails to Access Members Using VBA

Algirdas Jasaitis Sep 25, 2026 869 views

Question details

The user needs to send emails to organization members from a Microsoft Access database using a VBA procedure, including attaching a PDF report.

How to Send Emails to Access Members Using VBA

Product: Microsoft Access
Device & OS: not provided
Scenario: Automating email distribution to members listed in a database table by dynamically reading addresses and generating a message with a PDF attachment.
Observed behavior: The user wants to successfully read email addresses, build a recipient list, dispatch the emails via DoCmd.SendObject, and properly handle errors and null values without triggering spam controls.

Before you start

Ensure that your default email client (such as Microsoft Outlook) is installed and configured properly on your machine, and back up your Access database before testing new VBA scripts.

Solution 1Recommended

Use DAO Recordset and DoCmd.SendObject in VBA

Loop through the membership table using a DAO Recordset to compile email addresses and send a PDF attachment using the DoCmd.SendObject method.

This approach uses standard DAO.Database and DAO.Recordset objects to iterate through your table. It safely concatenates valid email addresses while skipping null values, ensuring your email client receives a properly formatted recipient string.

Initialize DAO Objects

Declare your DAO.Database and DAO.Recordset variables in your VBA module. Set the database object to CurrentDb and open a recordset based on your target table, such as 'tblMembership'.

Loop Through the Recordset

Use a 'Do While Not .EOF' loop to iterate through the records. Inside the loop, check if the EmailAddress field is not null. If valid, concatenate it to a string variable, separated by semicolons.

Execute DoCmd.SendObject

After the loop completes, call DoCmd.SendObject. Use named arguments like ObjectType:=acSendReport, ObjectName:="YourReportName", OutputFormat:=acFormatPDF, To:=yourRecipientString, Subject:="Your Subject", and MessageText:="Your Message".

Implement Error Handling and Cleanup

Include an 'On Error GoTo' statement at the start of your procedure. At the end of the script, ensure you close the recordset using rs.Close and set the database objects to Nothing to free up memory.

Use DAO Recordset and DoCmd.SendObject in VBA

Spam Control Warning: For large recipient lists, sending a single mass email can trigger spam controls. Consider modifying the loop to place DoCmd.SendObject inside, sending individual messages or small batches instead.

Solution 2

Debug and Step Through the VBA Procedure

Identify exactly where the VBA code fails if the email does not generate correctly or raises an error.

Free Microsoft Office alternative

Looking for a Free, Lightweight Office Suite?

While Microsoft Access is a specialized database tool, managing your daily documents, spreadsheets, and presentations doesn't have to be expensive. WPS Office provides a fully featured, lightweight alternative that is highly compatible with standard Microsoft Office formats, allowing you to handle your daily administrative tasks efficiently.

1. Visit the WPS Website: Go to the official WPS Office website to locate the free download link.
2. Download the Installer: Click the download button to get the lightweight installer compatible with your operating system.
3. Install and Launch: Run the setup file and follow the on-screen instructions to install and start using your new office suite.

Fully compatible with Microsoft Word, Excel, and PowerPoint formats (.docx, .xlsx, .pptx).Lightweight installation that runs smoothly on older devices without consuming heavy system resources.Familiar, tabbed user interface for seamless migration and easy document management.Completely free to use with robust built-in PDF viewing and editing capabilities.

microsoft office alternative - wps office

Frequently Asked Questions

How do I handle null email addresses in my VBA recordset loop?

Inside your recordset loop, use an If statement with the IsNull() function or check if the field's length is greater than zero (e.g., If Not IsNull(rs!EmailAddress) Then) before attempting to concatenate the email address to your recipient list string.

Why is my DoCmd.SendObject failing to attach the PDF report?

Ensure that you are specifying the correct ObjectType (which should be acSendReport) and that the ObjectName exactly matches the name of your saved report in Access. Additionally, verify that the OutputFormat argument is properly set to acFormatPDF.

Can I send individual emails to members instead of one large mass email using VBA?

Yes. Instead of concatenating all addresses into one string, you can move the DoCmd.SendObject command inside the 'Do While' recordset loop. This will generate and send a separate email for each valid record, which helps prevent your emails from being flagged by spam filters.

Office Quick Solutions