Mail merge to generate a PDF and a control file

[amitash]

Hi,

I have a Word letter template and an Excel file as a data source to do mail merge in Word. What I want to do is open the Word letter template file (which has code inside), run the maro to do mail merge (get data source from an Excel), print as a PDF file (concatenate of all the mail merge letters into 1 single PDF), and also generate a control/audit trail file which contains the total no of pages and no of records.

I tried to use below code and can successfully generated a merged PDF file. However some problems:
(1) This will close the orginial Word template document, but generate a new Word document (a Word file with all the mail merge letters, the source of PDF). How can I keep the orginial Word doc open and close the generated Word file after running the code?
(2) How can I write the total no of pages and no of records to a newly
generated file, like *.txt as a control/audit trail file?


Public Sub MergePDF()

' This tries to speed up the macro.
Application.ScreenUpdating = False
Application.DisplayAlerts = False

' This does a mail merge with the word Document.
With GetObject("U:\wordletter.docm")
With .MailMerge
.MainDocumentType = wdFormLetters
.OpenDataSource Name:= _
"U:\excelsource.xlsx", _
ConfirmConversions:=False, ReadOnly:=True, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:= _
"Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=U:\excelsource.xlsx;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=37;Jet OLE" _
, SQLStatement:="SELECT * FROM `Sheet1$`", SQLStatement1:="", SubType:= _
wdMergeSubTypeAccess
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
.Execute False
End With

' This prints to a PDF file and saves it to a designated folder.
pdfname = "TestMergePDF- " & Format(Date, "mm-dd-") & Format(Time, "hhmmss") & ".pdf"
.Application.Documents(1).ExportAsFixedFormat pdfname, 17
.Close 0
End With

End Sub


Thanks!

[macropod]

Nothing you've described requires a macro except, perhaps, the saving of the output file as a PDF (and you don't really need a macro for that, since you're evidently using Office 2007 or later). Without a macro, your mailmerge main document will remain open and the output document will be active. A macro in the mailmerge main document could be used to save the output document as PDF and to close it, leaving only the mailmerge main document active. For example:
Public Sub MergePDF()
Application.ScreenUpdating = False
Dim i As Long
' This does a mail merge with the word Document.
With ThisDocument.MailMerge
i = .DataSource.LastRecord
.Execute
End With
With ActiveDocument
'This inserts a last page with the merged record count
.InsertAfter Chr(12) & vbCr & i & " Records Merged"
' This saves the output as PDF and closes it
.SaveAs2 Format:=wdFormatPDF, AddToRecentFiles:=False, _
FileName:="TestMergePDF- " & Format(Date, "mm-dd-") & Format(Time, "hhmmss") & ".pdf"
.Close SaveChanges = False
End With
Application.ScreenUpdating = True
End Sub