Pass argument(s) to Word macro, e.g. for merging to separate documents

[Hasse]

Hey,

Consider a Word main document & data file ready for 'mail merge'. Word provides this feature BUT you can only produce one (1) file at the time, even if you mail merge for all records ... I can understand this is sufficient when all letters are print out 'hard copy'. We want, however, a separate file for each record: it is a private letter which needs to be sent (also) by e-mail to each instance (in private). Also more in general however, when the mailing must be spread digitally, an X00 pages document with the relevant piece submerged somewhere inside it, is not an option.

How can this be done?

I think that I'm quite close, but must solve one problem (supposing there are no dragons left waiting behind the corner): how to provide a variable to a Word macro from within Access?

The rest of this post is some background about the progress I made to solve this. I hope I'm heading in the good direction…



1. Recently I used Access-VBA for batch processing Word documents (based on a most useful application obtained from the thread "Find and Replace text in Word document from Access with VBA&quot. The principle is to store a macro in (your) Word (template) & run it from within Access for all documents of which the file name & path are stored in a table (by a previous procedure). We used it for some tens/hundreds of Word documents to
- print
- apply format (e.g. font = Arial for all text)
- find-replace
- in the future: load specific text fragments into an Access table
- check for some features…
Really useful.
One restraint: it only works if the macro is the same for all documents. If you want to adapt it depending on the document you're working on, you're stuck… (or at least I am)

2. I have a Word macro to create multiple record 'private'/single mail merge documents with a minimum effort: an inputbox asks the (kandidate key) field value for which you want to mail merge, creates the desired single letter, stores it (e.g. as Title_CustName.doc), asks if you want to continue with a new one, etc.

3. So the only next step appears to be to provide the macro/procedure with an argument, e.g. Sub Merge(strValue) (so that you can pass through each time a different value, while repeating this macro over and over...) . Right?

Or: factually, the mail merge data originate from an Access database. So why not scroll through the original table/query? Then, for each record, you provide the field value to the Word macro called to mail merge into a single document…

That's the idea. I'm looking already for an alternative, e.g. I could
(alt 1) automate in Word itself, and let Word get the ID's from within the record source (Word/Excel/Access). But that' still unknown terrain to me.
(alt 2) integrate the macro job in the Access VBA procedures, but I didn't find any info about this (no good searching done?)
(alt 3) add a column to the data source containing a serial number ID, from 1 => nr of records. I let the mail merge query/filter use that ID instead of the original key field value. I insert a counter into the macro. I replace the original inputbox by the value of this counter & loop/repeat the procedure for counter = 1 to ... Something like that. Probably this will do & should be quite easy to achieve.

Still, automating this from within Access seems far more 'clean'. My humble opinion…

ps Fyi: in a later stage, I might use Access automation to send all members in a mailing list a personalised Word file (which I want to create here …). When this all works, and anyone is interested, I could send you the result. OR if you think all this is too much workaround & trouble as it can be done so easy using a completely different approach, please let me know. (But even then: the knowledge about how to provide the Word macro with a variable should be very helpful.)

THANKS IN ADVANCE

 

[Hasse]

PS I think about posting this at Woody's office Lounge as well. If I succeed & I get useful information there, I'll post it here as well. So long!