mail merge with variable file name (1 Viewer)

i have searched all over and not yet found something that will work for me. i might be able to get "super easy mail merge" to work, but the problem is i want clicking the "merge" button to launch a file chooser box- so i can navigate to the word doc i want to use for the merge (destination doc will change with the currently selected record- i'm not merging a single record, however).

basically i want to do exactly what right clicking on a table/query, then choosing "export" then choosing "merge with word" does (access 2007).

can't do as a report because we may have "complex" letters- with graphics and/or tables.

why is something that is a simple right-click menu option so difficult to automate??

hmmm, "merge" per se, is normally intended to output specific fields from multiple records into a standard document 9same document for that specific merge result.

I'm guessing you probably aldarey know or have surmized this, hence the challenge trying to specifiy different destinations for the same merge.

I'm going to hazard a guess that these destination documents different perhaps in headings (different sales people), different companies for different products, etc.

You probably don't want to read this, but I'm sure you're going to need to code this whole process.

The good news is though, that if you have a known destination file (different) for each record you wish to use as a source to the destination file, you can specify that path\file in a table field and even automate that (avoiding the FileDialog entirely).

Word Merge via VBA from Access is not an uncommon goal and there are a ton of resources available with a Bing Search.

Bilbo_Baggins_Esq said:

The good news is though, that if you have a known destination file (different) for each record you wish to use as a source to the destination file, you can specify that path\file in a table field and even automate that (avoiding the FileDialog entirely).

Click to expand...

hey now there's an idea... if only i knew enough to put it into practice!

right now i create a new table with every list i generate, and then use mail merge to put the data from that table into the word doc fields. i want to do away with creating tables, though, and just add the data to an "ALL" table, then use a query to determine which records to use (query criteria set by field in currently open form on which "merge" button is placed) and then open the dialog box to find the word doc and finish the merge. (this should eliminate the hassle of scrolling through what will eventually be hundreds of table names to select the correct one to merge).

because i'd have the letter ID in the "ALL" table, and because the letter ID used is the same as the eventual word document file will be named, i could certainly create a table with the names as you suggested, i just don't know how to make SQL or VBA look at the table for a file name, and why it is better than filedialog.

i think i'm going to have to table this idea until i finish my database systems course (it starts in 2 weeks). i just don't know enough about VBA or SQL.

If you're not handy with SQL, you can build the appropriate query in Access using "Design View" and then save it with a name.
Then you can load that query as a recordset in the VBA and process each record individually.

Or, you can select "SQL View" and it will should you the straight up SQL text. in that case, you would still use VBA to load the SQL as a record set and process accordingly.

One difference is, with the SQL, you can specify a criteria for a field using variables from within your VBA.

i have built a query using access before, then viewed the sql and pasted into a module, but when it comes to editing more than just the contents of that part of the code i have no idea what i'm doing, and i just make changes and test them until i find something that works. that's why i hope my database class this fall will help me- it's supposed to have heavy focus on SQL and VBA. then i can learn WHY pieces of code that make no sense to me go where they go, and what should or should not go where in my code. i have a feeling a lot of light bulbs will be going on over my head in the next few months. =)

thanks for your insight!!