Mail Merge to a PDF

I need to do a Mail Merge from the Access 2013 application I am developing.  

The table that I will be doing the mail merge from contains many records, 1 record for each client.  Each client will get their own letter.  In addition, my client wants a pdf copy of each letter sent, to be associated with the client account.  



How do I generate a pdf of the mail merged output for each client?  It will need the storage location and name of the pdf to associate it with the clients account.

I will be doing the mail merge from Access 2013.  Here is the code for a test mail merge that puts merges all clients into a single output file.
This is the code (gotten from EE) I am using to test creation of a mail merge.

Public Sub CreateMailMerge_EE2()

'open the mergedoc
        Dim oApp As Object
        Dim MlMrge As Object
        Set oApp = CreateObject(Class:="Word.Application")
        oApp.Visible = True
        
        'this will open a new document based on the path as a template.  Excellent
        'but it doesn't open it as a mail merge
        oApp.Documents.Add "C:\My Documents\Access_Databases\JordanDelinq\MailMergeTestDoc.docx"
        ' so start a merge
        Set MlMrge = oApp.ActiveDocument.MailMerge
        With MlMrge
            'do the merge, get the datasource -- and be tricky if the Access app is opened exclusively!
            '
            ' Original //////////////////////////////////////////////
            '
            .OpenDataSource Name:="C:\My Documents\Access_Databases\JordanDelinq\MailTestData.accdb", _
            LinkToSource:=True, AddtoRecentFiles:=False, _
            Connection:="TABLE [tblNameFile]", _
            SQLStatement:="SELECT * FROM [tblNameFile]"
            '
            '/////////////////////////////////////////////////////////////
            '
            
            
            
             'show the field values
            .ViewMailMergeFieldCodes = 9999998 'wdToggle
            'merge to a new document
             'do you want merge to email instead
            .Destination = 0 'wdSendToNewDocument
            .SuppressBlankLines = True
            'blow through all the records
            With .DataSource
                .FirstRecord = 1 'wdDefaultFirstRecord
                .LastRecord = -16 'wdDefaultLastRecord
            End With
            .Execute (False) 'execute and don't stop for errors -- list them in a new Word document, if any.
        End With


End Sub

Open in new window

LVL 1
mlcktmguyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Luke ChungPresidentCommented:
If you are using Word, you can create a Word document that links to your data source in your Access database. A table or query. When you open the Word document, it can load that and create a new document that you can save as a PDF. You can do that interactive and with VBA in Word.

But is there a reason you have to use Word at all? Can you generate the report in Access and avoid using Word? If you can just stick to Access, you can send the report to a PDF.

We offer a commercial Microsoft Access product for email, Total Access Emailer, that sends personalized emails including filtered reports attached at PDF files. A free trial is available: http://www.fmsinc.com/MicrosoftAccess/Email/free-trial.html

Hope this helps.
GrahamSkanRetiredCommented:
Here is your code modified (just a bit) to illustrate a method for doing this. It produces one Word document and one PDF document per record.

It uses early binding, but I've tried to make it switchable to late binding.
Option Compare Database
Option Explicit

Public Sub CreateMailMerge_EE2()

'open the mergedoc
        'late binding for version compatibility
        ''Dim oApp As Object
        ''Dim MlMrge As Object
        
        'Word constants (already defined if early binding)
        Const wdDefaultFirstRecord = 1
        Const wdDefaultLastRecord = -16
        Const wdSendToNewDocument = 0
        Const wdDoNotSaveChanges = 0
        
        'Early binding for easier development
        Dim oApp As Word.Application
        Dim MlMrge As Word.MailMerge
        Dim mmdoc As Word.Document
        Dim docResult As Word.Document
        
        '
        Dim strOutputFolder As String
        Dim strMailMergeMainDocument As String
        Dim strDatabase As String
        Dim bNewWordApp As Boolean
        Dim rec As Long
        Dim strCustID As String
        
        'strMailMergeMainDocument = "C:\My Documents\Access_Databases\JordanDelinq\MailMergeTestDoc.docx"
        'strDatabase = "C:\My Documents\Access_Databases\JordanDelinq\MailTestData.accdb"
        
        strMailMergeMainDocument = "I:\Allwork\ee\28717544\Customer.docx"
        strDatabase = "S:\Allwork\Freeola\db1.mdb"

        strOutputFolder = "I:\Allwork\ee\28717544"
        'try to avoid multiple instance of the Word application"
        On Error Resume Next 'temporarily supress error checking
            Set oApp = GetObject(, "Word.Application")
        On Error GoTo 0 'resume error checking
        If oApp Is Nothing Then
            Set oApp = CreateObject(Class:="Word.Application")
            bNewWordApp = True
        End If
        
        oApp.Visible = True
        
        'this will open a new document based on the path as a template.  Excellent
        'but will only open it as a mail merge if the original is a mailmerge document
        Set mmdoc = oApp.Documents.Add(strMailMergeMainDocument)
        ' so start a merge
        Set MlMrge = mmdoc.MailMerge
        With MlMrge
            'do the merge, get the datasource -- and be tricky if the Access app is opened exclusively!
            '
            ' Original //////////////////////////////////////////////
            '
            .OpenDataSource Name:=strDatabase, _
            LinkToSource:=True, AddtoRecentFiles:=False, _
            Connection:="TABLE [tblNameFile]", _
            SQLStatement:="SELECT * FROM [tblNameFile]"
             'do you want merge to email instead
            .Destination = 0 'wdSendToNewDocument
            .SuppressBlankLines = True
            For rec = 1 To .DataSource.RecordCount
                With .DataSource
                
                    .FirstRecord = rec
                    .LastRecord = rec
                    .ActiveRecord = rec
                    strCustID = .DataFields("Cust_ID").Value

                End With
                
                .Execute (False) 'execute and don't stop for errors -- list them in a new Word document, if any.
                
                Set docResult = oApp.ActiveDocument
                docResult.SaveAs strOutputFolder & "\" & strCustID & ".docx"
                docResult.SaveAs FileName:=strOutputFolder & "\" & strCustID & ".pdf", FileFormat:=WdSaveFormat.wdFormatPDF
                docResult.Close wdDoNotSaveChanges
            Next rec
        End With
    mmdoc.Close wdDoNotSaveChanges
    If bNewWordApp Then
        oApp.Quit
    End If
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlcktmguyAuthor Commented:
Thanks you, I haven't had time to get back to this yet.  looking forward to testing your code.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

