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

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 12 12311 ... LastLast
Results 1 to 10 of 115

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

  1. #1
    Board Regular
    Join Date
    Oct 2006
    Location
    Manila, Philippines
    Posts
    104
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

     
    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 slave

  2. #2
    Board Regular
    Join Date
    Oct 2006
    Location
    Manila, Philippines
    Posts
    104
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Word 2007/2010 Mail Merge to save to individual PDF files

    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


    Code:
    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
    excel slave

  3. #3
    MrExcel MVP Cindy Ellis's Avatar
    Join Date
    Jun 2006
    Location
    California
    Posts
    1,802
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Word 2007/2010 Mail Merge to save to individual PDF files

    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,

    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("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
    Cindy

    Excel 2010 on Windows 7 at work, 2003 on Vista at home.
    If you need to post part of your worksheet, try one of these: Excel jeanie, MrExcel HTML Maker or
    Borders-Copy-Paste

  4. #4
    Board Regular
    Join Date
    Oct 2006
    Location
    Manila, Philippines
    Posts
    104
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Word 2007/2010 Mail Merge to save to individual PDF files

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

  5. #5
    Board Regular
    Join Date
    Oct 2006
    Location
    Manila, Philippines
    Posts
    104
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Word 2007/2010 Mail Merge to save to individual PDF files

    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:
    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:
    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
    Last edited by kryptonian; Jul 15th, 2013 at 02:41 AM.
    excel slave

  6. #6
    MrExcel MVP Cindy Ellis's Avatar
    Join Date
    Jun 2006
    Location
    California
    Posts
    1,802
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Word 2007/2010 Mail Merge to save to individual PDF files

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

    Excel 2010 on Windows 7 at work, 2003 on Vista at home.
    If you need to post part of your worksheet, try one of these: Excel jeanie, MrExcel HTML Maker or
    Borders-Copy-Paste

  7. #7
    New Member
    Join Date
    Nov 2013
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Word 2007/2010 Mail Merge to save to individual PDF files

    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

  8. #8
    MrExcel MVP Cindy Ellis's Avatar
    Join Date
    Jun 2006
    Location
    California
    Posts
    1,802
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Word 2007/2010 Mail Merge to save to individual PDF files

    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,
    Cindy

    Excel 2010 on Windows 7 at work, 2003 on Vista at home.
    If you need to post part of your worksheet, try one of these: Excel jeanie, MrExcel HTML Maker or
    Borders-Copy-Paste

  9. #9
    New Member
    Join Date
    Nov 2013
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Word 2007/2010 Mail Merge to save to individual PDF files

    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

  10. #10
    New Member
    Join Date
    Nov 2013
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Word 2007/2010 Mail Merge to save to individual PDF files

      
    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

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com