VBA for Word - read specific data from MailMerge objects

I m wondering how to retrieve the values of a specific column in the mail merge datasource with VBA. Datasource is an Excel file like this: First name Last name On vacation Depar...

VBA

word

Kidd_Ip

Apr 08, 2025

How about using MailMergeDataSource:

Sub ProcessMailMergeData()
    Dim mmDataSource As MailMergeDataSource
    Dim totalRecords As Long
    Dim currentRecord As Long
    Dim firstName As String
    Dim lastName As String
    Dim department As String
    Dim onVacation As String
    
    ' Ensure the document has an active mail merge data source
    If Not ActiveDocument.MailMerge.DataSource Is Nothing Then
        Set mmDataSource = ActiveDocument.MailMerge.DataSource
        totalRecords = mmDataSource.RecordCount
        
        ' Loop through each record in the data source
        For currentRecord = 1 To totalRecords
            mmDataSource.ActiveRecord = currentRecord
            
            ' Retrieve values from specific fields
            firstName = mmDataSource.DataFields("First name").Value
            lastName = mmDataSource.DataFields("Last name").Value
            onVacation = mmDataSource.DataFields("On vacation").Value
            department = mmDataSource.DataFields("Department").Value
            
            ' Check "On vacation" status and display the relevant info
            If onVacation = "Y" Then
                MsgBox "Name: " & firstName & " " & lastName & vbCrLf & _
                       "Department: " & department, vbInformation, "On Vacation"
            End If
        Next currentRecord
    Else
        MsgBox "No active Mail Merge data source found.", vbExclamation
    End If
End Sub

Sub ProcessMailMergeData() Dim mmDataSource As MailMergeDataSource Dim totalRecords As Long Dim currentRecord As Long Dim firstName As String Dim lastName As String Dim department As String Dim onVacation As String ' Ensure the document has an active mail merge data source If Not ActiveDocument.MailMerge.DataSource Is Nothing Then Set mmDataSource = ActiveDocument.MailMerge.DataSource totalRecords = mmDataSource.RecordCount ' Loop through each record in the data source For currentRecord = 1 To totalRecords mmDataSource.ActiveRecord = currentRecord ' Retrieve values from specific fields firstName = mmDataSource.DataFields("First name").Value lastName = mmDataSource.DataFields("Last name").Value onVacation = mmDataSource.DataFields("On vacation").Value department = mmDataSource.DataFields("Department").Value ' Check "On vacation" status and display the relevant info If onVacation = "Y" Then MsgBox "Name: " & firstName & " " & lastName & vbCrLf & _ "Department: " & department, vbInformation, "On Vacation" End If Next currentRecord Else MsgBox "No active Mail Merge data source found.", vbExclamation End If End Sub

Forum Discussion

VBA for Word - read specific data from MailMerge objects

Resources