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!

 

[mcway]

I've used Macropad and Cindy's codes, which work fine with the code for generating .docx files, but when I try the different coding for .pdf, I get the run-time error.

When the error occurs, the debugger highlights the section of the code you've said to paste over the SaveAs2 line for pdf files. I have Adobe Acrobat X Pro on my computer and just playing around with the code, I removed the ".pdf" from the OutputFileName command. It created non-pdf files, but it did run through and create the whole list, which makes me think the problem is with Adobe PDF.

Any ideas?
Thanks

 

[Macropod]

The code I have posted here (e.g. http://www.mrexcel.com/forum/genera...-save-individual-pdf-files-3.html#post3694494, http://www.mrexcel.com/forum/genera...-save-individual-pdf-files-4.html#post3710126) and at the links in my last post works. The only thing you might need to change in the posts here is from SaveAs2 to SaveAs, for Word 2007.

 

[mcway]

Thanks Paul,

I'm sure the problem I'm having is not your code. I am running it in the vba and if I use it to save individual word files, I have no problem. But this pesky runtime error that says the application is in use is persisting when I try it with pdf and I'm not sure how to fix it.

Sorry if I'm being a little thick here.

 

[Macropod]

But the code you're using is also not based on code that I have posted...

 

[mcway]

I had tried your codes that were on this thread, then went back to Cindy's. I've since gone back and tried the codes in the links you attached. This was the last code I tried, doing nothing more than changing your data source "Last_Name" to mine "Chargeback_Letter_Name". But in every instance I get the same error and the debugger refers to the same piece of the code dealing with saving the pdf.

Sub Merge_To_Individual_Files()
'Merges one record at a time to the folder containing the mailmerge main document.
' Sourced from: http://windowssecrets.com/forums/showthread.php/163017-Word-Mailmerge-Tips-amp-Tricks
Application.ScreenUpdating = False
Dim strFolder As String, StrName As String, MainDoc As Document, i As Long, j As Long
Set MainDoc = ActiveDocument
With MainDoc
  strFolder = .Path & Application.PathSeparator
  For i = 1 To .MailMerge.DataSource.RecordCount
    With .MailMerge
      .Destination = wdSendToNewDocument
      .SuppressBlankLines = True
      With .DataSource
        .FirstRecord = i
        .LastRecord = i
        .ActiveRecord = i
        If Trim(.DataFields("Chargeback_Letter_Name")) = "" Then Exit For
        StrName = .DataFields("Chargeback_Letter_Name")
      End With
      .Execute Pause:=False
    End With
    For j = 1 To 255
      Select Case j
        Case 1 To 31, 33, 34, 37, 42, 44, 46, 47, 58 - 63, 91 - 93, 96, 124, 147, 148
        StrName = Replace(StrName, Chr(j), "")
      End Select
    Next
    StrName = Trim(StrName)
    With ActiveDocument
      '.SaveAs FileName:=StrPath & StrName & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
      ' and/or:
.SaveAs FileName:=StrPath & StrName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
.Close SaveChanges:=False
    End With
  Next i
End With
Application.ScreenUpdating = True
End Sub

 

[Macropod]

In that case, it seems you have a duplicate 'Chargeback_Letter_Name' record and, at the time Word's trying to save the duplicate, it hasn't finished closing the original. Even if timing were not an issue, you risk ending up with the duplicate overwriting the original. You apparently need a better way to differentiate your filenames.

 

[soccerjon1013]

This is what I have tried to use, and have got it to work somewhat in word 2003. I have some basic questions and a problem though.

1. Why does it ask for a location when it starts? It seems to do the same thing no matter what I do
2. Is it possible to have it save it to a specific location based on text and fields (ie: c:\errors\'name'\CC Errors 'date') with 'name' and 'date' being data pulled from the mail merge?
3. When I run this, I get an Run-time error '5853': Invalid parameter
Debug highlights this line: ActiveDocument.MailMerge.DataSource.ActiveRecord = wdNextRecord

I am very new at all this, can you please help me?

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




    Application.PrintOut FileName:="", Range:=wdPrintAllDocument, Item:= _
        wdPrintDocumentContent, Copies:=1, Pages:="", PageType:=wdPrintAllPages, _
        ManualDuplexPrint:=False, Collate:=True, Background:=True, PrintToFile:= _
        False, PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _
        PrintZoomPaperHeight:=0
    
    ActiveDocument.MailMerge.DataSource.ActiveRecord = wdNextRecord


    Windows(MainDoc).Activate
    Next i


Application.ScreenUpdating = True




End Sub

 

[Macropod]

1. Why does it ask for a location when it starts? It seems to do the same thing no matter what I do

Because that's what you've coded it to do, via:
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
With fd
...
End With

2. Is it possible to have it save it to a specific location based on text and fields (ie: c:\errors\'name'\CC Errors 'date') with 'name' and 'date' being data pulled from the mail merge?

Yes. You could delete all the code relating to the File Dialog and instead use code like that shown in post #55. Indeed, if its OK to have .doc files instead of PDFs, you could almost use that code as is, with nothing more than a change to the field name used to populate the StrName and a change to the SaveAs code to save in the .doc format. As it is, your code makes no use of the data collected via the FileDialogFolderPicker and its not apparent you're doing anything with a PDF printer driver that might be able to make use of it.

 

[soccerjon1013]

I took that part out, and it no longer asked to pull the file location. What was the purpose of it being there or not, it seemed to yield the same results either way?

I am unable to save it as a .doc format, it has to be as a pdf, since my default printer is the cute pdf, I'm assuming it always bring that up.

When I run this macro, it now brings up a save function for each record to print as pdf, however it still comes up with the run-time error, is there a way to fix it?

and please forgive me ignorance, but I really know very little about all this. What code would I need, and where would I put it to save it in the location: c:\errors\'name'\CC Errors 'date') with 'name' and 'date' being data pulled from the mail merge?

Your assistance is GREATLY appreciated!

 

[Macropod]

AFAIK you cannot use VBA to pass a filename to the normal version of CutePDF. As a workaround, the following code copies the proposed filename from the mailmerge to the windows clipboard, from where you can paste it into the CutePDF dialog box.

Note: This code need a reference to the MS Forms 2.0 Object Library, which you set in the VBE via Tools|References.

Sub Merge_Print_To_Individual_Files()
Application.ScreenUpdating = False
'Note: This code need a reference to the MS Forms 2.0 Object Library
Dim strFolder As String, StrName As String, i As Long, MyData As DataObject, MainDoc As Document
Set MyData = New DataObject: Set MainDoc = ActiveDocument
With MainDoc
  strFolder = .Path & Application.PathSeparator
  For i = 1 To .MailMerge.DataSource.RecordCount
    With .MailMerge
      .Destination = wdSendToNewDocument
      .SuppressBlankLines = True
      With .DataSource
        .FirstRecord = i
        .LastRecord = i
        .ActiveRecord = i
        If Trim(.DataFields("Name")) = "" Then Exit For
        StrName = strFolder & Trim(.DataFields("Name")) & "-" & Format(.DataFields("Date"), "YYYY_MM_DD")
        MyData.SetText StrName
        MyData.PutInClipboard
      End With
      .Execute Pause:=False
    End With
  Next i
End With
Set MyData = Nothing: Set MainDoc = Nothing
Application.ScreenUpdating = True
End Sub