How to rename a word document according to a mail merge attribute in VBA?

Question

The title may be a bit gory, but here we are.

Currently I've got a word document that uses mail merge to insert two attributes from an Excel sheet (date and name). Once the merge is generated, I then have a macro to split each page of the resultant document into it's own separate document. The macro I'm using is just copied and pasted from VBA Express here, seen below.

Sub SplitIntoPages()
    Dim docMultiple As Document
    Dim docSingle As Document
    Dim rngPage As Range
    Dim iCurrentPage As Integer
    Dim iPageCount As Integer
    Dim strNewFileName As String

    Application.ScreenUpdating = False 'Makes the code run faster and reduces screen _
    flicker a bit.
    Set docMultiple = ActiveDocument 'Work on the active document _
    (the one currently containing the Selection)
    Set rngPage = docMultiple.Range 'instantiate the range object
    iCurrentPage = 1
     'get the document's page count
    iPageCount = docMultiple.Content.ComputeStatistics(wdStatisticPages)
    Do Until iCurrentPage > iPageCount
        If iCurrentPage = iPageCount Then
            rngPage.End = ActiveDocument.Range.End 'last page (there won't be a next page)
        Else
             'Find the beginning of the next page
             'Must use the Selection object. The Range.Goto method will not work on a page
            Selection.GoTo wdGoToPage, wdGoToAbsolute, iCurrentPage + 1
             'Set the end of the range to the point between the pages
            rngPage.End = Selection.Start
        End If
        rngPage.Copy 'copy the page into the Windows clipboard
        Set docSingle = Documents.Add 'create a new document
        docSingle.Range.Paste 'paste the clipboard contents to the new document
         'remove any manual page break to prevent a second blank
        docSingle.Range.Find.Execute Findtext:="^m", ReplaceWith:=""
         'build a new sequentially-numbered file name based on the original multi-paged file name and path
        strNewFileName = Replace(docMultiple.FullName, ".doc", "_" & Right$("000" & iCurrentPage, 4) & ".doc")
        docSingle.SaveAs strNewFileName 'save the new single-paged document
        iCurrentPage = iCurrentPage + 1 'move to the next page
        docSingle.Close 'close the new document
        rngPage.Collapse wdCollapseEnd 'go to the next page
    Loop 'go to the top of the do loop
    Application.ScreenUpdating = True 'restore the screen updating

     'Destroy the objects.
    Set docMultiple = Nothing
    Set docSingle = Nothing
    Set rngPage = Nothing
End Sub

However, there are over 90 pages of the mail merge and, as seen in the code above, they are all named by just adding numbers to the end of the file name. Instead of this, I would like to have it so that it would read the merged Date attribute from each page and use that as the file name instead. I've tried tinkering around with the code and reading up about it on the MS Dev Centre, but I've had no luck.

Can anyone help? Thanks.

Where is this Date attribute? Is it a value in a cell? Have you discovered how to read this value? And by Name do you mean the name of the worksheet? — Andy G
I maybe wasn't clear, sorry. I'm importing two string fields from an excel sheet into word to mail merge - called Date and Name. The Date attribute I am referred to is what is pulled into the word document from the excel sheet via the mail merge. When I run the merge I get a long document. The code above takes each page and makes it its own document. Instead of using the naming convention there, I would like to use the Date as it is pulled in from the mail merge to name each document. — AgentNo
Have you discovered how to refer to this Date merge field? Once you can obtain its value you can just use it to assign a value to strNewFileName. — Andy G
I'm working off what is in the MS Office Dev Centre, but I'm having difficulty getting it working. I can see how to assign a merge field, but I can't figure out how to get it to change with each page without breaking the code completely. — AgentNo
There would be two basic approaches: 1) Knowing exactly where the data is located on each page so that the code can pick it up as part of the loop. 2) don't use mail merge; create each document for each record in Excel using automation (either Word VBA reads the Excel sheet or Excel writes to Word). There are lots of examples around for (2); no one here can help you with (1) since we have no idea how the mail merge result pages are structured / where the date is on the page. — Cindy Meister

macropod macropod · Accepted Answer · 2019-03-27T21:45:05

A far better approach is to create the separate documents from the outset. By adding the following macro to your mailmerge main document, you can generate one output file per record. Files are saved to the same folder as the mailmerge main document, using the 'Date' field in the data source for the filenames. PDF & DOCX output formats are catered for. Do be aware that, should your data source have duplicate dates, only the last one processed will survive.

Sub Merge_To_Individual_Files()
'Merges one record at a time to the folder containing the mailmerge main document.
' Sourced from: http://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
Set MainDoc = ActiveDocument
With MainDoc
  StrFolder = .Path & Application.PathSeparator
  For i = 1 To .MailMerge.DataSource.RecordCount
    With .MailMerge
      .Destination = wdSendToNewDocument
      .SuppressBlankLines = True
      With .DataSource
        .FirstRecord = i
        .LastRecord = i
        .ActiveRecord = i
        If Trim(.DataFields("Date")) = "" Then Exit For
        StrName = Format(.DataFields("Date"), "YYYY-MM-DD")
      End With
      .Execute Pause:=False
      If Err.Number = 5631 Then
        Err.Clear
        GoTo NextRecord
      End If
    End With
    With ActiveDocument
      .SaveAs FileName:=StrFolder & StrName & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
      ' and/or:
      .SaveAs FileName:=StrFolder & StrName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
      .Close SaveChanges:=False
    End With
NextRecord:
  Next i
End With
Application.ScreenUpdating = True
End Sub

Note 1: The above code defaults to saving the output to the mailmerge main document's folder. You can change the destination folder by editing:

StrFolder = .Path & Application.PathSeparator

Note 2: 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. The potential disadvantage of intercepting the 'Edit Individual Documents' process this way is that you no longer get to choose which records to merge at that stage. However, you can still achieve the same outcome - and with greater control - via the 'Edit Recipient List' tools.

How to rename a word document according to a mail merge attribute in VBA?

1 Answers