none
Word: Mail merge to generate a PDF and a control file RRS feed

  • Question

  • 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?

    <VBA>

    PublicSub 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

    </VBA>

    Friday, July 20, 2012 6:05 AM

All replies

  • Try the following:

    Public Sub MergePDF()
    Dim pdfName As String
    Dim oDoc As Document
    Dim oMerged As Document
    Dim oRng As Range
    Dim iPages As Long
    Dim iRecords As Long
    ' This tries to speed up the macro.
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Set oDoc = GetObject("U:\wordletter.docm")
    With oDoc.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
    Set oMerged = ActiveDocument
    Set oRng = oMerged.Range
    oRng.Start = oRng.End
    iPages = oRng.Information(wdActiveEndPageNumber)
    iRecords = ActiveDocument.Sections.Count - 1
    ' This prints to a PDF file and saves it to a designated folder.
    pdfName = "U:\TestMergePDF- " & Format(Date, "mm-dd-") & Format(Time, "hhmmss") & ".pdf"
    oMerged.ExportAsFixedFormat pdfName, 17 'Path?
    oMerged.Close 0
    Open "U:\LogFile.txt" For Append As #1
    Write #1, "Pages - " & iPages & "  Records - " & iRecords  ' Write data."
    Close #1 ' Close file.
    Documents.Open oDoc.FullName
    End Sub


    Graham Mayor - Word MVP
    www.gmayor.com

    Friday, July 20, 2012 7:53 AM
  • Thanks! It works perfectly!!

    BTW, I just found out that everytime I open this Word file, it prompts below message asking me to choose Yes and No:

    Opening this document will run the following SQL command:

    SELECT * FROM 'Sheet1$'

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

    How can this be disappeared when I or any other users using different PC open this Word file?

    Friday, July 20, 2012 8:21 AM
  • You can fix that with a registry hack - You receive the "Opening this will run the following SQL command" message when you open a Word mail merge main document that is linked to a data source - http://support.microsoft.com/?kbid=825765. See also http://www.gmayor.com/word_vba_examples.htm

    Graham Mayor - Word MVP
    www.gmayor.com

    Friday, July 20, 2012 12:07 PM
  • As users of this macro file will not be the Admin users of Windows, does it mean there is no method to turn off this message when opening the file?
    Friday, July 20, 2012 4:16 PM
  • Not that I am aware of - this is one of the endearing features of mail merge. :)

    Graham Mayor - Word MVP
    www.gmayor.com

    Sunday, July 22, 2012 12:31 PM
  • This works wonderfully, but is there a way to save each page in the mail merge as separate files?

    Friday, February 15, 2013 5:37 AM
  • For splitting a merge document to PDF see http://www.gmayor.com/individual_merge_letters.htm  or if your data is in Excel format - see http://www.gmayor.com/ManyToOne.htm    both are capable of merging to separate PDF files. The latter is not a true mail merge so has some limitations, but offers a wider range of functions.

    Graham Mayor - Word MVP
    www.gmayor.com

    Friday, February 15, 2013 9:55 AM