Anna's notepad

Mail merge output to different folders

Jump to macro

I found the base macro in this MS Office Forums thread. This post is to add my own usage tips; hopefully they will save you from the same trial and error I went through.

Assumed knowledge:

Notes:

What follows is the VBA broken up with my notes. At the end the entire code is repeated for easy copy-pasting.

Sub MailMergeToDoc()
' Collects the results of the mail merge in a document

Apparently, the name of the macro is important. In the thread linked above, the macro is called Merge_To_Individual_Files, but OP states "If you rename the above macro as MailMergeToDoc, clicking on the 'Edit Individual Documents' button will intercept the merge and the process will run automatically." How does the name of the macro affect anything? I don't know.

Application.ScreenUpdating = False

Turns off the screen re-drawing with every action, which can save time when running a macro that goes through a lot of records. It needs to be set back to TRUE at the end of the macro. It seems silly that you the user have to do that, though.

Dim StrFolder As String, StrName As String, MainDoc As Document, i As Long, j As Long

Some variables, innit. We're telling the computer 'reserve some pieces of your memory for us to use, and we'll refer to them with these labels'. A String is some text, and a Long is a big number (as opposed to an Int, which 'only' goes up to 2 billion. That's probably overkill here, but whatever, I didn't write the macro).

Const StrNoChr As String = """*./\:?|"

Characters that we can't have or at least don't want in filenames.

Set MainDoc = ActiveDocument
With MainDoc
  'StrFolder = .Path & "\" 
  'Uncomment to use macro's own folder

If you just want to save all the PDFs into the same folder as the Word doc (i.e. the one that we're running this macro from) you can uncomment this line (remove the ') and use that. The .Path tells the macro to use whatever folder it's already in. The "\" needs to be manually added because computers are so stupid and will scream and cry if you don't spoon-feed them absolutely everything.

(Why do we have to dick about with creating a Document variable ('MainDoc') and then setting it to ActiveDocument and calling that - instead of just telling the macro With ActiveDocument? It's because we had to first tell the computer to reserve some pieces of memory, and then tell it what to put there.)

  With .MailMerge
    .Destination = wdSendToNewDocument
    .SuppressBlankLines = True

.SuppressBlankLines is a built-in command for use in MailMerge macros. I assume it acts as a failsafe in case you forgot to exclude blank entries in the 'Edit Recipient List' tools.

wdSendToNewDocument does pretty much what you'd expect.

    On Error Resume Next
    For i = 1 To .DataSource.RecordCount
      With .DataSource
        .FirstRecord = i
        .LastRecord = i
        .ActiveRecord = i

This is the start of a 'for' loop (tell the computer to do this thing until it can't do it any more).

        If Trim(.DataFields("Personnel_name")) = "" Then Exit For
        StrFolder = .DataFields("outputFolder") & "\"
            If Dir(StrFolder) = "" Then MkDir StrFolder
        StrName = .DataFields("outputFile")

      End With
      On Error GoTo NextRecord
      .Execute Pause:=False
      For j = 1 To Len(StrNoChr)
        StrName = Replace(StrName, Mid(StrNoChr, j, 1), "_")

Anything from StrNoChr earlier gets replaced by an underscore _.

      Next
      StrName = Trim(StrName)
      With ActiveDocument
        'Add the name to the footer
       '.Sections(1).Footers(wdHeaderFooterPrimary).Range.InsertBefore StrName

If you want the filename in the document footer, uncomment these lines (remove the ' at the start). I've never bothered to use it.

        .SaveAs FileName:=StrFolder & StrName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False

If you want the output files to be Word docs, change ".pdf" to ".docx" and wdFormatPDF to... wdFormatXMLDocument. Word (and Excel) files are just XML files under the bonnet! (Take a random Word/Excel file that you don't mind sacrificing, change its file extension to zip, then unzip it and see what's inside!)

If you want to save the output files as docx and PDF, duplicate the line and edit so you've gone one line with ".pdf" and wdFormatPDF and another with ".docx" and wdFormatXMLDocument.

        .Close SaveChanges:=False
      End With
NextRecord:
    Next i
  End With
End With
Application.ScreenUpdating = True
End Sub

Some gubbins to wrap everything up.

Whole thing

Sub MailMergeToDoc()
'
' MailMergeToDoc Macro
' Collects the results of the mail merge in a document
' Sourced from: https://www.msofficeforums.com/mail-merge/21803-mailmerge-tips-tricks.html

Application.ScreenUpdating = False
Dim StrFolder As String, StrName As String, MainDoc As Document, i As Long, j As Long
Const StrNoChr As String = """*./\:?|"
Set MainDoc = ActiveDocument
With MainDoc
  'StrFolder = .Path & "\" 
  'Uncomment to use macro's own folder
  With .MailMerge
    .Destination = wdSendToNewDocument
    .SuppressBlankLines = True
    On Error Resume Next
    For i = 1 To .DataSource.RecordCount
      With .DataSource
        .FirstRecord = i
        .LastRecord = i
        .ActiveRecord = i
        If Trim(.DataFields("Personnel_name")) = "" Then Exit For
        'StrFolder = .DataFields("outputFolder") & "\"
        '    If Dir(StrFolder) = "" Then MkDir StrFolder
        StrName = .DataFields("outputFile")
      End With
      On Error GoTo NextRecord
      .Execute Pause:=False
      For j = 1 To Len(StrNoChr)
        StrName = Replace(StrName, Mid(StrNoChr, j, 1), "_")
      Next
      StrName = Trim(StrName)
      With ActiveDocument
        'Add the name to the footer
        '.Sections(1).Footers(wdHeaderFooterPrimary).Range.InsertBefore StrName
        .SaveAs FileName:=StrFolder & StrName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
        .Close SaveChanges:=False
      End With
NextRecord:
    Next i
  End With
End With
Application.ScreenUpdating = True
End Sub

#MS Word #VBA #mail merge