How to split a mail merge and save files with a merge field as the name

Question

I have a bunch of mail merge templates setup, when I merge the documents I want to split the results into separate files each one with a name based on the merge field “FileNumber”.

The code I have currently is:

Sub splitter()
' Based on a macro by Doug Robbins to save each letter created by a mailmerge as a separate file.
' With help from http://www.productivitytalk.com/forums/topic/3927-visual-basic-question-for-merge-fields/

Dim i As Integer
Dim Source As Document
Dim Target As Document
Dim Letter As Range
Dim oField As Field
Dim FileNum As String

Set Source = ActiveDocument

For i = 1 To Source.Sections.Count
    Set Letter = Source.Sections(i).Range
    Letter.End = Letter.End - 1
        For Each oField In Letter.Fields
        If oField.Type = wdFieldMergeField Then
            If InStr(oField.Code.Text, "FileNumber") > 0 Then
            'get the result and store it the FileNum variable
            FileNum = oField.Result
            End If
        End If
        Next oField
    Set Target = Documents.Add
    Target.Range = Letter
    Target.SaveAs FileName:="C:\Temp\Letter" & FileNum
    Target.Close
    Next i
End Sub

The problem is if I “Merge to new document” then the “FileNumber” field no longer exists so it can’t pick that up but if I just go to “Preview Results” and run the macro it only saves the currently previewed record and not the rest of the letters.

I’m assuming I need to change the code to something like

For i = 1 To Source.MergedRecord.Count
    Set Letter = Source.MergedRecord(i).Range

but I can't work out the correct syntax.

I am aware of http://www.gmayor.com/individual_merge_letters.htm but I don't want the dialog boxes I just want a one click button.

If possible, please review and mark an answer that you find most suitable. Thanks! — swarajk1, Nov 13 '15 at 21:30

score 5 · Answer 1 · answered Mar 19 '15 at 20:38

In the Mail merge template document, paste the following macro code in "ThisDocument" module:

Dim WithEvents wdapp As Application
Dim bCustomProcessing As Boolean

Private Sub Document_Open()

Set wdapp = Application
bCustomProcessing = False
ThisDocument.MailMerge.DataSource.ActiveRecord = 1
ThisDocument.MailMerge.ShowWizard 1
With ActiveDocument.MailMerge
   If .MainDocumentType = wdFormLetters Then
       .ShowSendToCustom = "Custom Letter Processing"
   End If
End With

End Sub
Private Sub wdapp_MailMergeWizardSendToCustom(ByVal Doc As Document)

bCustomProcessing = True
Doc.MailMerge.Destination = wdSendToNewDocument
With Doc.MailMerge
    For rec = 1 To .DataSource.RecordCount
        .DataSource.ActiveRecord = rec
        .DataSource.FirstRecord = rec
        .DataSource.LastRecord = rec
        .Execute
    Next
End With

MsgBox "Merge Finished"
End Sub


Private Sub wdapp_MailMergeAfterMerge(ByVal Doc As Document, ByVal DocResult As Document)
If bCustomProcessing = True Then
    With Doc.MailMerge.DataSource.DataFields
        sFirmFileName = .Item(1).Value ' First Column of the data - CHANGE
    End With
    DocResult.SaveAs "c:\path\" & sFirmFileName & ".docx", wdFormatXMLDocument
     ' Path and File Name to save. can use other formats like wdFormatPDF too
    DocResult.Close False
End If
End Sub

Remember to update the column number to use for file names, and the path to save the generated files.

After writing this code, save and close the merge template doc. Re-open the file and this time you will be prompted with the Merge wizard. Proceed as required for the Letter, and at the last step, select "Custom Letter Processing" option instead of finishing merge. This will save the separate merged docs in specified folder.

Please remember that this code can be heavy on the processor.

The script runs fine but not changing the destination file name. — ViKiNG, Mar 06 '18 at 00:34
@ViKiNG I hope you have the file names in the first column of your data table, or otherwise change the column number from 1 to your desired column number for sFirmFileName in the last sub. — swarajk1, Mar 11 '18 at 03:01

score 4 · Accepted Answer · answered Jul 07 '13 at 11:34

There is a simple solution not involving splitting the resulting document: Prepare the merge and staying in the template document.Record a macro as you merge one record, then save and close the resulting file, eventuallye advance to the next record.

See the generated macro below. I have added very little code just to extract the filename from a field in the datasource (which is accessible in the template document).

Assign the macro to a shortcut key or implement a loop in VBA. Observe that the fieldnames are casesensitive.

Regards, Søren

Sub flet1()
'
' flet1 Makro
' 1) Merges active record and saves the resulting document named by the datafield     FileName"
' 2) Closes the resulting document, and (assuming that we return to the template)
' 3) advances to the next record in the datasource
'
'Søren Francis 6/7-2013

    Dim DokName  As String   'ADDED CODE

    With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = ActiveDocument.MailMerge.DataSource.ActiveRecord
            .LastRecord = ActiveDocument.MailMerge.DataSource.ActiveRecord
' Remember the wanted documentname
           DokName = .DataFields("FileName").Value         ' ADDED CODE
        End With

' Merge the active record
        .Execute Pause:=False
    End With

