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!

 

[kryptonian]

Here's what I got running so far taken from the net <forgot which one since I opened a lot of 'em>. It is able to save the file as a PDF but I need to do these 2 things:

1. Splilt it to different PDF files
2. Save each PDF with a unique name, i.e., name field in the list + date

Public Sub MergePDF()
Dim pdfName As String
Dim oDoc As Document
Dim oMerged As Document
Dim oRng As Range
Dim iPages As Long
Dim iRecords As Long
' This tries to speed up the macro.
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set oDoc = GetObject("C:\Users\e.m.cepe\Documents\00_Templates\10_Mail Merge\Test 1.docm")
With oDoc.MailMerge
    .MainDocumentType = wdFormLetters
    .OpenDataSource Name:= _
                    "C:\Users\e.m.cepe\Documents\00_Templates\10_Mail Merge\List.xlsx", _
                    ConfirmConversions:=False, _
                    ReadOnly:=True, _
                    LinkToSource:=True, _
                    AddToRecentFiles:=False, _
                    PasswordDocument:="", _
                    PasswordTemplate:="", _
                    WritePasswordDocument:="", _
                    WritePasswordTemplate:="", _
                    Revert:=False, _
                    Format:=wdOpenFormatAuto, Connection:= _
                    "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=U:\excelsource.xlsx;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=37;Jet OLE", _
                    SQLStatement:="SELECT * FROM `Sheet1$`", _
                    SQLStatement1:="", _
                    SubType:=wdMergeSubTypeAccess
    .Destination = wdSendToNewDocument
    .SuppressBlankLines = True
    .Execute False
End With
Set oMerged = ActiveDocument
Set oRng = oMerged.Range
oRng.Start = oRng.End
iPages = oRng.Information(wdActiveEndPageNumber)
iRecords = ActiveDocument.Sections.Count - 1
' This prints to a PDF file and saves it to a designated folder.
pdfName = "C:\Users\e.m.cepe\Documents\00_Templates\10_Mail Merge\TestMergePDF- " & Format(Date, "mm-dd-") & Format(Time, "hhmmss") & ".pdf"
oMerged.ExportAsFixedFormat pdfName, 17 'Path?
oMerged.Close 0
Open "C:\Users\e.m.cepe\Documents\00_Templates\10_Mail Merge\LogFile.txt" For Append As #1
Write #1, "Pages - " & iPages & "  Records - " & iRecords  ' Write data."
Close #1 ' Close file.
Documents.Open oDoc.FullName
End Sub

 

[Cindy Ellis]

Hi,
I have used the following macro to save mail merge output to individual files based on the value of a field in the datasource...maybe you can modify it to do what you need, or add in the logic into your macro that merges the data one at a time. It uses a different approach than the macros I found on the web. Instead of merging then separating, it loops through the datasource, merging and saving one record at a time.
The macro allows the user to select a directory for saving the files. It depends on an existing mail merge main document that already contains merge fields and a link to the datasource.
Hope this helps,

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("ASP_Print").Value & ".docx"      ' 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

 

[kryptonian]

Thanks, Cindy! I'll give it a try when I get to work tomorrow and will get back to you.

 

[kryptonian]

Hi Cindy! With a little tweak of your code, I was able to do a mail merge to save as PDF files! Thank you very much for the assistance! Sharing the tweak I made below for reference to those who might have the same concern.

Also changed extension of docName from *.docx to *.pdf

Part of original 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

Changed to:

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

 

[Cindy Ellis]

Thanks for the feedback and for replying with a working solution. I think others will benefit from your solution.

 

[ashleys]

Hi Cindy -

I am trying to use your code, and I am a very new newbie. I managed to get it to save the first file, but then it says there is a problem with this line of code:
End With
.Execute Pause:=False

Just wondering how much I need to personalize other than the name that I want the document to have.

Thanks for any help!

Ashley

 

[Cindy Ellis]

Hello Ashleys, and welcome to Mr. Excel!
I'm not sure what the problem would be that would save the 1st file but no files after that. If you do the word merge without the macro (merging to a single file rather than a printer) do you get a file that includes all of the records merged in sequence?
Did you update the macro with the field name that holds the filename to save to?
Is there anything unique about the data?
Also, if your intention is to save to pdf, are you using the modification from kryptonian?

It's a bit more difficult for me to troubleshoot word macros than excel (just due to more experience with Excel), but I'll give it a try

Hoping to help,

 

[ashleys]

When I do a regular mail merge I get the whole large document. I did update the field name of what I want the files to save as, and it worked for my first document, which is a unique thing for each document.
Not sure what you mean about having something unique about the data, it's just letters we are sending out, with each letter being customized for that person, including an ID number (which is our unique identifier).
We are saving to word, so that's working fine.

I am using word 2007, if that would make a difference. I get a runtime error 5631. I've been here: You receive a to see if I could figure out how to fix it, but I'm not good enough at macros to really know where I should be adding this information. Any help would be greatly appreciated!

Thanks!!

Ashley

 

[ashleys]

Hi Cindy -
I figured out my problem. Because I wasn't selecting all the records from my excel file as part of the mail merge the recoord numbers weren't 1, 2, 3, 4, 5 etc. So I just edited my excel file to only include the records I want, and the macro works great. It's not perfect, but for the amount of time I am doing this, it works well for me.

Thanks again,

Ashley