VBA & Macro Problems

How to Use Access VBA Instead of DLookup for Multiple Emails

Partner Editor Sep 27, 2026 869 views

Question details

The user needs a reliable method to send emails to multiple vendors in Access because DLookup and basic macros are failing.

How to Use Access VBA Instead of DLookup for Multiple Vendor Emails

Product: Microsoft Access
Device & OS: not provided
Scenario: Automating email messages to multiple vendor addresses stored in an Access database.
Observed behavior: Macros using repeated DLookup functions fail with an unknown recipient error due to unnormalized email fields and limited error handling.

Before you start

Before modifying your database structure or VBA scripts, create a backup copy of your Access database file (.accdb) to prevent accidental data loss during testing.

Solution 1Recommended

Use VBA Recordsets to Loop Through Recipients

Replace rigid DLookup macros with a VBA procedure that dynamically loops through a recordset, builds the recipient list, and safely executes the email command.

VBA is significantly more reliable than macros for complex email tasks because it fully supports recordset looping, data validation, and error handling. By iterating through your data, you can build a semicolon-separated string of email addresses before triggering the email client.

Open the VBA Editor

Press ALT + F11 in Microsoft Access to open the Visual Basic for Applications (VBA) editor.

Create a New Module

Click 'Insert' from the top menu and select 'Module' to start a blank script.

Declare DAO Recordset Variables

Set up your variables using 'Dim db As DAO.Database' and 'Dim rs As DAO.Recordset' to prepare for querying the vendor email table.

Loop Through the Records

Write a 'Do While Not rs.EOF' loop. Inside the loop, concatenate each valid email address into a single string variable, separated by semicolons.

Send the Email

Use 'DoCmd.SendObject' after building the recipient string to generate and dispatch the message. Include error-handling routines (e.g., 'On Error GoTo ErrorHandler') to catch unexpected failures.

Use VBA Recordsets to Loop Through Recipients

Error Handling Advantage: Unlike macros, VBA allows you to intercept missing email errors gracefully without crashing the entire database routine.

Solution 2

Normalize Your Database Email Fields

Redesign your table structure to store each email address as a separate record in a related table, abandoning repeating fields like Email1 and Email2.

Free Microsoft Office alternative

Experience Seamless Productivity with WPS Office

While complex relational databases are best managed in Microsoft Access, WPS Office offers a powerful, free, and lightweight suite for your everyday document, spreadsheet, and presentation tasks, complete with strong format compatibility.

1. Download the Installer: Visit the official WPS Office website and download the free installation package for your operating system.
2. Install WPS Office: Run the setup file and follow the straightforward prompts to install Writer, Spreadsheets, and Presentation.
3. Open Your Files: Launch WPS Spreadsheets to seamlessly open your existing Excel workbooks and execute your VBA scripts.

Highly compatible with Microsoft Excel (.xlsx), Word (.docx), and PowerPoint (.pptx) formats.Robust Spreadsheets application with advanced formulas and VBA macro support for data automation.Free, lightweight, and incredibly fast to install on any device.

microsoft office alternative - wps office

Frequently Asked Questions

Why does DLookup fail when sending emails to multiple recipients in Access?

DLookup is strictly designed to return a single value based on a specific criteria. Using it repeatedly in macros to check flat fields (like Email1, Email2) is inefficient, difficult to scale, and triggers 'unknown recipient' errors if a field returns Null or poorly formatted data.

What is the benefit of normalizing email fields for VBA loops?

Normalizing fields by migrating them to a related table (e.g., tblVendorEmails) allows a single vendor to have an unlimited number of email addresses. A VBA recordset can smoothly loop through this table to extract as many addresses as needed without altering the underlying table structure.

Does DoCmd.SendObject support sending to multiple email addresses at once?

Yes, DoCmd.SendObject can populate the 'To', 'Cc', or 'Bcc' fields with multiple addresses, provided they are formatted as a single, continuous string separated by semicolons (;). Building this string is easily achieved using a VBA loop.

Excel & Data Problems