Software

How do I… Integrate mail merge in Microsoft Word with Excel

We'll use this inventory table for the examples in this article

This gallery is also available as a TechRepublic article and download.

Earlier in this series, you saw an article explaining how to harness the pure power of Word's mail merge feature and bend it you your will. In that article, you used Word's internal address book--really a very limited database--to keep track of the addresses to which information would be sent.

But, how many of you actually keep all of your contact information in Word? If you keep it in any Office program, you probably keep it in Excel, or in Access. Or, if you keep it in some other system, it's generally an easy process to get, for example, customer address information into Excel. In this article, I will show you how to make use of Excel data when using Word's mail merge feature.

I'm assuming that you know how to enter data into Excel and understand the purpose of Word's mail merge feature. If you need a refresher on either of these topics, take a look at previous articles in this series. I will be repeating some of the mail merge information from my previous article in this article in the interest of completeness. However, if you're interested in Word mail merge, you should read that article before you read this one.

Inventory sheets

For this article, rather than the typical form letter, we'll use an existing Excel used vehicle inventory to create inventory sheets that can be posted on each vehicle on the lot. Here's a snapshot of the Excel inventory table.

Note the column headings above. Every column has some kind of heading. While this isn't absolutely required, I do highly recommend that you assign a column heading to every column in your spreadsheet. Word will use these column headings as field names. If you fail to provide a column heading, you'll get things like "Column A", "Column B", which can be rather confusing. "Leather seats" and "Sunroof" are much more descriptive.

16 comments
clifforde
clifforde

Well writtem. I am curious how you have col. F (row 1) slanted into col. G. Any search just shows how to slant text within the same cell. Thanks

deepvibha
deepvibha

I have been using this feature since long. I have used it for preparing salary statements, generating yearly Income Tax statements, mailing the above statements etc. With more features in MS Office 2007, this has become a more versatile tool

chris
chris

Surely anyone using word at this level would be using some form of contact mamagement system such as Outlook, Act! etc.

stapleb
stapleb

I show people how to do this and I believe it is an extremely useful tool. You are right, Word is not designed to keep records the way Excel is. I also agree with you that it is better to have column headings as it makes it much easier to identify each field as it is entered into the master document - again with you, master document as a term can "come back and bite you" if incorrectly used.

stapleb
stapleb

It is just the degree that was chosen in the Format Cells, Alignment, Rotation section. It is probably something like 120 degrees. As the column is narrow, it just looks like it is in in Cell G1. I think any rotated or slanted text should have borders so it is easier to identify the cell into which the text has been entered.

dnstamp
dnstamp

Surprisingly, I find it's usually the adminstration staff that use the mail merge and they are not using Outlook, Act or any other contact mamagement system as most of the mailouts are to clients - not internal staff. Usually the admin staff uses Excel and are please to discover they can also connect to their Access data.

cesar_montinola
cesar_montinola

wonderful info. Would it be great if we can merge it to email? The company has rid of the printing and snail mail. Maybe merge it to a PDF so we can attach it?

wittmav
wittmav

Have a workbook with a series of charts, one for each employee, based on benefits dollar allocations. Would love to be able to use Word Mail Merge to create custom letters, each with a personal chart inserted. Is this at all possible?

dogknees
dogknees

Try that when you're not running Outlook. It's dead on arrival.

VGieseke
VGieseke

We prepare total rewards statements for client and have been doing this for several years using Word/Excel, so we know it works. We haven't yet figured out how to do it for online yet due to a step in the current process that is inherently manual (updating the fields) but I stumbled across your post while looking for ways around it. Here's what we do: Store the charts with a name that includes an identifier field. We use empIDChartname, for example 12345Pay In the Word doc, your field is {LINK Excel.Sheet.8 "\\\\drivenamen\\directorylevel1\\level2\\levelN||{MERGEFIELD "EEID"}Pay \p \* MERGEFORMAT} Then you merge your doc, and update the fields (Ctrl-A, F9) to get the charts to pull in.

stapleb
stapleb

wittmav@... I have never tried, and you have of course issued a challenge. I'll let you know how I go. I don't believe they can be identified as a field. I'm going to try to use a hyperlink to see what happens. Keep you posted.

stapleb
stapleb

The article/instructions do state, albeit in the small print, that "Outlook must be open..". So the beauty of all these types of articles is careful reading otherwise they bite you.

LMinGA
LMinGA

I am trying to do the same thing! Looking for some information.....

wittmav
wittmav

You say "store the charts with a name that includes an ID field". Where do you "name" the chart? I just opened Excel 2010 and in the Chart Tools > Layout ribbon there is a box for Chart Name in the Properties group. Is this what you mean? Also, in your field string, if all data (Excel and Word) is stored locally, skip the drivename and directory level references? Finally, if your empIDChartname is 12345Pay, where does that appear in your code statement? Drop it in where you have }Pay? Sorry for all the questions; trying to sort this all out! :-)

Mark.Mathews
Mark.Mathews

I'm doing a little expiramentation with this idea. I'm assuming you're going to be sending the letter to the same people each [month]. After you create the form letter, go to edit each letter and insert a chart (formatting the chart is a challenge). Replace the chart data with your data. Unfortunately, updating the data in your source spreadsheet does not update the form letter. You need to do that on each letter individually. Hopefully, someone will come up with a better way to do it. Mark M.