VBA code for dividing one large mail merge into separate PDFs

Question

VBA code for dividing one large mail merge into separate PDFs

Anonymous

Hello,

I have been trying different codes and suggestions I have found online but still having a problem. I have a mail merge that I run and would like to then run a macro that will divide the applications up into individual PDF files. So far I've been able to get the mail merge to save as 1 single PDF instead of separate PDF documents. Each document should contain 2 pages. This is the code I have thus far:

Sub Save_Merged_As_PDFs2()

'Makes the code run faster and reduces screen flicker a bit.

Application.ScreenUpdating = False

' Select a folder, change the default file save location below so it's not just C:\

Dim strFolder As String

Set fd = Application.FileDialog(msoFileDialogFolderPicker)

With fd

.Title = "Select the folder into which the documents will be saved."

If .Show = -1 Then

strFolder = .SelectedItems(1) & ""

Else

MsgBox "The documents will be saved in the default document file location."

strFolder = "S:\Readmit\Continuation Appeal"

End If

End With

ChangeFileOpenDirectory strFolder

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 + .RecordCount

' Remember the wanted documentname

DokName = .DataFields("Username").Value ' ADDED CODE

End With

' Merge the active record

.Execute Pause:=False

End With

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

Application.Browser.Target = wdBrowseSection

'A mailmerge document ends with a section break next page.

'Subtracting one from the section count stop error message.

For i = 1 To ((ActiveDocument.Sections.Count) - 1)

'Select and copy the section text to the clipboard

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

'Create a new document to paste text from clipboard.

Documents.Add

Selection.PasteAndFormat (wdFormatOriginalFormatting)

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

Selection.MoveUp Unit:=wdLine, Count:=1, Extend:=wdExtend

Selection.Delete Unit:=wdCharacter, Count:=1

' Save then resulting document. NOTICE MODIFIED filename

ActiveDocument.ExportAsFixedFormat OutputFileName:="S:\Readmit\Continuation Appeal" + DokName + ".pdf", _

ExportFormat:=wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _

wdExportOptimizeForPrint, Range:=wdExportAllDocument, From:=1, To:=1, _

Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _

CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _

BitmapMissingFonts:=True, UseISO19005_1:=False

ActiveDocument.Close savechanges:=wdDoNotSaveChanges

'Move the selection to the next section in the document

Application.Browser.Next

Next i

ActiveDocument.Close savechanges:=wdDoNotSaveChanges

End Sub

Any help is greatly appreciated. Thanks in advance!

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Answer 1

Do you have the UserName mergefield in a location on the document where it can be located with certainty.

If so you could just execute the merge to a new document and then run a macro over that document that would create a new document with the content being that of each Section in the document produced by the mail merge and use code to access the UserName data to be used for the filename of the document.

Answer 2

Thanks so much Doug for the suggestion of the add-on. For this particular document its best I stick to VBA coding for the macro. Its for my job and I won't be the only person needing to run the process so, they in turn would each have to download the add-on which will require administrator permissions.

Answer 3

Use the Merge to Individual Documents facility on my Merge Tools add-in.

You can download the MergeTools – 20150422.dotm Add-in that I created from the following page of my One Drive: http://bit.ly/1hduSCB

The MergeTools – 20150309.dotm file needs to be saved in the Word Startup folder. In Windows Vista and Windows 7, 8 or 8.1, 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 of "Hide extensions for known file types".

In Windows 8 and 8.1, 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 of "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:

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.
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
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.
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.
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:

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.
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.
the Using the Many to One Facility document that describes how to use that facility.

Share via

VBA code for dividing one large mail merge into separate PDFs

3 answers