Automate Word Mail Merge from Excel?

[risoam]

Is there a way to automate a MS Word mail merge from an Excel worksheet.

I have a workbook with about 8 or 9 worksheets in it. Each worksheet is a name and address of someone who a letter needs to be sent too. I have 8 or 9 formatted word documents with the mail merge fields already in place.

Daily, people will add to the worksheet names and addresses of people who they need to sent the letters out to. At the end of the day, I would like them to go into the workbook, and if there are new names and addresses in any given worksheet to be able to merge the document and print it?

Thanks!

 

[SkipVought]

risoam,

Why do you have a SEPARATE worksheet for each name? If there are compelling reasons why separate sheets are necessary, then write a routine to capture the name and address from each sheet and populate a table with the names and addresses. Then Word can reference that table for mailmerge. I would suggest naming the mailmerge table so that when Word gets the external data from your workbook, you can reference the table name.

Hope this helps Skip,
SkipAndMary1017@mindspring.com

 

[risoam]

Thanks for responding. Each worksheet goes with a different letter.

 

[SkipVought]

Well, to use a mailmerge, you need a SINGLE table with common headings like; name, address, city, state, zip, salutation....


??? Skip,
SkipAndMary1017@mindspring.com

 

[spudk]

hi,

Did you every get it to work?

I'm doing the same but with all the data on one sheet.

The list of names and email address are on sheet 1 and the General email is on Sheet 2 with a VBA button that SHOULD run the code to do an email mail merge however i'm having trouble using sheet 1 as the Data Source, So i have to copy the data in sheet 1 to a new workbook and then use wrdDoc.MailMerge.OpenDataSource Name:="C:\Temp_Store.xls", Connection:="Sheet1", this takes to long as is stupid.

I want to use sheet 1 !!! help please.

 

[spudk]

see thread707-342664

 

[SkipVought]

Hi, Spudk,

Here's the way I would do it...

1. On the data sheet, name all ranges using Insert/Name/Create...

2. On the document sheet, name the target cells

3. Write VBA to 1) read each row from data sheet, 2) map data from each row to document sheet named cells

Hope this helps

Skip,
SkipAndMary1017@mindspring.com

 

[Guest_imported]

Wow! This is exactly what I am trying to achieve. I am a newbie and I am look for the exact VBA code to use to link an Excel worksheet to merge data into a Word mail merge document.

My Excel worksheet is a main database of names, address, and accounts. A user selects from this database who is to receive a letter (the letters are all the same). Based on the user's selection of a check box a macro assigns a confirmation number.

I've tried inserting an OLE object in the Excel workbook as the mail merge Word document, but just can't seem to get it to work.

I'm looking if some one can help me with actual code.

Your help is greatly appreciated!!

 

[SkipVought]

KOKO,
You don't need VBA to have your database as an Excel document and use mailmerge in Word. Use the MailMerge Wizard, select the Excel document as your source and continue with mailmerge. You might want to Name the table in Excel if you have other tables in that workbook.

Am I missing something? Skip,
SkipAndMary1017@mindspring.com

 

[ItripleA]

Skip,

I would like to automate the procedure for the user, so after they mark their selected records in the Excel worksheet they would then hit a radio button with an assigned macro that will automatically run and print the mail merge.

Possible?

KOKO