VBA for Word - read specific data from MailMerge objects

Question

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	Department
FName1	LName1	N	A
FName2	LName2	Y	F
FName3	LName3	Y	C
FName4	LName4	N	A
FName5	LName5	N	B
FName6	LName6	Y	F

Word macro shuld loop through col "On vacation" and display a messagebox with the name and department of those who have "Y" in the "On vacation" field.

There are many MailMerge related objects like MailMergeDataField, MailMergeFields, MailMergeDataSource, etc, and i m yet clueless which one to use for this purpose.

In the mail merge Word template, the "On vacation" field will not be inserted. The macro will use it in the background to send http requests to a webservice backend.

kidd_ip · Accepted Answer

How about using MailMergeDataSource:
&nbsp;
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: " &amp; firstName &amp; " " &amp; lastName &amp; vbCrLf &amp; _
                       "Department: " &amp; department, vbInformation, "On Vacation"
            End If
        Next currentRecord
    Else
        MsgBox "No active Mail Merge data source found.", vbExclamation
    End If
End Sub
&nbsp;
&nbsp;

Forum Discussion

VBA for Word - read specific data from MailMerge objects

2 Replies

Resources