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!
 
Thanks for the feedback and for replying with a working solution. I think others will benefit from your solution. :)

Hi Cindy. I am trying to use your code with Kryptonian's modification. The issue I seem to be encountering is Word trying to save out a new copy of the word document after saving the PDF. Any ideas? I can post the code I have inserted if that would be useful.

Thanks
Mark
 
Upvote 0

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
I suspect the problem is with this line:
Documents.Open oDoc.FullName
It appears to be trying to re-open the already-open document. Try commenting-out the line and see if you get the desired results.
 
Upvote 0
I suspect the problem is with this line:
Documents.Open oDoc.FullName
It appears to be trying to re-open the already-open document. Try commenting-out the line and see if you get the desired results.


Thanks Paul!! I was actually using the other code, but you got me pointing in the right direction. I actually needed to make two tweaks: remove the line Windows(MainDoc).Activate AND change the line ActiveWIndow.Close to include
SaveChanges:=False


It's working great now! Will be so much faster than their old way of exporting the entire document to PDF, breaking it into individual docs, and then renaming each of those individual docs.
 
Upvote 0
This is a great thread. Thanks. I've run and saved individual .pdfs file types based on my merge data and using Cindy's code. The only problem now is that I cannot open my files. I receive the message that the file has been damaged. I've modified the docName and changed the extension to .pdf

Any ideas?
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("ID") & ".pdf"     ' ADDED CODE
            End With
            .Execute Pause:=False
    Application.ScreenUpdating = False
            
        End With
    ActiveDocument.SaveAs FileName:=docName, FileFormat:= _
        wdFormatXMLDocument, LockComments:=False, Password:="", AddToRecentFiles _
        :=True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts _
        :=False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
        SaveAsAOCELetter:=False
    ActiveWindow.Close


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




End Sub
 
Last edited by a moderator:
Upvote 0
Hi JMcCauley, and Welcome to Mr.Excel!
As shown in the modification from Kryptonian, for this to save as a pdf, you also need to set the appropriate parameters to create the pdf, not just save it as a word document with a pdf extension.
Try changing this section
Code:
    ActiveDocument.SaveAs FileName:=docname, FileFormat:= _        wdFormatXMLDocument, LockComments:=False, Password:="", AddToRecentFiles _
        :=True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts _
        :=False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
        SaveAsAOCELetter:=False
    ActiveWindow.Close

to the following:
Code:
ActiveDocument.ExportAsFixedFormat OutputFileName:=docName, _        ExportFormat:=wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _ 'set OpenAfterExport to False so the PDF files won't open after mail merge
        wdExportOptimizeForPrint, Range:=wdExportAllDocument, From:=1, To:=1, _
        Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _
        CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _ 
        BitmapMissingFonts:=True, UseISO19005_1:=False
Hope that helps,
 
Last edited:
Upvote 0
That worked! Thanks Cindy!

I must be doing something small wrong, but now I have to manually indicate that I don't want to save the word version. I'm a rookie at Macros, but I thought I knew how to fix that, but then I went down a dead end, so I went back and just declined to save each one.... This is very interesting stuff though! Looking forward to picking up some more pointers. Thank you so much for your help!
 
Upvote 0
After the 'ActiveDocument.ExportAsFixedFormat' code, insert a new line with 'ActiveDocument.Saved = True'. That will stop the save prompts. Alternatively, to close the document via code, use 'ActiveDocument.Close SaveChanges:=False' instead of 'ActiveWindow.Close'.
 
Upvote 0
Encrypted PDF

Is it possible to create using same code a password protected PDF with Owner Password and user password.
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

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