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!

 

[Mistolo]

Hi Cindy,

I'm looking to do something similar to this however i'm a super newbie with macros and need extreme help! Hopefully i'm not too many years late in requesting this?

 

[Macropod]

Too late in requesting what? The thread contains a detailed discussion and whatever your requirements are have most likely already been discussed.

 

[Mistolo]

I've tried going through the Form but honestly I don't entirely understand everything. I'm a super NEWBIE. From what i've read here what i'm looking for can be done but I don't even understand how it can be implemented or understanding reading the codes.

Here's what I need:
I'm looking for a way to run a mail merge, using an excel document that contains data and the word document from the mail merge. I've been able to run the merge but it then makes all of the files show in one singular document. I'd like to save the documents as individual files, and name each file with a specific name that is generated from one of the columns in the excel spread sheet. Ideally if I can gave all of the files that are named with that specific name be located in their own folder that would be great as well.

I hope I was able to be clear enough and this can lead to some help? I would really need babysteps through this or even at least highlighted steps in the code showing where to plug in he file name/destination or how to save the file with a specific name which is generated from a column in excel.

Thank you so much in advance

 

[Macropod]

Without knowing exactly what you've tried, it's impossible to provide advice on that. Nevertheless, in post #81 I supplied links to generic code designed to do exactly what you say you want to achieve.

 

[Mistolo]

Thank you so much! As you can tell i'm way too much of a NEWBIE that I even didn't fully explain what I've/tried. I'll go back to the very beginning and refer to post #81, try with those generic codes and report back on how it goes. Thank you again!

 

[JPJensen]

Thanks for the fine answers
I need to be able to make mailmerge to pdf conditional on cell a2 <> ""
I got this code working
Now I would like it to merge to pdf only when they line in excel (cell A2) Do While Ark1.Cells (x, 1) <> "" or marked with x
The rest of the lines should not be merged

Does it make sense

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
'xxxxxxxx
x = 2 ' added code


Do While Ark1.Cells(x, 1) <> "" ' added code
'xxxxxxxx
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("no").Value & "_" & .DataFields("Adress").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
'xxxxxx
Loop 'added code
'xxxxxx


Application.ScreenUpdating = True
End Sub

 

[Macropod]

You don't need any code for that; all you need do is define the appropriate mailmerge filters in the mailmerge main document. The links in post 81 (https://www.mrexcel.com/forum/gener...vidual-pdf-files-post4551045.html#post4551045) point to all the code all you need - unless you want to force the user to choose the output folder each time.

 

[JPJensen]

Thank you
I missed that part

 

[mac232007]

Hi Guys!

First of all, thank you so much Cindy for your "merge to pdf" code, it saved me many hours!

However I have an issue I can't seem to wrap my head around. I am trying to mail merge records but to have the data updated between each record prior to merging.

Let me explain:

1. I used one of my merge field (DataFields("HM_VR")) from my word doc and copied it as OLE link into a separate excel sheet.
2. All data in the excel sheet is updated and dependent on the reference shown through the OLE link.
3. I then did a pasted link of the excel table from the excel sheet into the original word document.
4. Thus, when I manually preview each record, the HM_VR data changes and the excel updates and then shows back the correct data in word. (Sometimes it needs to be jump started and have to manually update the link).

I have two macros (see under), which both work when run separately. I am trying to integrate the first one into the second one so that the ideal macro (call it macro 3) merges the first record (macro 2), then updates the links (macro 1), then merge the second record (macro 2), and so on. I've done everything I can with my limited knowledge and am completely stuck

Macro 1: updates all the links without fault.

Sub Update()

With Options
        .UpdateFieldsAtPrint = True
        .UpdateLinksAtPrint = True
    End With
    ActiveDocument.Fields.Update
End Sub

Macro 2: Simply merges each record into separate pdfs (thanks Cindy).

Sub Merge_to_pdf() '
' 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 = "DOC NAME -" & .DataFields("HM_VR").Value & ".pdf"      ' ADDED CODE
            End With
            .Execute Pause:=False
    Application.ScreenUpdating = True
        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
    ActiveWindow.Close SaveChanges:=False
    

    Next i
Application.ScreenUpdating = True

End Sub

Thanks for the help!!
Luke

 

[Saved]

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

I've used this code for years now and it works amazing. I used it to generate PDFs and upload them onto a server where the users can go and download their file. It so happens that I now need to take this task into my website. Basically, there will be a UI on the website with two fields where a user enters his information; if both match with his details on the database, his respective PDF has to be generated and given to him as a download. I did my best trying to find a tool online, but couldn't really locate anything. Would someone be able to help with this? I am willing to post it on the consulting page too if required.