asked on

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

Luke Chung

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.

ASKER CERTIFIED SOLUTION

GrahamSkan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mlcktmguy

ASKER

Thanks you, I haven't had time to get back to this yet. looking forward to testing your code.

Theo Kouwenhoven

We use XperiDo as a Word MM generator. is compatible with all DB's using XML as interface.

mlcktmguy

ASKER

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

SOLUTION

GrahamSkan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mlcktmguy

ASKER

Thank you, that's exactly what I needed.