Using Merge Fields in a Split Document Macro

Question

Hello,

I use an Access database to merge client information into letters in Word. I can select multiple records in access and create all of the merge letters at once - however, they of course merge into one document with a new letter on each page. I have a VB Macro to split the document into separate letter files. My question is whether it is possible to use one of the merge fields to populate the document name as the Split Macro loops through each page. For instance, can I use the "Applicant Name" merge field to populate as the document name for each new split & save? If this is doable, help would be much appreciated.

Code:

Sub SplitLetters()

   ' Used to set criteria for moving through the document by page.

   Application.Browser.Target = wdBrowsePage

   For i = 1 To ActiveDocument.BuiltInDocumentProperties("Number of Pages")

      'Select and copy the text to the clipboard.

      ActiveDocument.Bookmarks("\page").Range.Copy

      ' Open new document to paste the content of the clipboard into.

      Documents.Add

      Selection.Paste

' Removes the break that is copied at the end of the page, if any.

      Selection.TypeBackspace

      ChangeFileOpenDirectory "C:"

      DocNum = DocNum + 1

      ActiveDocument.SaveAs FileName:=DocNum & ".doc"

      ActiveDocument.Close

      ' Move the selection to the next page in the document.

      Application.Browser.Next

   Next i

   ActiveDocument.Close savechanges:=wdDoNotSaveChanges

End Sub

-DG

Answer 1

You can't reference mergefields post-merge, because they no longer exist. You can, however, reference them during the merge to generate individual files, or their location post-merge for the same purpose. For macros to implement both approaches, see:

• Send Mailmerge Output to Individual Files; and

• Split Merged Output to Separate Documents,

in the Mailmerge Tips and Tricks threads at:

http://www.msofficeforums.com/mail-merge/21803-mailmerge-tips-tricks.html

or:

http://windowssecrets.com/forums/showthread.php/163017-Word-Mailmerge-Tips-amp-Tricks

Answer 2

Hi Doug, thank you for this, but as I'm using a work computer I can't download add-ins or applications without IT's permission. I might try this at home though - thanks very much.

Answer 3

You could use the Merge to Individual Docs facility on my MergeTools – 20150422.dotm Add-in that you can download from the following page of my One Drive:

http://bit.ly/1hduSCB 

The MergeTools – 20150422.dotm file needs to be saved in the Word Startup folder.  In Windows Vista and Windows 7, 8 or 8.1, and 10, the default location for that folder is

C:\Users[User Name]\AppData\Roaming\Microsoft\Word\STARTUP

If you do not see the AppData folder: -

In Windows 7, - In Windows Explorer, click on the Organize drop down and then on Folder and search options and in the Folder Options dialog, go to the View tab and select the item "Show hidden files, folders, and drives".  While there, it is a good idea to uncheck the box for "Hide extensions for known file types".

In Windows 8,  8.1 or 10, in the File Explorer, click on Options on the View tab of the ribbon and then on the View tab in the dialog that appears and select the item "Show hidden files, folders, and drives".  While there, it is a good idea to uncheck the box for "Hide extensions for known file types".

When that has been done and Word is started\re-started, the tab shown below will be added to the Ribbon:

The requirements for using the system are:

1. The mail merge main document must be of the Letter type, though that does not mean that the output cannot be sent as an e-mail message.
2. For the Many To One, Merge with Attachments and Merge to Individual Docs utilities, the data source may be either a table or query in an Access database, or in the form of an Excel worksheet and that worksheet must be the first sheet in the Excel workbook. If the data is on some other sheet, you can easily move that sheet so that it is the first sheet in the workbook by clicking on the sheet tab and dragging it to the left.  For the Chart Merge utility, download the Mail Merging with Charts document that is also on that page of my OneDrive for additional requirements of the data source for use with that utility
3. For a data source in the form of an Excel worksheet, the field names must be in the first row of the worksheet and there must be a field name in all of the cells in that row that are within the range of columns that contain the data.
4. For both types of data source, the field names must contain only alphanumeric characters (No @,#,$,%,&,(,), etc) and the field names must not start with a numeric character (0-9). The number of characters in the field names, including spaces, must not be more than 40.
5. For a data source in the form of an Excel worksheet, the data must start in the second row of the worksheet and there should be no empty rows within the range of data that is to be processed.

NOTE: The MergeTools applications cannot handle “Compound” MergeFields such as the «AddressBlock» or «GreetingLine».  Instead of using those fields, you will need to insert the individual merge fields.

You may also want to download:

1. the Merging with Attachments document that is also on that page which explains how the system is used.  It is not actually necessary to have separate attachments as the facility can be used to send just the documents created by the merge itself as attachments, either as the body of the message itself or in the form of Word files or .pdf files.
2. the Mail Merging with Charts document that is also on that page.  That document explains how you must set up the Excel Data Source and the Mail Merge Main document to be able to execute a merge with a Chart that is unique to each record in the data source.
3. the Using the Many to One Facility document that describes how to use that facility.

Answer 4

The merge fields don't exist as fields in the output of the merge -- they're replaced by the value of the source field for the current record as plain text. There isn't anything field-related there for your macro to refer to.

If your merge document (where the merge fields are) is such that the applicant name is always in the same paragraph in each page, for instance in the third paragraph, then the macro can grab the text from that paragraph, delete the paragraph mark at the end of it, and use the text as the file name. In code,

      strApplicName = Replace(ActiveDocument.Paragraphs(3).Range.Text, vbCr, "")

      ActiveDocument.SaveAs FileName:=strApplicName & ".doc",  _

            FileFormat:=wdFormatDocument97

To use this in your macro, change the number 3 in Paragraphs(3) to the number of the paragraph that contains the "Applicant Name" merge field.

Note that this won't work if any merge field that precedes "Applicant Name" could possibly insert more than one paragraph into the output.