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!

 

[kryptonian]

Wow! This thread is almost 4 years old and still helping folks with their mail merge to doc/pdf problems!

I'm doing a couple of projects again with mail merge and had to revert back to this thread after a couple of years and I'm happy this helped more than just me when I posted this question.

 

[kryptonian]

Let's take this up a notch...

So, I have an Excel macro to open Word and run a Mail Merge macro in Word. The problem is I am able to open the file but the Word macro is not starting. I'm sure that there is no problem with the Word macro as I can run this with no issues in Word. I think the issue lies when opening Word from Excel macro. When opening the file in Word, I get prompted with:

"Opening this document will run the following SQL command:

SELECT * FROM 'Raw$' Data fro

Data from your database will be placed in this document. Do you want to continue?"

To which I would need to click YES.

I don't get this prompt when I open Word via Excel Macro so I assume this is an automatic No selected and hence the Word macro is not running.

Any suggestions??? TIA!!!

 

[Macropod]

Automating a mailmerge from Excel requires a different approach.

The following macro automates a mailmerge from Excel. The code assumes you have a document named 'MailMergeMainDocument.docx' stored in the same folder as the Excel workbook. That document should be saved as an ordinary, macro-free, document, not as a mailmerge main document. As coded, the macro also assumes a standard query, processing all records from Sheet1. Change the sheet references, as appropriate. If you're using filtering, you'd have to add that to the macro's SQLStatement, too. Each record's output is sent to a new file in the same folder as the Excel workbook, using the 'Last_Name' & 'First_Name' fields in the data source for the filenames (change these to suit your requirements). PDF & DOCX save formats are catered for. Illegal filename characters (i.e. "*./\:?|) are replaced with underscores.

For testing purposes, you might want to comment-out the line 'wdApp.Visible = False'.

Sub RunMerge()
'Note: this code requires a reference to the Word object model
Application.ScreenUpdating = False
Dim StrMMSrc As String, StrMMDoc As String, StrMMPath As String, StrName As String
Dim i As Long, j As Long
Const StrNoChr As String = """*./\:?|"
Dim wdApp As New Word.Application, wdDoc As Word.Document
wdApp.Visible = False
wdApp.DisplayAlerts = wdAlertsNone 
StrMMSrc = ThisWorkbook.FullName
StrMMPath = ThisWorkbook.Path & "\"
StrMMDoc = StrMMPath & "MailMergeMainDocument.doc"
Set wdDoc = wdApp.Documents.Open(Filename:=StrMMDoc, AddToRecentFiles:=False, ReadOnly:=True, Visible:=False)
With wdDoc
  With .MailMerge
    .MainDocumentType = wdFormLetters
    .OpenDataSource Name:=StrMMSrc, ReadOnly:=True, AddToRecentFiles:=False, _
      LinkToSource:=False, Connection:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;" & _
      "Data Source=StrMMSrc;Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", _
      SQLStatement:="SELECT * FROM `Sheet1$`"
    For i = 1 To .DataSource.RecordCount
      .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      
      For j = 1 To Len(StrNoChr)
        StrName = Replace(StrName, Mid(StrNoChr, j, 1), "_")
      Next
      StrName = Trim(StrName)
      With wdApp.ActiveDocument
        .SaveAs Filename:=StrMMPath & StrName & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
        ' and/or:
        '.SaveAs Filename:=StrMMPath & StrName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
        .Close SaveChanges:=False
      End With
    Next i
    .MainDocumentType = wdNotAMergeDocument
  End With
  .Close SaveChanges:=False
End With
wdApp.DisplayAlerts = wdAlertsAll
wdApp.Quit
Set wdDoc = Nothing: Set wdApp = Nothing
Application.ScreenUpdating = False
End Sub

If you already have a mailmerge main document set up with (or without) filtering, you could use the following Word macro to retrieve the SQL statement:

Sub GetSQL()
With ActiveDocument.MailMerge
  If .MainDocumentType <> wdNotAMergeDocument Then
    MsgBox "Mail Merge Query String:" & vbCr & .DataSource.QueryString
  Else
    MsgBox "Not A Merge Document"
  End If
End With
End Sub

 

[kryptonian]

Thanks, Paul!

I'm trying out your code but I'm getting flagged off the bat at "wdApp As New Word.Application".

When I comment this line out, I get flagged at this line -- "Set wdDoc = wdApp.Documents.Open(FileName:=StrMMDoc, AddToRecentFiles:=False, ReadOnly:=True, Visible:=False)"

...and just to confirm, I place this in Excel, right?

 

[Macropod]

Did you read the first line in the macro? And yes, you'd run the macro in Excel.

 

[kryptonian]

Thanks, Paul!

I'm trying out your code but I'm getting flagged off the bat at "wdApp As New Word.Application".

When I comment this line out, I get flagged at this line -- "Set wdDoc = wdApp.Documents.Open(FileName:=StrMMDoc, AddToRecentFiles:=False, ReadOnly:=True, Visible:=False)"

...and just to confirm, I place this in Excel, right?

Solved this. I had to set the reference from Tools>References>Microsoft Word for it to work. That worked but now I'm hanging mid code at the .mailmerge part.

 

[Macropod]

Provided you've-
• actually done the mailmerge setup in your mailmerge main document;
• referred to it correctly (e.g. do you need to change the document name: 'MailMergeMainDocument.doc' and is it in the same folder as the Excel workbook?);
• used the correct SQL statement (e.g. is the worksheet named 'Sheet1' - note the necessary inclusion of $ in the SQL statement?); and
• chosen the correct field(s) for the filename (e.g. do you need to change 'StrName = .DataFields("LAST_NAME") & "_" & .DataFields("FIRST_NAME")'?),
I can see no reason for it hanging. It works just fine when I run it with the correct parameters.

 

[kryptonian]

Finally was able to make it work! Thanks for the assist Paul!

 

[Macropod]

One thing to be aware of when running a mailmerge from the workbook that is also the datasource is that, since this means having the workbook open, some fields might not produce the desired output. Times, for example, may appear as decimal numbers. There are two options for dealing with such peculiarities:
1. Run the merge from a different workbook, preferably with code that ensures the workbook used for the datasource is closed; or
2. Use field coding to convert the data to the correct format. With times, for example, the field code might be:
{QUOTE{SET Time {MERGEFIELD TIMEOFDAY}}{SET ss{=Time*86400 \# 0}}{SET hr {=INT(ss/3600) \# 0}}{SET mn {=INT((ss-hr*3600)/60) \# 0}}{SET AMPM{IF{hr}< 12 "AM" "PM"}}"{=MOD(hr+11,12)+1 \# 0}:{mn \# 00} {REF AMPM}"}
where 'TIMEOFDAY' is the field name.

 

[Mistolo]

Anyways anyone is still helping on this?