VBA & Macro Problems

How to Copy Access Query Results Without Field Headers Using VBA

Elise Williams Oct 14, 2026 869 views

Question details

Extract specific data records, such as email addresses, from an Access query without including the field headers.

How to Copy Access Query Results Without Field Headers Using VBA

Product: Microsoft Access
Device & OS: not provided
Scenario: Automating the extraction of email addresses from a database query to dynamically populate the To or BCC field in an email.
Observed behavior: When copying all records directly from an Access query, the field headers (like 'email') are copied along with the data, preventing the address list from working correctly in automated email fields.

Before you start

Ensure you have an existing Access query that returns a column of email addresses, and verify that you have basic familiarity with navigating the Microsoft Access VBA Editor.

Solution 1Recommended

Extract Email Addresses Using a DAO Recordset in VBA

By opening the query in VBA as a DAO recordset, you can iterate through the rows programmatically to construct a clean, semicolon-separated list of addresses without the field headers.

Instead of attempting to copy and paste query results manually, using Data Access Objects (DAO) in VBA allows you to read just the values row by row.

You can concatenate these values into a single text string, stripping out any unnecessary trailing characters at the end, and then pass that string directly to your email application.

Declare DAO Variables

Open the VBA Editor by pressing Alt + F11. Insert a new module and declare your variables: Dim strTo As String, Dim db As DAO.Database, and Dim rs As DAO.Recordset.

Open the Recordset

Set your database object using Set db = CurrentDb(). Then, open your specific query by executing Set rs = db.OpenRecordset("Your_Query_Name"). Replace 'Your_Query_Name' with the actual query name.

Loop Through Query Results

Write a Do Until rs.EOF loop. Inside this loop, append each email address to your string: strTo = strTo & rs.Fields("E-Mail").Value & "; ". Advance the recordset by calling rs.MoveNext before closing the loop.

Trim the Trailing Semicolon

After the loop finishes, clean up the final string by removing the last semicolon and space using this logic: If Len(strTo) > 1 Then strTo = Left$(strTo, Len(strTo) - 2).

Pass to SendObject

Use your finished strTo variable as the To or BCC parameter in your DoCmd.SendObject method to dispatch the emails.

Privacy Recommendation: It is highly recommended to map the final strTo string to the BCC (Blind Carbon Copy) parameter instead of the To field. This ensures that bulk recipients cannot see each other's email addresses.

Free Microsoft Office alternative

Looking for a Free Office Suite? Try WPS Office

While WPS Office does not currently provide a direct database counterpart to Microsoft Access, it is an exceptionally powerful, free, and lightweight alternative for your spreadsheet, document, and presentation needs. If you regularly work with data extracts and CSV files, WPS Spreadsheet offers outstanding compatibility.

1. Download the Installer: Visit the official WPS Office website and click the Free Download button.
2. Run Setup: Open the downloaded installation file and follow the quick on-screen instructions to set up WPS Office on your computer.
3. Open WPS Spreadsheet: Launch WPS Spreadsheet to effortlessly manage, format, and filter your exported database information and CSV files.

100% format compatibility with Microsoft Excel, Word, and PowerPoint.Lightweight installation that runs smoothly even on older devices.A familiar, intuitive user interface that requires zero learning curve.Includes a powerful built-in PDF editor and format converter for free.

microsoft office alternative - wps office

Frequently Asked Questions

How can I send an individualized email for each record instead of one bulk email?

To send individualized messages, do not concatenate the email addresses into a single string. Instead, place your email sending command (such as DoCmd.SendObject) directly inside the 'Do Until rs.EOF' loop. This will generate and send a new email during each iteration.

Why is there an extra semicolon at the end of my email string?

Because the loop mechanically appends '; ' after every email address it reads, the final record will leave a trailing semicolon. You must use the Left$ function to trim the last two characters from the string once the loop is complete.

Can I use ADO instead of DAO for reading Access query results?

Yes. You can use ActiveX Data Objects (ADO) by declaring an ADODB.Recordset and looping through it. However, DAO (Data Access Objects) is natively optimized for local Microsoft Access databases and generally performs better for this specific task.

Excel & Data Problems