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]

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

THANK YOU! It works perfectly *bows to Macropod*

 

[Macropod]

You have to supply them. To get you started, try:
docName = "MergeLetter_" & i & ".pdf"
That should be enough to generate individually-numbered PDFs, prefixed with "MergeLetter_"

 

[acedanger]

Thanks for all your help, but I'm still not getting any further it seems...I've pasted my two merge documents as a public link from Dropbox, can anyone tell from my files what I'm doing wrong with the code? If possible, fix the code and link to it again?

https://dl.dropboxusercontent.com/u/18508700/Share%20Cert.docm

https://dl.dropboxusercontent.com/u/18508700/Shares Email.xlsx

You have to supply them. To get you started, try:
docName = "MergeLetter_" & i & ".pdf"
That should be enough to generate individually-numbered PDFs, prefixed with "MergeLetter_"

 

[Macropod]

There seemed to be some corruption in your dropbox document that kept locking up my system. Nevertheless, I was able to extract the certificate and add it to a new document that I could attach to your datasource.

Rather than persisting with the other code (which works OK), I've revised my earlier code, coupling it with a folder browser. Once you've fixed any corruption that may be in the document, reattached the datasource and added the macro to it, the macro should run fine. Your output files will have the client names as the filenames.

Sub Merge_to_pdf()
'merges one record at a time to the chosen output folder
Application.ScreenUpdating = False
Dim strFolder As String, StrName As String, MainDoc As Document, i As Long
strFolder = GetFolder
If strFolder = "" Then Exit Sub
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
        If Trim(.DataFields("Last_Name")) = "" Then Exit For
        StrName = .DataFields("Last_Name") & "_" & .DataFields("First_Name")
      End With
      .Execute Pause:=False
    End With
    With ActiveDocument
      .SaveAs2 FileName:=strFolder & StrName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
      .Close SaveChanges:=False
    End With
  Next i
End With
Application.ScreenUpdating = True
End Sub

Function GetFolder() As String
Dim oFolder As Object
GetFolder = ""
Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
Set oFolder = Nothing
End Function

 

[comebakqueen]

Thanks Paul!

That worked a treat but now it's giving me Run time error '5853' once all the PDF's are generated and when I go to debug it says it's because of this line:

.ActiveRecord = i

! It's been working fine since your previous assistance so was just wondering if you had any idea what I'm doing wrong (you're so knowledgable )

Thank you!
Tams =D

 

[Macropod]

I suspect it's because your worksheet has some empty, but used, rows at the bottom.

 

[comebakqueen]

That's what I thought too (and I deleted them)... I shall try again tomorrow; VBA has not been my friend today haha.

Thank you for all your help!

 

[TBromley]

Cindy, thank you for providing this code. I have a Word mail merge and it works as advertised for naming the individual pdf files. I'm having a problem with the mail merge and am hoping you can assist. The mail merge has an ASK field with the \o switch. When I run the mail merge directly from Word, the prompt asks only once. When I run the mail merge using your code, the prompt asks for each document. Does vba not recognize the /o switch? Is there a work around? Thank you.

 

[Macropod]

Cross-posted at: Ask prompt \o switch doesn't work in macro
For cross-posting etiquette, please read: Excelguru Help Site - A message to forum cross posters

 

[Cindy Ellis]

TBromley, was your problem resolved on the other forum? If not, let me know and I'll try to find a solution.