' Save then resulting document. NOTICE MODIFIED filename
    ActiveDocument.SaveAs2 FileName:="C:\Temp\" + DokName + ".docx", FileFormat:= _
        wdFormatXMLDocument, LockComments:=False, Password:="", AddToRecentFiles _
        :=True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts _
        :=False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
        SaveAsAOCELetter:=False, CompatibilityMode:=14

' Close the resulting document
    ActiveWindow.Close

' Now, back in the template document, advance to next record
    ActiveDocument.MailMerge.DataSource.ActiveRecord = wdNextRecord
End Sub

Could you please update the code implementing the loop for all records in the Merge? That would complete the answer in all respects as it currently works as expected, although record by record. — swarajk1, Mar 19 '15 at 16:53

score 1 · Answer 3 · answered Oct 02 '12 at 00:45

1

Thanks for that roryspop,

I ended up swapping the for loop with

Set Source = ActiveDocument

'The for loop was "To ActiveDocument.MailMerge.DataSource.RecordCount" but for
'some reason RecordCount returned -1 every time, so I set ActiveRecord 
'to wdLastRecord and then use that in the for loop.
ActiveDocument.MailMerge.DataSource.ActiveRecord = wdLastRecord

For i = 1 To ActiveDocument.MailMerge.DataSource.ActiveRecord
    ActiveDocument.MailMerge.DataSource.ActiveRecord = i
    Set Letter = Source.Range
        For Each oField In Letter.Fields

The rest of the code is the same, it's not very neat and I'm sure there must be a better way of doing things but it works.

answered Oct 02 '12 at 00:45

Hybrid

576
3
6
10

Yes, it would be nice if the output filename could be a field in the merge data document. I end up just running a using Excel to generate text for a batch rename. – roryspop Oct 10 '12 at 13:52
1

You can get the values of mergefields during the merge using active document.mailmerge.datasource.datafields("the exact name of the field - it's case sensitive").value, and use that to construct an output file pathname for each file. – Nov 18 '12 at 08:53

score 1 · Answer 4 · answered Mar 22 '13 at 16:21

The accepted solution did not work for me. I am using Word 2010. I managed to get a solution working and would like to share it here, so others can benefit from it:

'purpose: save each letter generated after mail merge in a separate file
'         with the file name equal to first line of the letter.
'
'1. Before you run a mail merge make sure that in the main document you will 
'   end your letter with a Section Break (this can be found under 
'   Page Layout/Breaks/Section Break Next Page)
'2. Furthermore the first line of your letter contains the proposed file name
'   and put an enter after it. Make the font of the filename white, to make it 
'   is invisible to the receiver of the letter. You can also include a folder 
'   name if you like.
'3. Run the mail merge as usual. A file which contains all the letters is 
'   generated.
'4. Add this module to the generated mail merge file. Use Alt-F11 to go to the 
'   visual basic user interface, right click in the left pane on the generated
'   file and click on Import File and import this file
'5. save the generate file with all the letters as ‘Word Macro Enabled doc 
'   (*.docm)’.
'6. close the file.
'7. open the file again, click allow content when a warning about macro's is 
'   shown.
'8. execute the macro with the name SaveRecsAsFiles


Sub SaveRecsAsFiles()
    ' Convert all sections to Subdocs
    AllSectionsToSubDoc ActiveDocument
    'Save each Subdoc as a separate file
    SaveAllSubDocs ActiveDocument
End Sub

Private Sub AllSectionsToSubDoc(ByRef doc As Word.Document)
    Dim secCounter As Long
    Dim NrSecs As Long
    NrSecs = doc.Sections.Count
    'Start from the end because creating
    'Subdocs inserts additional sections
    For secCounter = NrSecs - 1 To 1 Step -1
        doc.Subdocuments.AddFromRange _
          doc.Sections(secCounter).Range
    Next secCounter
End Sub

Private Sub SaveAllSubDocs(ByRef doc As Word.Document)
    Dim subdoc As Word.Subdocument
    Dim newdoc As Word.Document
    Dim docCounter As Long
    Dim strContent As String, strFileName As String

    docCounter = 1
    'Must be in MasterView to work with
    'Subdocs as separate files
    doc.ActiveWindow.View = wdMasterView
    For Each subdoc In doc.Subdocuments
        Set newdoc = subdoc.Open
        'retrieve file name from first line of letter.
        strContent = newdoc.Range.Text
        strFileName = Mid(strContent, 1, InStr(strContent, Chr(13)) - 1)
        'Remove NextPage section breaks
        'originating from mailmerge
        RemoveAllSectionBreaks newdoc
        With newdoc
            .SaveAs FileName:=strFileName
            .Close
        End With
        docCounter = docCounter + 1
    Next subdoc
End Sub

Private Sub RemoveAllSectionBreaks(doc As Word.Document)
    With doc.Range.Find
        .ClearFormatting
        .Text = "^b"
        With .Replacement
            .ClearFormatting
            .Text = ""
        End With
        .Execute Replace:=wdReplaceAll
    End With
End Sub

Part of the code I copied from here

How to split a mail merge and save files with a merge field as the name

4 Answers4

Linked