Group Word Mail Merge Records into One Document per Manager
Question details
The user needs a Word mail merge macro to create one document per manager containing all employees assigned to that manager.

- Product
- Microsoft Word mail merge and VBA
- Device & OS
- not provided
- Scenario
- A mail merge data source contains employee records associated with managers.
- Observed behavior
- The existing macro creates a separate document for each employee instead of grouping employees under their manager.
Sort the merge data source by the Updated_Manager_01 field so each manager's records are contiguous, then reconnect or refresh it in Word. Save the main merge document in an existing folder, confirm every manager name is populated, and test with a small unfiltered data set.
Merge each contiguous manager group with a Word VBA macro
Use this approach when the data source is sorted by manager and you want Word to save one DOCX file for each group.
The macro identifies the first and last record for each manager, merges that range to a new document, and saves it in the main document's folder. It prefixes and sanitizes filenames and adds a number if a file already exists.
The example groups by Updated_Manager_01 only. If managers can share the same name, use a unique manager ID as the grouping field instead. Remove recipient filters before testing, and verify the results if you use a data source or filter that changes record numbering.
Sort the source file by Updated_Manager_01. In Word, open Mailings > Edit Recipient List to confirm that records for each manager appear together and that the field name matches the macro.
Open the main merge document, press Alt+F11 to open the VBA editor, and choose Insert > Module. Paste the following code into the module: Sub MergeByManager() Dim mainDoc As Document Dim ds As MailMergeDataSource Dim recordCount As Long, i As Long, first As Long Dim currentName As String, groupName As String On Error GoTo Failed Set mainDoc = ActiveDocument If mainDoc.Path = "" Then Err.Raise vbObjectError + 1, , "Save the main merge document first." Set ds = mainDoc.MailMerge.DataSource recordCount = ds.RecordCount If recordCount < 1 Then Err.Raise vbObjectError + 2, , "No records found, or the data source cannot report a record count." Application.ScreenUpdating = False For i = 1 To recordCount + 1 If i <= recordCount Then ds.ActiveRecord = i currentName = Trim$(CStr(ds.DataFields("Updated_Manager_01").Value)) If currentName = "" Then Err.Raise vbObjectError + 3, , "A manager name is blank at record " & i & "." Else currentName = "" End If If i = 1 Then first = 1 groupName = currentName ElseIf i = recordCount + 1 Or StrComp(currentName, groupName, vbTextCompare) <> 0 Then SaveManagerGroup mainDoc, first, i - 1, groupName first = i groupName = currentName End If Next i Application.ScreenUpdating = True MsgBox "Manager documents created in " & mainDoc.Path Exit Sub Failed: Application.ScreenUpdating = True MsgBox "Merge stopped: " & Err.Description, vbExclamation End Sub Private Sub SaveManagerGroup(ByVal mainDoc As Document, ByVal first As Long, ByVal last As Long, ByVal managerName As String) Dim fileBase As String, filePath As String, suffix As Long Dim invalidChars As String, ch As String Dim i As Long, mergedDoc As Document invalidChars = "\/:*?""<>|" fileBase = "Manager_" For i = 1 To Len(managerName) ch = Mid$(managerName, i, 1) If InStr(invalidChars, ch) > 0 Or AscW(ch) < 32 Then ch = "_" fileBase = fileBase & ch Next i fileBase = Left$(fileBase, 100) Do While Right$(fileBase, 1) = "." Or Right$(fileBase, 1) = " " fileBase = Left$(fileBase, Len(fileBase) - 1) Loop filePath = mainDoc.Path & Application.PathSeparator & fileBase & ".docx" suffix = 2 Do While Dir$(filePath) <> "" filePath = mainDoc.Path & Application.PathSeparator & fileBase & "_" & suffix & ".docx" suffix = suffix + 1 Loop With mainDoc.MailMerge .Destination = wdSendToNewDocument .SuppressBlankLines = True .DataSource.FirstRecord = first .DataSource.LastRecord = last .Execute Pause:=False End With Set mergedDoc = ActiveDocument mergedDoc.SaveAs2 FileName:=filePath, FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False mergedDoc.Close SaveChanges:=False End Sub
Return to Word, select the main merge document, and run MergeByManager from View > Macros. Open the resulting DOCX files in the main document's folder and check that each contains all employees for the named manager.

Work with mail merge documents in WPS Office
WPS Office is a free, lightweight alternative with a familiar interface for working with Office documents and moving existing files into a different office suite. The Word VBA macro above is a Microsoft Word solution and should not be assumed to run unchanged in WPS.

Frequently Asked Questions
Why does the current macro create one file per employee?
It sets both MailMerge.DataSource.FirstRecord and LastRecord to the same record number before each merge. Set them to the first and last records in a manager's group instead.
Why must the data source be sorted by manager?
The macro detects a group when the manager field changes. Sort Updated_Manager_01 in the source and check Mailings > Edit Recipient List so the same manager does not appear in separate blocks.
What happens if two managers have the same name?
The example treats adjacent records with the same manager name as one group. If names are not unique, sort and group by a unique manager ID instead of Updated_Manager_01.
Where are the merged documents saved?
The macro saves them in the folder containing the main merge document. Save that document first; if an output filename already exists, the macro adds a numeric suffix.
What if the macro reports that the record count is unavailable?
Some data sources cannot report a usable RecordCount. Check the connection under Mailings > Select Recipients and test with a source that exposes a record count before running this macro.




