Can You Automate Mail Merge Function??

[kanin247]

To Whom It May Concern:

Is there a way I can automate MS Word's Mail Merge function? I have a list of people in MS Excel and have been using Mail Merge to generate an XML file that lists those people. The Mail Merge works great but I just wanted to see if there was some way I can automate this process so that I won't have to go through all the Mail Merge steps.

Here's a sample of what I am doing:
MyList.xls
First | Last | Address | City | State | Zip | Phone
John | Smith | 123 Oak Street | NY City | NY | 01234 | 100-555-0001
Jane | Miller | 456 Pine Drive | Orlando | FL | 12345 | 123-555-4321
Bob | Jones | 987 Cherry Circle | San Jose | CA | 45678 | 321-555-7890

BeforeMailMerge.doc
<PEOPLE>
<FIRST><<first>></FIRST>
<LAST><<last>></LAST>
<ADDRESS><<address>></ADDRESS>
<CITY><<city>></CITY>
<STATE><<state>></STATE>
<ZIP><<zip>></ZIP>
<PHONE><<phone>></PHONE>
</PEOPLE>

AfterMailMerge.doc
<HEADER> -->Need to add this header & closing header (below) before saving as an XML file

<PEOPLE>
<FIRST>John</FIRST>
<LAST>Smith</LAST>
<ADDRESS>123 Oak Street</ADDRESS>
<CITY>NY City</CITY>
<STATE>NY</STATE>
<ZIP>01234</ZIP>
<PHONE>100-555-0001</PHONE>
</PEOPLE>
<PEOPLE>
<FIRST>Jane</FIRST>
<LAST>Miller</LAST>
<ADDRESS>456 Pine Drive</ADDRESS>
<CITY>Orlando</CITY>
<STATE>FL</STATE>
<ZIP>12345</ZIP>
<PHONE>123-555-4321</PHONE>
</PEOPLE>
<PEOPLE>
<FIRST>Bob</FIRST>
<LAST>Jones</LAST>
<ADDRESS>987 Cherry Circle</ADDRESS>
<CITY>San Jose</CITY>
<STATE>CA</STATE>
<ZIP>45678</ZIP>
<PHONE>321-555-7890</PHONE>
</PEOPLE>

</HEADER>

Any help will be much appreciated. Thanks!

~kanin

 

[jdhilljr]

Select 'TOOLS' 'MACRO' 'RECORD NEW MACRO'
Name the macro then select 'OK'
Do the mail merge.
After recording the entire process click 'STOP'

The all you have to do is run the macro as long as the source file remains the same name.


Jim

 

[kanin247]

Jim, THANKS! That worked great!

Just one more thing...now that I have word automating the mail merge, how do I create an executable that physically opens word, runs the marco, and closes out word?

 

[jdhilljr]

Use the AutoOpen setup.

Sub AutoOpen()
&& PLACE MACRO HERE
End Sub

This will give you a little more help.

http://support.microsoft.com/default.aspx?scid=kb;en-us;286310

Then schedule your task manager to open Word and the file.

Jim