MailMerge And Create PDF

I have some code from EE that will generate Mail Merge output using an Access Table and a Word Doucment.

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

I've tested the code an it works, presenting the complete mail merged document on the screen.

The customer also wants to be able to create a PDF, so certain generated documents are stored and associated with each account.

To execute the mail merge record by record I realize I'll have to change the select to only pull one record at a time and pass the recordID as a parameter.  I'm planning to use an outside loop that reads thru tblNameFile pulling the pertinent records and calling the mail merge routine with a specific record ID.

I'll change the select to :  SQLStatement:="SELECT * FROM [tblNameFile] Where ID = " & passedID

That way one record at a time will run though the mail merge.

Within the above loop how would I create a PDF for each tblNameFile record currently being processed?
How would I link the PDF to the tblNameFIleRecord being processed?

Is there a better way to accomplish this that using the current code loop I am using now?
Who is Participating?

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

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:
We offer a commercial product, Total Access Analyzer, that is an Access add-in which lets you send personalized emails from Microsoft Access to everyone in your table or query of email addresses. You can attach report(s) as PDF files filtered so that each recipient gets their own data. Multiple files can also be zipped into one file and optionally password protected. All of that can be done through the Emailer Wizard without programming.

A professional version lets you automate the process using VBA and includes a royalty-free runtime license.

A free trial version is available to let you send your own emails from your database:

Hope this helps.

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 but not interested in including any 3rd party products in this project.
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 Access

From novice to tech pro — start learning today.