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!

 

[comebakqueen]

Hi Guys!

Firstly, wanted to say a HUGE thank you for this code; saved me SO much time trying to figure it out!


Just had a question regarding the Dialog prompter. My files will be saved in the same path every time; is there a way to tell it to automatically go there every time I run the macro so that I don’t have to go through the whole selection process again… I know this might seem lazy but this is a weekly report that I have to generate and the extra few minutes will save me so much time!


Thanking you in advance!

 

[Macropod]

Try:

Sub merge1record_at_a_time()
Application.ScreenUpdating = False
Dim StrPath As String, StrName As String, MainDoc As Document
StrPath = "C:\File path\"
Application.ScreenUpdating = False
Set MainDoc = ActiveDocument
With MainDoc
  For i = 1 To .MailMerge.DataSource.RecordCount
    With .MailMerge
      .Destination = wdSendToNewDocument
      .SuppressBlankLines = True
      With .DataSource
        .FirstRecord = i
        .LastRecord = i
        .ActiveRecord = i
        StrName = .DataFields("ID")
      End With
      .Execute Pause:=False
    End With
    With ActiveDocument
      .SaveAs2 FileName:=StrPath & StrName & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
      .Close SaveChanges:=False
    End With
  Next i
End With
Application.ScreenUpdating = True
End Sub

Naturally, you'll need to use your own path for the StrPath variable and, if you're saving the outputs as PDF files, change the '.SaveAs2' line to:

      .SaveAs2 FileName:=StrPath & StrName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False

 

[comebakqueen]

Heya Macropod!

Thank you for the reply! I’m still struggling to get it to work (Give me Excel VBA any day)!

At this stage the code is:

Application.ScreenUpdating = False
Dim StrPath As String, StrName As String, MainDoc As Document
StrPath = "R:\Operations\Australia\Reports\DIFOT\Supplier Reports"
Application.ScreenUpdating = False
Set MainDoc = ActiveDocument
With MainDoc
For i = 1 To .MailMerge.DataSource.RecordCount
With .MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = i
.LastRecord = i
.ActiveRecord = i
StrName = .DataFields("Supplier")
End With
.Execute Pause:=False
End With
With ActiveDocument
.ExportAsFixedFormat OutputFileName:=StrPath & StrName & ".pdf", ExportFormat:=wdExportFormatPDF, _
OpenAfterExport:=False, OptimizeFor:=wdExportOptimizeForPrint, Range:=wdExportAllDocument, _
Item:=wdExportDocumentContent, IncludeDocProps:=True, CreateBookmarks:=wdExportCreateNoBookmarks, _
KeepIRM:=True, DocStructureTags:=True, BitmapMissingFonts:=True, UseISO19005_1:=False
End With
ActiveDocument.Close SaveChanges:=False
Next i
End With
Application.ScreenUpdating = True

End Sub

But it’s saving into the filepath "R:\Operations\Australia\Reports\DIFOT" and is adding "Supplier Reports" before the field name. What am I doing wrong?

(ALSO: How do get the coding in a groovy box?)

 

[Macropod]

You should have a '\' after 'Supplier Reports'.
As for the code tags, you insert them via the menus on the 'Go Advanced' window.

 

[Soramanego]

This is a version that function for .pdf

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 = "Lettera_UMTS_01_2014_" & .DataFields("PK_Assegnatario").Value & ".pdf"      ' ADDED CODE
            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
    ActiveWindow.Close SaveChanges:=False
    

    Next i
Application.ScreenUpdating = True

End Sub

 

[acedanger]

Hi There, I'm a complete newbie for this. I have added the below macro in to word 2010 and clicked run, but all that happens is a prompt to enter a location where the files are to be saved, no PDF's are created for me.

Can someone maybe give me the "dummy" explanation?

-Do i need to change the code in any fashion?
- Do I need to number my excel spreadsheet in any way?

Thanks a bunch.

This is a version that function for .pdf

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 = "Lettera_UMTS_01_2014_" & .DataFields("PK_Assegnatario").Value & ".pdf"      ' ADDED CODE
            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
    ActiveWindow.Close SaveChanges:=False
    

    Next i
Application.ScreenUpdating = True

End Sub

 

[Macropod]

What document are you running the macro from? It's intended to be run from your mailmerge main document, after you have attached the data-source as you'd do for any other mailmerge.

 

[acedanger]

Hi Macropod, I'm running the macro from the mailmerge document Word 2010 macro enabled document. I've gone through the step by step mail merge wizard through to step 6 of 6, then attempted to run the macro. As I said I get the option to choose a folder, but that's it. I've also tried running the macro after selecting "edit individual letters" once the merge is complete.

What document are you running the macro from? It's intended to be run from your mailmerge main document, after you have attached the data-source as you'd do for any other mailmerge.

 

[Macropod]

The code, as posted, generates PDF files prefixed with "Lettera_UMTS_01_2014_" and includes the value of a datafield in the source, named "PK_Assegnatario". Have you changed the line:
docName = "Lettera_UMTS_01_2014_" & .DataFields("PK_Assegnatario").Value & ".pdf"
to whatever you require? The macro can't produce the correct output if this is wrongly specified.

 

[acedanger]

No I haven't, can you give me a quick description of what each of those references items mean? I'm not bad with computers, but this coding stuff is totally new to me!