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

kryptonian

Board Regular
Joined
Oct 6, 2006
Messages
104
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!
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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:
Code:
ActiveDocument.MailMerge.DataSource.ActiveRecord = wdFirstDataSourceRecord
This moves the pointer to the first record.
Changed the following lines
Code:
.FirstRecord = i
.LastRecord = i
To
Code:
 .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
Code:
.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:
Code:
 ActiveDocument.MailMerge.DataSource.ActiveRecord = wdNextDataSourceRecord
For our full code (adapted from Cindy's):
Code:
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).
 
Upvote 0
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
 
Upvote 0
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).
 
Upvote 0
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.

[TABLE="width: 256"]
[TR]
[TD="width: 64"]Org_Id[/TD]
[TD="width: 64"]First_Name[/TD]
[TD="width: 64"]Last_Name[/TD]
[TD="width: 64"]Amount[/TD]
[/TR]
[TR]
[TD="align: right"]10070642[/TD]
[TD]Test[/TD]
[TD]Body[/TD]
[TD="class: xl63, align: right"]$5,000.00[/TD]
[/TR]
[TR]
[TD="align: right"]10070642[/TD]
[TD]Second[/TD]
[TD]Test[/TD]
[TD="class: xl63, align: right"]$5,100.00[/TD]
[/TR]
[TR]
[TD="align: right"]10080290[/TD]
[TD]First [/TD]
[TD]NewUnit[/TD]
[TD="class: xl63, align: right"]$3,000.00[/TD]
[/TR]
[TR]
[TD="align: right"]10080290[/TD]
[TD]Second[/TD]
[TD]NewUnit[/TD]
[TD="class: xl63, align: right"]$3,500.00[/TD]
[/TR]
[TR]
[TD="align: right"]10080290[/TD]
[TD]Third[/TD]
[TD]NewUnit[/TD]
[TD="class: xl63, align: right"]$3,100.00[/TD]
[/TR]
[/TABLE]

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
 
Upvote 0
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
 
Upvote 0
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.

Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,401
Messages
6,171,896
Members
452,431
Latest member
TiffanyMcllwain

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top