Mailings - Save many PDF and Word files from a Word template & Excel List

Creating many documents from a template and using a data source to populate those templates is easily accomplished with mailings! In this article, we’ll demo how to add a macro to save each instance as a PDF and a Word document.

The Concept

When you have many records, where only 1 row gets populated into a template. This is called a simple mail merge (1 row to 1 template). It is possible to combine many rows into 1 template (e.g. transcripts, weekly schedules etc) but that’s not covered here. Images can also be mail merged, not covered here.

The Source

Get your source file ready. This should be an excel file, with the first row containing headers. No empty columns, no unnamed columns. Here is a good looking sample. Sheet names will come relevant. If you have multiple sheets, take note.

The Template

Get your sample file ready. This should resemble the final output you hope for. Common ideas are:

• Business/Contact cards/Staff ID Cards
• Product info sheets
• Bank statements
• SHAs? Or other long form documents.

The Link

Now link the two files. Starting from scratch, where both files are closed. Open the word document. Go to the Mailings Tab > Start Mail Merge > Letters This defines your word document as a template type for mail merge.

Then Use an Existing List to Select Receipents.

Pick the file

Pick the sheet within the file.

Insert fields as necessary.

Note the handy features for highlighting and preview. Image above with preview on. Image below preview off.

The Interim File

Let’s now use the default word functionality to generate an interim file. This will instantiate our template with the chosen records.

In this example, out of a total of 193 records in my Source File, I shall instantiate records number 77 thru 103.

Lets pay attention to what just happened:

A new word document, yet unsaved, has been generated, containing many instances of our template. Our template consisted of a few lines and a table. Note the template being repeated. If our template had been a multi page contract, it would have simply repeated the full set over and over. Feel free to format your template with styles, fonts, colours etc. Note that record #77 appears in row #78, as the first row is the header row.

Viewing the hidden formatting characters in our template file and the interim file side-by-side, note that the interim file generated simply tacked on multiple instances of the template file with section breaks. We’ll be capitalising on this. Close the template file. Save the interim file. The interim file should be the only word document open on your PC. Your split files will end up here as well. Run the macro from within your interim file. Developer Tab > Macros > Run

The Macro

We’ll be using a macro to split the merged document and save it as both a PDF and a Word document. The macro will first prompt you to enter number of section breaks in your template. This could come in handy if your template is complex with multiple sections for each record. Our example is simple and there are no internal sections per record (one section break per record). The saved files will simply be named using the 1st paragraph of the generated files. This can be changed or customized. Field data can be used as well. The generated files will be saved at the same location as your interim file. Initial Setup of the Macro Add the Developer tab to the ribbon.

Add a macro name and Create the following macro.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61

Sub SplitMergedDocument()
' Sourced from: https://www.msofficeforums.com/mail-merge/21803-mailmerge-tips-tricks.html
Application.ScreenUpdating = False
Dim i As Long, j As Long, k As Long, StrTxt As String
Dim Rng As Range, Doc As Document, HdFt As HeaderFooter
Const StrNoChr As String = """*./\:?|"
j = InputBox("How many Section breaks are there per record?", "Split By Sections", 1)
With ActiveDocument
   ' Process each Section
  For i = 1 To .Sections.Count - 1 Step j
    With .Sections(i)
       '*****
       'ALTERNATIVE 1 - Just Numbers
       ' Construct the destination file path & name
       ' StrTxt = ActiveDocument.Path & "\" & (i + j - 1) / j
       'ALTERNATIVE 2 - Use 1st Paragraph Text as filename
       ' Get the 1st paragraph's text
      StrTxt = Split(.Range.Paragraphs(1).Range.Text, vbCr)(0)
      For k = 1 To Len(StrNoChr)
        StrTxt = Replace(StrTxt, Mid(StrNoChr, k, 1), "_")
      Next
       ' Construct the destination file path & name
      StrTxt = ActiveDocument.Path & "\" & StrTxt
       '*****
       ' Get the whole Section
      Set Rng = .Range
      With Rng
        If j > 1 Then .MoveEnd wdSection, j - 1
         'Contract the range to exclude the Section break
        .MoveEnd wdCharacter, -1
         ' Copy the range
        .Copy
      End With
    End With
     ' Create the output document
    Set Doc = Documents.Add(Template:=ActiveDocument.AttachedTemplate.FullName, Visible:=False)
    With Doc
       ' Paste contents into the output document, preserving the formatting
      .Range.PasteAndFormat (wdFormatOriginalFormatting)
       ' Delete trailing paragraph breaks & page breaks at the end
      While .Characters.Last.Previous = vbCr Or .Characters.Last.Previous = Chr(12)
        .Characters.Last.Previous = vbNullString
      Wend
       ' Replicate the headers & footers
      For Each HdFt In Rng.Sections(j).Headers
        .Sections(j).Headers(HdFt.Index).Range.FormattedText = HdFt.Range.FormattedText
      Next
      For Each HdFt In Rng.Sections(j).Footers
        .Sections(j).Footers(HdFt.Index).Range.FormattedText = HdFt.Range.FormattedText
      Next
       ' Save & close the output document
      .SaveAs FileName:=StrTxt & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
       ' and/or:
      .SaveAs FileName:=StrTxt & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
      .Close SaveChanges:=False
    End With
  Next
End With
Set Rng = Nothing: Set Doc = Nothing
Application.ScreenUpdating = True
End Sub

See this source for additional examples of merge tricks at MS Office Forums here.

The Result

Viola! Might take a while to complete but in the end you will have something like this.

Notice Word AND PDF files generated for each record, and named using the first paragraph of the document. Enjoy.