Automate Mail Merge with VBA - Access Developer Forum


	Back to Access Developer Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Automate Mail Merge with VBA

David Britz

3 years ago

Hi folks!

I watched the old video on Mail Merge (I believe it was titled "Free Microsoft Word Mail Merge Tutorial") and found it very useful, however I'm up against a slightly bigger task. In this video the letters were printed every time by going into the mail merge using an Address book.

In this case I need to print through VBA. I have a form that basically looks up a single record and puts it in a special Mail Merge table (the source for the merge). There is a button on that form which is supposed to insert that one record into a letter template.

The query for putting the data in the Mail Merge table works just fine, the right record (Person A) is in there. However once I print, it shows Person B instead. If I open up the Word doc MANUALLY I find that now it correctly has Person B in there, and the next time running through the form it's now correct. Then I select Person C and run the code and it shows me Person B again. It's like the Mail Merge wants to print the previously selected name not the current one.

This is not the complete code but for brevity this is what is actually opening the document:

    Set wordApp = New Word.Application
    With wordApp
        .Visible = True
        Set WordDoc = .Documents.Open(Application.CurrentProject.Path & "\letters\test.docx")
    End With

This seems the easiset way to do it - do the Mail Merge wizard, hook it up to an existing table then simply open that document through code. But I must be doing something wrong.

Maybe a video topic? :) :)

	Scott Axton @Reply 3 years ago
David are you batching the letters or just printing them "one off"? The data source for your Word doc is the temporary table that you create. At some point you have to clear out (delete) the old records from the temp table if you don't want them to print again. Depending on how you are doing things would determine when you clear out the temp table.

	Scott Axton @Reply 3 years ago
By the way - I believe that Richard has some plans in the works to show some ways to interact between Access and the other Office products like Word and Excel. If that is something that interests you, be sure and leave him a comment here to encourage him to do so.

David Britz OP @Reply

3 years ago

Hi Scott,

So by temporary table do you mean the one I talked about creating, or something else Access creates behind the scenes? If the latter, it would make sense in that the table is still holding old records.

I'm not sure what you mean by batching the letters but I believe it is a one-off. I load the form which runs the query building the temporary table. We then generate the one letter from that one row, print and that's it. There's no reason to save the information.

Scott Axton @Reply

3 years ago

David
In your original post you said, "...puts it in a special Mail Merge table (the source for the merge)."  That is what I was referring to as your temporary table.  If there is still data in your Mail Merge table it is going to keep showing up. Unless you clear out that table at the start of each session. When and how you clear out that information is something you have to decide how to do.

By "batching" I was meaning processing the same letter to one or more people using their personalized information.
(Sorry - I was in the mailing business for 20 years prior to my current profession.  I'm guilty of thinking people know certain terms in the business.)
For one-off,   I meant print the current record and ...done.

For me - I tend to be a bit lazy (inefficent?)
I make my query, which is re-usable, then close down my db and hop on to Word.  I make up my letter in Word using the query in my db as the data source.
Keep in mind, I was working with many different clients data and usually processing between 500 (my minimum) upwards of 30K to 50K letters at a time.
Now days my needs, and possibly yours, would be much more simple.
=========
Maybe a video topic? :) :)

Check out the Letter Writer video.  That might be sufficient for what you are doing.
There are other courses that contain the "How To" if you just search the right terms.
Search the Microsoft Access Outlines (Ctrl-F) for what you are looking for.
Such as letters, mailing, merge.

Here are a couple that might interest you:
Access Mailing Label Seminar
Access Time & Billing Seminar

	David Britz OP @Reply 3 years ago
Makes sense but I don't think the client will like that.

	Scott Axton @Reply 3 years ago
No problem. Assuming that everything else is working, you just need to make sure that the Mail Merge table is cleared out prior to adding the next one. Feel free to post the code where you are adding in the current customer to send the letter if you cant figure it out.

David Britz OP @Reply

3 years ago

Yeah I'm not sure what you mean by "Mail Merge Table". Are you simply talking about the table used to get the data from, or is this some special temp table that Access uses.

I'll see if I can post the relevant code as concisely as I can below:

======================================

The query "Write Contracts..." below is what generates a table "Write Contracts Temp" which is what the Word doc is based on.

I wonder if I could "clear the table" as you say by running a DELETE * on that table before the query. Maybe I gave myself an idea.

The query goes something like:
SELECT <<stuff>> INTO [Write Contracts Temp]
FROM <<joiny type places>>
WHERE <<things are true>>

======================================

Private Sub btnPrintContract_Click()
    Dim wordApp As Word.Application
    Dim WordDoc As Word.Document

    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.OpenQuery "Write Contracts from edit reg form Make Temp", acViewNormal, acEdit

    Set wordApp = New Word.Application
    With wordApp
        .Visible = True
        '.Documents.Add Application.CurrentProject.Path & "\letters\ContractTemplate.docx"
        Set WordDoc = .Documents.Open(Application.CurrentProject.Path & "\letters\ContractTemplate.docx")
    End With

End Sub

	David Britz OP @Reply 3 years ago
Nah the clearing out of the table prior to running the add table query did nothing. Just prior to running the Word doc, the Temp table DOES contain the right data, it's as if the Mail Merge document is simply ignoring it and getting its data from somewhere else. This is really eerie :(

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developer Forum.

Next Unseen

New Feature: Comment Live View