MurpheyApplication ConsultantCommented:
We use XperiDo as a Word MM generator. is compatible with all DB's using XML as interface.
mlcktmguyAuthor Commented:
GrahamSkan:  I revised your logic for my database and file locations

When I execute the logic I get error 'Incompatable File Type and Extension" on the statement

docResult.SaveAs strOutputFolder & "\" & strCustID & ".docx"

Here is my entire routine:

Public Sub CreateMailMerge_EE3_wPDF()

'open the mergedoc
        'late binding for version compatibility
        ''Dim oApp As Object
        ''Dim MlMrge As Object
        
        'Word constants (already defined if early binding)
        Const wdDefaultFirstRecord = 1
        Const wdDefaultLastRecord = -16
        Const wdSendToNewDocument = 0
        Const wdDoNotSaveChanges = 0
        
        'Early binding for easier development
        Dim oApp As Word.Application
        Dim MlMrge As Word.MailMerge
        Dim mmdoc As Word.Document
        Dim docResult As Word.Document
        
        '
        Dim strOutputFolder As String
        Dim strMailMergeMainDocument As String
        Dim strDatabase As String
        Dim bNewWordApp As Boolean
        Dim rec As Long
        Dim strCustID As String
        
        'strMailMergeMainDocument = "C:\My Documents\Access_Databases\JordanDelinq\MailMergeTestDoc.docx"
        'strDatabase = "C:\My Documents\Access_Databases\JordanDelinq\MailTestData.accdb"
        
        'strMailMergeMainDocument = "I:\Allwork\ee\28717544\Customer.docx"
        strMailMergeMainDocument = "C:\My Documents\Access_Databases\JordanDelinq\MailMergeTestDoc.docx"
        
        'strDatabase = "S:\Allwork\Freeola\db1.mdb"
        strDatabase = "C:\My Documents\Access_Databases\JordanDelinq\MailTestData.accdb"
        
        'strOutputFolder = "I:\Allwork\ee\28717544"
        strOutputFolder = "C:\My Documents\Access_Databases\JordanDelinq"
        
        'try to avoid multiple instance of the Word application"
        On Error Resume Next 'temporarily supress error checking
            Set oApp = GetObject(, "Word.Application")
        On Error GoTo 0 'resume error checking
        If oApp Is Nothing Then
            Set oApp = CreateObject(Class:="Word.Application")
            bNewWordApp = True
        End If
        
        oApp.Visible = True
        
        'this will open a new document based on the path as a template.  Excellent
        'but will only open it as a mail merge if the original is a mailmerge document
        Set mmdoc = oApp.Documents.Add(strMailMergeMainDocument)
        ' so start a merge
        Set MlMrge = mmdoc.MailMerge
        With MlMrge
            'do the merge, get the datasource -- and be tricky if the Access app is opened exclusively!
            '
            ' Original //////////////////////////////////////////////
            '
            .OpenDataSource Name:=strDatabase, _
            LinkToSource:=True, AddtoRecentFiles:=False, _
            Connection:="TABLE [tblNameFile]", _
            SQLStatement:="SELECT * FROM [tblNameFile]"
             'do you want merge to email instead
            .Destination = 0 'wdSendToNewDocument
            .SuppressBlankLines = True
            For rec = 1 To .DataSource.RecordCount
                With .DataSource
                
                    .FirstRecord = rec
                    .LastRecord = rec
                    .ActiveRecord = rec
                    strCustID = .DataFields("LastName").Value

                End With
                
                .Execute (False) 'execute and don't stop for errors -- list them in a new Word document, if any.
                
                Set docResult = oApp.ActiveDocument
                docResult.SaveAs strOutputFolder & "\" & strCustID & ".docx"
                docResult.SaveAs FileName:=strOutputFolder & "\" & strCustID & ".pdf", FileFormat:=WdSaveFormat.wdFormatPDF
                docResult.Close wdDoNotSaveChanges
            Next rec
        End With
    mmdoc.Close wdDoNotSaveChanges
    If bNewWordApp Then
        oApp.Quit
    End If
End Sub

Open in new window

GrahamSkanRetiredCommented:
I guess that, if unspecified, the format is the last one used, so second time around it is still expecting .pdf.

Try changing line 81 to
                docResult.SaveAs strOutputFolder & "\" & strCustID & ".docx", wdFormatDocumentDefault

Open in new window

mlcktmguyAuthor Commented:
Thank you, that's exactly what I needed.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Word

From novice to tech pro — start learning today.