How to Use VBA to Mail Merge From Custom Data Source

When Word’s built-in mail merge wizard cannot connect to your specific data source, such as a custom database, an API endpoint, or a proprietary file format, you need a more flexible approach. Visual Basic for Applications allows you to write code that pulls data from virtually any source and creates merged documents directly. This article explains how to use VBA to bypass the standard data source connections and build a mail merge from an in-memory array or custom file. You will learn the core VBA objects, the step-by-step code structure, and how to handle common errors when the data does not come from a standard Word-recognized source.

Understanding VBA Mail Merge With Custom Data

Word’s mail merge feature is designed to connect to data sources through Open Database Connectivity drivers, OLE DB, or directly to Excel, Access, Outlook contacts, and text files. When your data lives in a custom source, such as a JSON file, a SQLite database, a web service response, or a proprietary ERP system, the standard MailMerge.OpenDataSource method cannot interpret it. VBA solves this by letting you read the data yourself using any method, store it in an array or collection, and then loop through each record to create individual merged documents. The key is that you do not use a connected data source at all. Instead, you insert merge fields into the main document, then run the merge in a paused state, replacing each field with the next row of data manually. This approach works with any data format that VBA can parse, including text files, ADODB recordsets, or even arrays hardcoded in the macro.

Prerequisites for the VBA Mail Merge Macro

Before writing the macro, verify the following:

• You have a Word document with the main content and merge fields already inserted. For a custom data source, you must insert merge fields manually using the Insert > Quick Parts > Field > MergeField dialog, or via VBA code.
• Your custom data source is accessible from VBA. For example, if the data is in a text file, you can use the Open statement and Line Input to read it. If the data is in a database, you can use ADO with a connection string.
• Macros are enabled in Word. Go to File > Options > Trust Center > Trust Center Settings > Macro Settings and select Enable all macros for testing.
• You know the field names you will use in the merge document. These names must match the keys in your custom data structure.

Steps to Create a VBA Mail Merge From a Custom Data Source

The following macro reads data from a comma-separated text file and creates a separate merged document for each record. You can adapt the reading part to any data source.

1. Open the Visual Basic Editor
   Press Alt+F11 in Word to open the VBA editor. In the Project Explorer, locate your document project and insert a new module by clicking Insert > Module.
2. Declare variables and the main subroutine
   Start the subroutine with the required variable declarations. The example below uses a text file as the data source. Replace the file path with your own data file.
   Sub MailMergeFromCustomDataSource()
Dim wdDoc As Document
Dim wdMerge As MailMerge
Dim sData() As String
Dim vRecord As Variant
Dim i As Long
Dim sLine As String
Dim iFile As Integer
Dim aFields() As String
Dim sFieldNames() As String
Dim sFieldValues() As String
Dim j As Long
Set wdDoc = ActiveDocument
Set wdMerge = wdDoc.MailMerge
3. Read the custom data file into an array
   Open the text file, read all lines, and store them in an array. The first line contains field names. Subsequent lines contain data values separated by commas. This example assumes the file is in the same folder as the document.
   iFile = FreeFile
Open ThisDocument.Path & "\data.txt" For Input As #iFile
sLine = Input(LOF(iFile), iFile)
Close #iFile
sData = Split(sLine, vbCrLf)
' First line is field names
sFieldNames = Split(sData(0), ",")
' Remove empty lines at end
If Right(sData(UBound(sData)), 1) = vbCr Then
ReDim Preserve sData(UBound(sData) - 1)
End If
4. Insert merge fields into the main document
   If the document does not already have merge fields, insert them at the beginning. This code adds fields for each column name. Run this section only once per document setup.
   ' Optional: Insert merge fields at the top of the document
wdDoc.Range.InsertBefore "Dear "
For j = 0 To UBound(sFieldNames)
wdDoc.MailMerge.Fields.Add wdDoc.Range, sFieldNames(j)
If j < UBound(sFieldNames) Then wdDoc.Range.InsertText ", "
Next j
wdDoc.Range.InsertParagraphAfter
5. Loop through each data row and create merged documents
   For each data row after the header, split the values, then execute the mail merge with Pause=True to insert the values manually. The merge creates a new document for each record.
   For i = 1 To UBound(sData)
If Len(sData(i)) > 0 Then
sFieldValues = Split(sData(i), ",")
With wdMerge
.Destination = wdSendToNewDocument
.Execute Pause:=True
End With
' The paused merge opens a new document with merge fields
' Replace each merge field with the corresponding value
For j = 0 To UBound(sFieldNames)
With wdDoc.MailMerge.DataSource
.FindRecord FindText:=sFieldNames(j), Field:=sFieldNames(j)
If .Found Then
.SetAllIncludedFlags False
.SetAllIncludedFlags True
End If
End With
Next j
' Save or print the new document as needed
ActiveDocument.SaveAs2 FileName:=ThisDocument.Path & "\Merged_" & i & ".docx"
ActiveDocument.Close
End If
Next i
6. Clean up and end the subroutine
   Release object references and display a completion message.
   Set wdDoc = Nothing
Set wdMerge = Nothing
MsgBox "Mail merge completed. Created " & (UBound(sData)) & " documents."
End Sub

Common Issues When Using VBA for Custom Data Mail Merge

Word Freezes or Crashes When Running the Macro

This usually happens when the data file is very large and the macro tries to process thousands of records in one loop. To avoid this, process records in batches of 100 and call DoEvents after each batch to release system resources. Also, ensure that the main document does not contain complex formatting or images that slow down the merge.

Merge Fields Are Not Replaced With Data

If the merged document still shows field codes like “{ MERGEFIELD FirstName }” instead of actual values, the Execute Pause:=True method may not be working as expected. Instead of using Pause, you can manually replace the fields in the new document after the merge completes. Use a loop that goes through all fields in the new document and replaces each with the corresponding value from your array.

The Data File Contains Headers or Extra Spaces

If your custom data source has a header row that should not be treated as data, skip the first line in the loop. Use Trim on each field value to remove leading and trailing spaces. For CSV files with quoted fields, use a proper CSV parser or replace commas inside quotes with a placeholder before splitting.

VBA Mail Merge With Custom Data vs Standard Mail Merge

Feature	Standard Mail Merge	VBA Custom Data Source
Data source types	Excel, Access, Outlook, text files, SQL Server	Any data VBA can read: JSON, API, custom DB, arrays
Setup complexity	Use wizard or OpenDataSource	Write and debug VBA code
Performance with large data	Fast, handled by Word internally	Slower, each record processed individually
Error handling	Built-in error messages	Must add On Error statements manually
Flexibility	Limited to supported connectors	Full control over data reading and transformation

For most business users, the standard mail merge wizard is sufficient when the data is in Excel or Access. Use the VBA approach only when your data source is not supported by Word’s built-in connectors, or when you need to apply complex data transformations before merging.

You can now create a mail merge macro that reads data from any source VBA can access, such as a text file, a database query, or an array. Start by testing the macro with a small sample of records to verify the merge field names match your data keys. For a more advanced solution, consider using the MailMerge.DataSource.EditRecord method to edit records programmatically instead of pausing the merge. One concrete tip: when reading CSV data, use the Microsoft Scripting Runtime library’s FileSystemObject to read files line by line, which handles large files more efficiently than the Input statement.