Automating mail merge from excel

[Cheese_whiz]

Hi all,

I'm mostly an access guy, so I'm not unfamiliar with vba (access vba,
anyway)...

I think I need to automate a word mail merge from Excel. What I need to do
is check (or filter) a list in version 2003 for no entries in a date column.
For those records that have no entries in the date column, I need to merge
those into a word document and then add today's date (Date()) as the column
value to those records. I want to print the word document, but most likely
I'd be just as happy if not more happy to do that manually once the merge has
been completed.

Does that sound like working from excel is the way to go, maybe utilizing an
already setup mail merge file in word? Could I (in vba) filter the records,
run the merge, and then append the date values from within excel? Can anyone
point me to some code to start me out?

Any direction would be greatly appreciated. I've automated exel and word
from access, but never word from excel, and I'm not even sure what I'm asking
is feasible.

Thanks,
CW

 

[Peter T]

I don't quite follow why you need to automate Word's mail merge from Excel,
or rather which aspect(s) of the entire process need(s) to be automated.

I can see perhaps you might want to automate the creation of your Mail Merge
data in Excel. Filter your data as required, copy it to a dedicated sheet
(in same or another file). Alternatively to a Named range. Ensure the top
row is headers and there are no entirely blank rows. All this process might
be pretty straightforward, manually or programmatically. Don't forget Word
also has the ability to filter data, in a similar way to Excels Data/filter,
depends on what you need.

In Word, Tools / Letters & mailings / Mail merge, follow the wizard. In step
3 browse to your Excel file, and again follow the Wizard. When almost done
don't preview but save the doc as a Template.

Of course there's a bit more to it, no doubt the entire process can be
automated from either Excel or Word but would need to know more details (I'm
not a mail merge expert!).

Regards,
Peter T

 

[Cheese_whiz]

Thanks for the reply, Peter.

The reason I wanted to do it from excel was so I could execute some
functions IN excel after the automation code ran. While I could probably do
that from word as well, it just seemed easier to start in excel, and a more
logical approach for my users, too, who may want to see the merge data
directly in excel.

I think I've found a suitable solution to this issue. Automation doesn't
seem to work with mail merge anyway. I'm guessing like in other situations,
things can be a little different if you open a program and then open a file
in that program than if you just open the file directly. Hopefully, I can
get this shell command working....not sure what the issue is....used shell in
any number of other situations.

Anyway, thanks for the response.

CW

 

[Peter T]

I don't see why you can't automate the entire mail merge process either from
Excel or Word or from some other appropriate app.

Indeed there are several differences between Shell and normal automation,
though I can't think how you'd do this particular task with Shell.

Regards,
Peter T