Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rating: Thread Rating: 5 votes, 5.00 average. Display Modes
Old 08-14-2013, 07:46 AM   #1
bikermo
Newly Registered User
 
Join Date: Jan 2013
Location: Twin Cities, MN
Posts: 37
Thanks: 13
Thanked 1 Time in 1 Post
bikermo is on a distinguished road
mail merge with variable file name

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??

bikermo is offline   Reply With Quote
Old 08-14-2013, 09:15 AM   #2
Bilbo_Baggins_Esq
Newly Registered User
 
Join Date: Jul 2007
Location: Irving, Texas
Posts: 561
Thanks: 35
Thanked 24 Times in 23 Posts
Bilbo_Baggins_Esq will become famous soon enough
Re: mail merge with variable file name

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.
__________________
There are TWO ways to show appreciation to users:
1. If you feel a user has demonstrated compentence and skill, add to their reputation by clicking the "scales" icon under the users ID in the left panel.

2. Additonally, if you want to thank a user for his/her work and contribution to your project, click the "Thumbs Up" icon in the post.

Please be considerate of all users and refrain from sending direct requests via Private Message.
Not only do you limit your access to other experts, but you limit other's access to solutions that may help them.
Bilbo_Baggins_Esq is offline   Reply With Quote
Old 08-14-2013, 09:46 AM   #3
bikermo
Newly Registered User
 
Join Date: Jan 2013
Location: Twin Cities, MN
Posts: 37
Thanks: 13
Thanked 1 Time in 1 Post
bikermo is on a distinguished road
Re: mail merge with variable file name

Quote:
Originally Posted by Bilbo_Baggins_Esq View Post
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).
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.


Last edited by bikermo; 08-14-2013 at 09:53 AM.
bikermo is offline   Reply With Quote
Old 08-14-2013, 10:23 AM   #4
Bilbo_Baggins_Esq
Newly Registered User
 
Join Date: Jul 2007
Location: Irving, Texas
Posts: 561
Thanks: 35
Thanked 24 Times in 23 Posts
Bilbo_Baggins_Esq will become famous soon enough
Re: mail merge with variable file name

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.
__________________
There are TWO ways to show appreciation to users:
1. If you feel a user has demonstrated compentence and skill, add to their reputation by clicking the "scales" icon under the users ID in the left panel.

2. Additonally, if you want to thank a user for his/her work and contribution to your project, click the "Thumbs Up" icon in the post.

Please be considerate of all users and refrain from sending direct requests via Private Message.
Not only do you limit your access to other experts, but you limit other's access to solutions that may help them.
Bilbo_Baggins_Esq is offline   Reply With Quote
Old 08-16-2013, 07:47 AM   #5
bikermo
Newly Registered User
 
Join Date: Jan 2013
Location: Twin Cities, MN
Posts: 37
Thanks: 13
Thanked 1 Time in 1 Post
bikermo is on a distinguished road
Re: mail merge with variable file name

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!!

bikermo is offline   Reply With Quote
Reply

Tags
mail merge

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
MS Word Mail Merge File Path Error abbaddon223 Modules & VBA 1 02-08-2013 08:47 AM
Mail Merge problems with a text file boomer Modules & VBA 1 01-02-2009 08:40 PM
Database Password stopping mail merge and catalog merge???!??! sgtblitz General 1 04-17-2008 10:46 AM
Variable File Path for Mail Merge aTmchick Modules & VBA 1 02-22-2005 10:06 PM




All times are GMT -8. The time now is 12:53 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World