Word 2007/2010 Mail Merge to save to individual PDF files

[kryptonian]

I tried using gmayor's Individual Merge Letters solution but my file only closes without doing anything so I am posting here as a last recourse...

Here's what I want to do --

I'm using Word 2007/2010 mail merge and I want to create individual PDF files. Currently, we do it the long way, saving it one PDF at a time using Save As. This works if it only a handful of letters but it becomes a pain when we're processing hundreds! Data source is an Excel file.

Any help would be greatly appreciated!

 

[TBromley]

Yes, it was. Thank you for following up and for supplying the code to get me started.

 

[diederikslot]

Awesome help Macropot, i've been looking for this quite some time...

 

[ConnDublin]

First: Many thanks to Cindy for the massive start her code provided.

Like the contributor ashleys we wanted to run this using Excel ranges as a source, where not all records were included in the dataset. We were getting problems because of the stepping through the records using the fixed pointer i.

We made the following changes to Cindy's code on the first page of this thread, which now allows us to have selected records in the Excel dataset without any issues in the output:
Before the For i loop starts, added:

ActiveDocument.MailMerge.DataSource.ActiveRecord = wdFirstDataSourceRecord

This moves the pointer to the first record.
Changed the following lines

.FirstRecord = i
.LastRecord = i

To

 .FirstRecord = .ActiveRecord
.LastRecord = .ActiveRecord

First time through it reads the first record selected before the loop starts. Each time around, it is moved on by the next change.

We moved the

.ActiveRecord = i

to just before the Next i and it now moves to the next available record rather than record(i), which might not be selected for inclusion. It now reads:

 ActiveDocument.MailMerge.DataSource.ActiveRecord = wdNextDataSourceRecord

