Access 2007 - Query export to Word mail merge w/Command Button

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

draytond

Thread Starter
Joined
Jul 20, 2011
Messages
220
I have a form with a button. I need to be able to press the button and have Access export a query to Word 2007's mail merge. Is this even possible? All the tutorials I can find say I need to select the query from the Navigation Pane, then go to External Data -> Export -> More -> Merge it with Microsoft Office Word. The problem is that I don't want to give the user access to the Navigation Pane.

Thanks in advance.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
You write a procedure that does just that and have it triggered by that button.
 

draytond

Thread Starter
Joined
Jul 20, 2011
Messages
220
Is there any chance you can show me a sample of what that might look like? I can read VBA but not write it. If I have a template to work off of, I should be able to figure out how to fit it to my form.
 

draytond

Thread Starter
Joined
Jul 20, 2011
Messages
220
Hm, close but no cigar. It looks like that's set up to export data into a blank document, not a mail merge document. So instead of inserting the data into merge fields, it brings up a document that says things like:

ENTRY – First Field Value
ENTRY – Second Field Value
ENTRY – Third Field Value


So almost... Still not sure how to approach this.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
I'm glad I don't smoke :)
If you already hava a document with the needed fields, why not approach it from Word?
Record a macro in word that does just that?
 
Joined
Jul 26, 2011
Messages
143
Create a make table with the fields you want and set the criteria. Then have vba attached to your button to open the make table query and then export the new table. I actually done it last week.

Another way of doing this is using bookmarks, place bookmarks into your word doc where you need a field to be merged, then in your access vba write them bookmarks in the code.
 

draytond

Thread Starter
Joined
Jul 20, 2011
Messages
220
That might work, but again, I have no idea how to actually code that. I don't know how to write VBA.

Let's say I have Table A and Table B, and a query that pulls info from both of them called Query Z. I want to export Query B's info into a Word mail merge. What would the code that I need to attach to the button look like?
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
The query is the soruce, so just open word and marge the document with the field generated in the Query B.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
What exactly do you want to do, email people with the body of data from the table(s) in the body of the email?
email people with the body of data from the table(s) in the body of the email using word as the email body?
email people with the body of data from the table(s) in the body of a Word doc as an attachment?

I have the VBA code for doing 1 & 3 but not 2, it does not actually mail merge, it works directly with the emailing program.
 

draytond

Thread Starter
Joined
Jul 20, 2011
Messages
220
Our nonprofit makes contracts with village peoples in other countries. The deal usually goes like this: they agree to protect 300 acres of the rainforest near their village for 10 years in exchange for us giving them the funds needed to build a much needed facility in their village (health clinic, water system, community center, etc.). It's a win-win kind of thing. But to make sure it actually happens, not only are we very strict about how the money gets into their hands, but also about how the process actually proceeds. It starts with the generation of a "Communitiy Covenant" that's essentially a contract between our nonprofit and the people of the village. The contract is very similar between villages, but details like the village name, the country, the amount of money, the date, etc. all change from project to project. So what I need to be able to do (ideally) is let the user hit a button, and have Access send all its information as a data source over to an already-created Word template with merge fields that will allow them to create a custom covenant with the click of a mouse.
 
Joined
Jul 29, 2001
Messages
21,334
Have you ever considered creating a report that would serve the same function?
 

draytond

Thread Starter
Joined
Jul 20, 2011
Messages
220
Keebellah, that's exactly what I'd like to do, but by clicking a button. The problem with using the query to merge the document is because we only want to create one document based on a specific record's information based on the record that's selected in Access at the time of the actual merge. So say there are 10 projects in the database. It's time to make a document for project #8. I go into Project #8's record (viewable on a form in Access), hit the "Generate Documents" button, and (hopefully) Word will pop up with the merged document, using the record's information as its datasource. I know Access has this functionality since there are tutorials on how to do it by using the export options, it's just a question of how to squeeze that functionality into a button so that people who aren't familiar with Access can do this task without any problems.
 

draytond

Thread Starter
Joined
Jul 20, 2011
Messages
220
Rockn... you're brilliant. That might in fact do the trick. The only problem I can see with that approach is that it wouldn't allow the user to edit the document in order to fine tune it after it's been created (unless they go into the design of the report, which would change it for every new report). And they'll almost definitely have to fine tune it because there are so many variables in what these reports can show that not even the most complex conditional merges can take care of it all on Word's end. We also need to be able to save copies of the generated documents on our server for our records, and I don't think Access lets you save individual instances of reports, does it?

Hmm... something to think about. I think Word would be ideal if possible, but I might have to keep a report as a fallback plan. People would just have to copy and paste the report's text into Word for further editing.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
I can provide the VBA for filling in the "Bookmarks" in a Word Doc with data from the database.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top