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.

Answer 1

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