For our full code (adapted from Cindy's):

Sub merge1record_at_a_time() '
' merge1record_at_a_time Macro
' Adapted from:
'http://www.mrexcel.com/forum/general-excel-discussion-other-questions/713478-word-2007-2010-mail-merge-save-individual-pdf-files.html
    Dim fd As FileDialog
    'Create a FileDialog object as a Folder Picker dialog box.
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    With fd
        'Use the Show method to display the Folder Picker dialog box and return the user's action.
        'The user pressed the button.
        If .Show = -1 Then
                For Each vrtSelectedItem In .SelectedItems
                    'vrtSelectedItem is aString that contains the path of each selected item.
                    'You can use any file I/O functions that you want to work with this path.
                    'This example displays the path in a message box.
                    selectedpath = vrtSelectedItem
                Next vrtSelectedItem
            Else
            MsgBox ("No Directory Selected.  Exiting")
            Exit Sub
        End If
    End With
    'Set the object variable to Nothing.
    Set fd = Nothing

Application.ScreenUpdating = False
mainDoc = ActiveDocument.Name
    ChangeFileOpenDirectory selectedpath
    ActiveDocument.MailMerge.DataSource.ActiveRecord = wdFirstDataSourceRecord
    For i = 1 To ActiveDocument.MailMerge.DataSource.RecordCount
         With ActiveDocument.MailMerge
            .Destination = wdSendToNewDocument
            .SuppressBlankLines = True
            With .DataSource
                .FirstRecord = .ActiveRecord
                .LastRecord = .ActiveRecord
                'moved the .ActiveRecord to just before the Next i
                docname = .DataFields("Site").Value & ".pdf"      ' ADDED CODE
            End With
            .Execute Pause:=False
            Application.ScreenUpdating = False
        End With
        'This next code is used if you want Word documents.
        '     ActiveDocument.SaveAs FileName:=docName, FileFormat:= _
                wdFormatXMLDocument, LockComments:=False, Password:="", AddToRecentFiles _
                :=True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts _
                :=False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
                SaveAsAOCELetter:=False
        'This next code is used if you want PDF documents.
        'set OpenAfterExport to False so the PDF files won't open after mail merge
        ActiveDocument.ExportAsFixedFormat OutputFileName:=docname, _
          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
        'Windows(mainDoc).Activate (works for us WITH pdfS when this is commented out - ConnDublin
        ActiveDocument.MailMerge.DataSource.ActiveRecord = wdNextDataSourceRecord
    Next i
        
Application.ScreenUpdating = True
End Sub

I hope this helps anyone wishing to make individual files from selected set of records held in Excel (or similar).

 

[klingen2]

I've used the code and created a macro that saves files individually which solves much of my requirements. One more that I can't seem to resolve. I need to concatenate all files for a department into one file. My input spreadsheet contains a department number and will be sorted by department number. On change in that department number I need to write the file and create a new one for the next file. Thanks for all your help.

Rick

 

[ConnDublin]

Hi!

Could you post a simplified example of the structure you are working with an I would be happy to work with a practical example as reply. (Naturally, no real data should be contained therein.

In essence, I imagine this involves just one extra loop, looking out for a change in department code (or whatever).

 

[klingen2]

Thanks for the quick reply. I'm just not experienced with VB in word to figure out the loops. Anything from Cobol to Java would be much easier. Here is s sample of the data I would expect to have.

Org_Id	First_Name	Last_Name	Amount
10070642	Test	Body	$5,000.00
10070642	Second	Test	$5,100.00
10080290	First	NewUnit	$3,000.00
10080290	Second	NewUnit	$3,500.00
10080290	Third	NewUnit	$3,100.00

I would want to create two pdfs with this input file. One named including 10070642 with two merged document pages and one named including 1008029 with three merged document pages. I'm sure it's not hard and I'm surprised there are not posts for doing something like this I can't find.

Rick

 

[Macropod]

You may want to try one of the Many-to-One Mail Merge add-in by Doug Robbins at http://bit.ly/1hduSCB

In addition to a 'Many to One' merge, the addin handles:
• Merge with Charts
• Duplex Merge
• Merge with FormFields
• Merge with Attachments
• Merge to Individual Documents
• Merge, Print and Staple

 

[ConnDublin]

Dear Rick,

Just gone 22:45hrs Dublin (Ireland) time so forgive me - will get to this Wed.

Cheers.

 

[mcway]

Hey All,

Great thread and super helpful. I signed up to try and figure out this one problem I'm having. I'm using the pdf version of the code and while it seems to work fine, I am never getting more than a few iterations into the merge before I get a runtime error. Run-time '-2147467259 (80004005)': The file is in use by another application or user.

I've tried several iterations of the code mentioned on this thread. My latest version below. I first thought the issue was with Adobe pdf, but when I keep the function on to see the save changes prompt, it is when closing the word doc that the error is triggered and the macro stops. I don't know enough about Word's inner workings to know what causes the error, but it doesn't seem to be consistent and yesterday when I first tried all this, it seem to run through without any issues (though I couldn't open the pdf's when I switched the code which has been talked about on the thread).

The word version of the macro seems to work, and I don't know enough about the code itself to know what I might tweak in the pdf version to fix it or if that's even the issue.

Really appreciate any ideas you all might have.

Sub merge1record_at_a_time() '' merge1record_at_a_time Macro
'
'
    Dim fd As FileDialog








    'Create a FileDialog object as a Folder Picker dialog box.
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    With fd








        'Use the Show method to display the Folder Picker dialog box and return the user's action.
        'The user pressed the button.
        If .Show = -1 Then
                For Each vrtSelectedItem In .SelectedItems








                'vrtSelectedItem is aString that contains the path of each selected item.
                'You can use any file I/O functions that you want to work with this path.
                'This example displays the path in a message box.
        SelectedPath = vrtSelectedItem








        Next vrtSelectedItem








        Else
        MsgBox ("No Directory Selected.  Exiting")
        Exit Sub
        End If
    End With








    'Set the object variable to Nothing.
    Set fd = Nothing








Application.ScreenUpdating = False








MainDoc = ActiveDocument.Name
    ChangeFileOpenDirectory SelectedPath
    For i = 1 To ActiveDocument.MailMerge.DataSource.RecordCount
        With ActiveDocument.MailMerge
            .Destination = wdSendToNewDocument
            .SuppressBlankLines = True
            With .DataSource
                .FirstRecord = i
                .LastRecord = i
                .ActiveRecord = i
                docName = .DataFields("Chargeback_Letter_Name") & ".pdf"
            End With
            .Execute Pause:=False
    Application.ScreenUpdating = False
            
        End With
ActiveDocument.ExportAsFixedFormat OutputFileName:=docName, _
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:=False




    Windows(MainDoc).Activate
    Next i
Application.ScreenUpdating = True








End Sub

 

[Macropod]

I have posted code here that does work a number of times. See also 'Send Mailmerge Output to Individual Files' at:
Word Mailmerge Tips & Tricks | Windows Secrets Lounge
or:
Mailmerge Tips & Tricks