How to get VBA to fire in mail merge target document

[digitalsage]

Hello,

I'm doing a basic mail merge for the most part. However, the one standard activity is that after the mail merge completes and populates multiple tables in the target merged document ("LetterX"), I want VBA to fire that cycles through all of the cells and conditionally changes some based on the text contents of the cells.

I have code that does this, but how do I:

1. Get the VBA into the merged document?
2. Get the VBA to "fire" after the merge is complete?

I've done a little poking around on this and found the "MailMergeAfterMerge" event, and the others that are similar. I'm guessing that this will be part of the solution?

Any ideas or suggestions would be well received.

Thanks,

Mike

[macropod]

Hi Mike,

Is there any reason you can't do all the conditional stuff via the mailmerge process itself? Word supports IF fields for generating conditional output, etc.

If you're wedded to the vba approach, what have you tried so far?

[digitalsage]

I'm not married to any particular approach.

I would love to use a .NET console or GUI app to build a Word doc from scratch using a SQL Server database backend, but that's overkill -- this is an interim solution that will get tossed out in around 6 months (probably to be replaced with data from the source system funnelled through Business Objects). That said, this process will probably help define the requirements for the final solution.

The situation I'm in right now:
1. I'm trying to automate a report as much as possible.
2. Source data exist in multiple Excel spreadsheets.
3. Current form is a Word document.
4. Part of what must happen is that certain cells in multiple tables must be evaluated and background colors, patterns need to be changed at times depending on the text in those cells.

My challenges are:
- I would prefer to keep all the logic in a single file
- I would prefer to eliminate as much user interaction as possible... the "owner" of the business process changes on a regular basis
- I would prefer not to modify Normal.dot if possible, because that makes the report harder to use if the process owner changes.
- Word mail merge doesn't seem to handle multiple Excel files well, if at all... the final report is actually three separate mail merges, and all of the resulting docs need to be merged into a single file.

If you have any recommendations on how to best handle this situation, feel free to pass them on.

Where I was headed was trying to keep everything in a single Word file and a single Excel file for the mail merge. The single Excel file would pull data from the other Excel files. I was thinking about writing a short C# console application to trigger the activities to "build" the final report (launch Excel, refresh mail merge feeder file from the source Excel files, launch Word and conduct the mail merge). Writing the console app is probably overkill, but that's just the idea I was kicking around.

[digitalsage]

If anyone is interested in the part of my discussion that revolves around writing a console application to automate the conduct of the mail merge, I found a posting at http://www.pedautreppe.com/post/VSTO--NET-35--Word---How-to-mail-merge-a-document-.aspx that I was able to adapt to my needs.

I still would like some code that would also load the "main" Excel file used for the mail merge and do a refresh of the data from the "feeder" Excel files... if anyone has seen code (preferably C#) that does that, I would love to hear from you.

[macropod]

Hi Mike,

Word mailmerges can only interrogate a single data source - AFAIK, using .NET or vba doesn't change that. If you've got three data sources, that necessitates three mailmerges - unless you can combine the source data files.

With a Word mailmerge, you can test the source data and format the font & background shading accordingly. You can't change cell colours, per se, however. But you could embed different tables in the IF fields and output the ones you need.

Provided you can combine the data sources into a single file (eg as you suggested, an Excel workbook that links to the other three and consolidates the data required for the merge), all the logic can be built into a single Word file, without recourse to vba and without needing to change the Normal.dot file.