Lesson 12 – External Data Sources & Mail Merge

In the last two lessons you learned about using Excel Tables and Pivot Tables. Both of which are excellent ways for organizing and analyzing your data without having to create a lot of formulas or complicated scenarios yourself. Instead you let Excel do the heavy lifting for you. Now that your data is prepared you most likely need to share it at some point in time. Throughout the course of day to day operations in any small business there is a need to communicate with customers. Many times this is as simple as a phone call, but there may often be times that you need to communicate some form of data with them. This can start with a welcome letter, introduction to your business, Thank You’s, invoices, etc. How often do you prepare this correspondence by hand, writing one letter or e-mail after another? Fortunately, there’s an easier way and it involves sharing your data between Excel and Microsoft Word. This lesson is going to focus using Word to create a customer letter which you’ll print and mail (although you can also choose to e-mail Mail Merge documents too), and having Excel populate that with all of your relevant customer information. As for your data, while you may well have your customer data in Excel, but in this case we’re going to go on the assumption that you have it elsewhere, so another part of the lesson is going to concentrate on bringing customer information into Excel from an External Data Source, in this case, Microsoft Access.

As we discussed in the Pivot Table lesson there are often occasions in which you need to query an external data source in order to retrieve smaller data sets into Excel for analysis purposes. An example would be a transactional database in which an order entry system keeps track of customer purchases. In this case we’re going to retrieve information from a database for a given time-period, perform some data analysis on it, and send our customers a quarterly purchase recap. The data source for the Lesson will be Microsoft’s Northwind Database, which is included with each version of Microsoft Access. But just because we’re going to use Access, doesn’t mean that you can’t query other data sources; you can pull data from other databases, text files, websites, etc. Once we pull the data into Excel we’re going to set up a Pivot Table to summarize the data and then create a Word document that we’ll populate with the Excel data. You don’t necessarily need to use Excel as a data source for Word either, but we’re going to approach this lesson from the standpoint of needing to perform analysis on our external data before we can use it in Word. Provided that your data is structured properly you could certainly pull directly from it without having to use Excel (but since this is an Excel course, we can’t really leave it out can we?)

External Data Sources

The first thing to learn is how to get your data into Excel. As mentioned, we’re going to use an Access database for this lesson, by linking to a pre-existing Access database table, but the methods will be very similar for other sources. This is simply going to give you an idea of the process that’s involved.

• • Get External Data – Anytime you want to pull data into Excel from another source you start on the Data tab. Fortunately, because Access is part of the Microsoft Office Suite of applications, they have made Access a default option, and it plays remarkably well with Access, as does Word.

 

Figure 467  

• • As soon as you click on the “From Access” option, Excel will open up a Select Data Source dialog, which is very much like a File Open dialog, where you’ll see that we’ll browse to the Northwind 2007 database. If you wanted to use a different data source you could either choose “All Files” from the Files of Type drop-down (which isn’t labeled, but it’s indicated in the example), or select one of the other options from the Data tab.

 

Figure 468  

• • Once you choose your data source Excel will load another dialog asking you specifics about the data connection, where you’ll see Provider, Connection, Advanced and All tabs. Fortunately, Excel will do the bulk of the work for you here (especially with Access), so you generally don’t need to argue with the dialog and make any changes.

 

Figure 469  

• • As soon as you confirm that dialog you’ll get another one. Generally the only information you’ll need to provide here is a password to the external data source if you have one. As soon as you do that you’ll be prompted to select a table from the data source. Note that you’re not limited to Tables, but Queries as well. In case you’re wondering, in this case Excel is calling Queries “VIEW”s.

 

Figure 470  

• • For this lesson we’re going to select the Invoice Summary table which was created for this lesson (a copy of the database is provided along with the Excel companion workbook).

 

Figure 471  

• • Next Excel will ask you how you want the data returned to your workbook. For example purposes we’ll import the data as both a Table and a Pivot Table, examples of which you’ll see next.

 

Figure 472  

• • The first thing you’ll notice about the Access query imported as raw data is that Excel automatically creates the data as an Excel Table. If your query is set up in such a fashion that it needs no further manipulation then you wouldn’t need to go the Pivot Table route. In this case we want to summarize some of our order data so we are going that extra step. Note that when it comes to merging the data in Word, it doesn’t care how many data fields you have; you only merge the ones that you want.

 

• • Pivot Table Report – When you choose this format Excel isn’t going to create a Pivot Table for you. Instead, as you saw in the Pivot Table lesson, Excel will analyze the Access query data and prepare the Pivot Table dialog and Field List for you. It’s up to you to create the Pivot Table from there, which you’ll see next where we’ve summarized our order information by First Quarter sales only and used the Tabular Form layout.

 

Figure 473  

• • There is only one problem to using a Pivot Table as a Mail Merge source and that’s the blank rows that Excel inserts above the Pivot Table. Word won’t read the blank rows, so when your data comes over it won’t have any row headers, just ambiguous field labels like F3, F4, etc., but there are workarounds that are important to know, so we’re going to go that route. You could also just take the Pivot Table data only and copy it into another worksheet. The next step is to move onto Word to create the Mail Merge document and get it ready to receive your Excel data.

 

 

Figure 474  

Create your Word Document

Before you can create a Mail Merge you have to have a Word document to populate with your customer information. In this case we’ll be using the “Notice of Valued Customer Status” letter from the Office Online Template Gallery. You can access it through Word via File, New, Letters, Business Letters, Billing and Order Letters, but you can certainly choose any template or letter of your own. The first things you’ll see are the form fields that the template designers added for you. Note that these aren’t Mail Merge fields, but form fields that we’re going to replace.

 

Figure 475  

Mail Merge – Step 1 - Next we’re going to walk through how to link to your Excel data and bring those data fields into Word. When you’re in Word, goto the Mailings tab and select Start Mail Merge, where you’ll see the following dialog. The easiest selection will be the “Step by Step Mail Merge Wizard”, which will immediately open a new pane on the right side of your screen. Select “Letters” then move onto “Starting Document” at the bottom of the pane.

• • Mail Merge – Step 2 – Since you’ve already opened the Word template there’s no need to do anything other than allowing Word to proceed with the “Use Current Document” option.
• • Mail Merge – Step 3 – The next step is to select your Word document’s recipients, which is where you get Excel involved. Since we already have a data source, you’ll want to choose the Browse option for the “Use an existing list option”. As soon as you do you’ll be presented with a Select Data Source dialog almost identical to the one you saw when you pulled in the Access data.

 

 

Figure 476  

• • Once you’ve selected the data source you’re going to get another series of dialogs in which you’ll identify the worksheet and the data that you want Word to use.
• • Now Word is going to analyze your Excel data and return another dialog for you. This one might seem a bit more complicated, but we’ll walk through each step of the way. You’re going to see two almost identical dialog examples here; the first is what you would see if you had proceeded with copying the Pivot Table data to a new worksheet. The second is the Mail Merge proceeding with our Pivot Table data. As mentioned, there is a process to fix the fact that Excel adds rows that Word can’t read, and it’s important to know them, especially if you find yourself in a situation where you’re pulling information from a database that has less than friendly field names. The process we’ll walk through is called field mapping and it’s integral to being able to successfully perform Mail Merges.

 

Figure 477  

• • Mail Merge Recipients Overview - The first thing you’ll see is the top row with the Merge Field Names. Next you’ll see that the first row is blank; that’s because Excel adds a blank row when it creates a Pivot Table and Word is seeing that row as the header row. You can go ahead and uncheck it otherwise you’ll create a blank document for that row/record. The blank row is what’s causing Word to use the F3, F4, F5 designations for your column headers, but we’ll change that in a minute. You can also uncheck the second row which is the header row from the Pivot Table, since we’re going to manually adjust the column values anyway, otherwise you’ll create a document for the header row as well. Next you’ll see the data source listed on the left hand side of the dialog. Since it’s at the bottom of the dialog in its own pane anyway, you can just minimize that part so you can see more of the fields.

 

Figure 478  

• • Refine Recipient List – Since we already use a Pivot Table to narrow down our recipient list you don’t need to worry about these options unless you want to explore them on your own. You should note the Validate address option though. This will take you to a Microsoft website that offers Address Verification Services through companies like Stamps.com. If you aren’t sure that your customer addresses are correct you can use this option, otherwise proceed without it. One advantage to Address Verification is that it will append Zip+4’s, although it would probably be better to update the original data source as opposed to the Mail Merge list.

 

Figure 479  

• • Mail Merge Step 4 – Write your letter – You’ll see that you have several options here. We’re going to manually replace our template merge fields with ours, so we’re going to skip the Address block, Greeting line and Electronic postage options but we will walk through them. These options assume that you haven’t already written your letter, so Word’s giving you the opportunity to do so now.
• • Address Block – This allows you to insert a pre-formatted Address Block that Word will populate with your data instead of you having to input the various mergefields (recipient name, address, city, etc.). It comes with its own dialog that analyzes your data and gives you an example of how it will output.

 

Figure 480  

Greeting Line – Our letter already has a greeting line, but this is another way to automate one. Of note here is that Word gives you alternatives for invalid recipient names, like if a name is missing in your data. However, as we’ll discuss shortly regarding Data Integrity, this should not come as a surprise, and if it does then you might have bigger issues to worry about. This option should be a last resort, not a catch all.

 

Figure 481  

• • Electronic Postage – Similar to your Address Verification options from Step 3, if you have an electronic postage service you can configure it here. Otherwise you’ll be prompted to goto Microsoft.com where they’ll make some recommendations for you. If you find yourself doing a lot of mailing, these can be invaluable tools.
• • Mail Merge Step 4 (cont.) - More Items – The next thing we need to do is give Word the proper data for the document. You could leave the F3, F4, F5, etc. column headers, but then you’d have to remember which one corresponds to which data value. It’s much easier to use Word’s Field Mapping Wizard to do the work for us. So now it’s time to move on to Step 4 in the Mail Merge process.

 

• • In this case you want to select the “More items” option, which will launch a new dialog. When you get to this point it will be helpful to have the Excel workbook open so you can make sure that you can reference the fields directly as you map them. You select the “Match Fields” option and you can start mapping the data fields to your Mail Merge document. You can see how we’ve matched all of the available data fields to Words’ pre-defined fields. Simply select the Word field you want to match and select the appropriate Excel data field from the drop-down on the right of the Match Fields dialog. Note that Word’s Company field has been mapped to our Order Date field because of the Pivot Table filter we discussed earlier. That’s OK since we won’t be using the first rows of the Pivot Table data, which don’t contain relevant records. If you were using the non-Pivot Table data you wouldn’t need to worry about this. If you’ll be using the same Mail Merge data again, you should check the “Remember this matching…” option at the bottom of the Merge Fields dialog.

 

Figure 482  

 

• • Now it’s time to start inserting the mapped fields into your Word document, so you simply start clicking away on the fields that you want added. Since we mapped the merge fields to our data you can switch to the “Address Fields” option in the Insert Merge Field dialog, and you’ll see the same Word categories you saw when you matched the fields. Don’t worry about the merge field placement for now, because we need to manipulate that in a bit. Merge Fields are noted by the <<Merge Field>> tags before and after the field name. You can separate merge fields with punctuation as you’ll see in the final letter example.

 

Figure 483  

• • You’ll first need to switch back to the Database Fields option in order to add the Ship Name (Full Name) and Extended Price fields (F2 & F10 respectively), since Word didn’t have matching categories for those fields. However, in this case the F2 – Ship Name field will actually be labeled “Multiple Items”, since it’s a Filter indicator in the Pivot Table, just like “Order Date”.

 

Figure 484  

• • Now we’ll close the Insert Merge Field dialog and start making changes to our Word document, by moving the merge fields where we want them, and also deleting the place-holding fields that Microsoft put in for you when they created the template. Anytime you want to reuse one of the merge fields you can simply copy and paste it. In the following example you’ll see the completed customer letter with the merge fields highlighted for clarity (they won’t be highlighted in your completed document).

 

Figure 485  

• • Mail Merge Step 5 – Preview your letters - At this time you can make changes to the document like adding your signature, company logo, etc. For our purposes we’ll take it that the document is ready to go and proceed with the next step, which is to preview the letter with the actual Excel data merged into the document, so start clicking through the Recipients by clicking the >> button. You’ll immediately see that it’s not quite right!

 

Figure 486  

• • I don’t know that F3 will be pleased being referred to this way! So why did this happen? Remember the unnecessary data from the Pivot Table? We never told Word not to exclude it when we set up the linked data. Fortunately, Word lets you do that here, instead of taking quite a few steps backwards. Believe me, more than a few letters like this have been sent out to a fulfillment house and been processed, costing you money, so it’s always a good idea to preview your mailing. Every time. This leads us to a discussion of data integrity, which is paramount for any business’ communications with customers.

Data Integrity – It seems fairly logical to assume that you need to have accurate customer mailing/shipping information, but for whatever reason many companies stop there and don’t give a second thought to things like customer names. If you want to impress your customers, it’s not just the quality of the services that you provide, but the quality of communications as well. Here are some all too common examples of data that has been entered into a company system differently.

 

Figure 487  

• • How do you think Mr. Thompson will feel receiving a personalized letter to “Dear N/A:”? And you’ll probably get a nasty call from Mr. Bell. Before you start laughing, this kind of data is all over the place. When business competition is so fierce, many people overlook something that is so seemingly simple, but it happens all the time. Before you start a Mail Merge, or any mass customer communication for that matter, please check your data source. The litmus test is would you want to receive a letter from your business? If not, then you’ve got some work to do. Fortunately, that’s why we spent some time earlier in the course detailing how to address certain data issues, like Parsing and Concatenating data if your current data isn’t flexible enough to personalize your communication. The use of the PROPER function is also a big one because many database systems convert all entries to UPPERCASE, or many data-entry types simply prefer to enter everything that way, since you can type faster if you never have to shift cases. You need to be able to catch things like this before you create your first letter. If you go to the effort to create a beautifully worded letter and skip these bits, not even Shakespeare’s words could make up for failing to properly communicate.
• • Continuing Step 5 – Preview your Letters - Now that the importance of Data Integrity has been addressed and you know how to fix it, we can continue with previewing your letters. The first thing we need to do is get rid of the erroneous records that Word brought in from our Pivot Table. All you need to do is click the “Exclude this recipient” and Word will ignore it. Once you’ve gone through your data then you can proceed to the next step. Normally all you’ll need to do is first verify your data’s accuracy in Excel, then eliminate the ghost records created between Word and the Pivot Table and proceed, although it’s generally a good idea to review the first ten or so letters just to make sure that they do what you want with different data. Here’s what you should see once you get past the erroneous merge records.

 

Figure 488  

• • Field Switches – Did you notice that Word brought over the straight value data, and not the formatting, even though it was formatted in Excel? To fix this you need to use something called Field Switches, which allow you to apply the formatting directly in Word. Before you think “hey, I could just add a $ before the merge field and be done with it”, you could, but what about the comma to separate hundreds and thousands? Field Switches will take this into account. The first thing is to hit ALT+F9 to toggle the MERGEFIELD view. It will look like a lot of gibberish, but once you take a closer look at it you’ll see a definite logic there.

 

Figure 489  

• • Something that might be interested is that you can see where Word created its own Mergefield for the date, which was inserted through Insert, Text, Date & Time, so this is Word’s internal structure and Microsoft gives you the ability to change it. If you were to change the “MMMM d, yyyy” format you would see the change as soon as you toggle the Mergefield view (Alt+F9) back off. IF you recall in our earlier discussion about using the TEXT function in Excel to convert numeric entries, field switches are very similar, and you can use them to format Dates, Phone Numbers, Zip Codes, Social Security numbers, etc., some of which are detailed below.
• • Currency - {MERGEFIELD number \# $####,0.0}
• • Phone Number - {MERGEFIELD phone \# ###’-’###’-’####}
• • Date-Time - {MERGEFIELD date \@ “MMMM d, yyyy”}
• • Now that you see what the Currency Mergefield looks like we’ll go ahead and make that change in the Word document, which you’ll see here.
• • If you toggle Mergefield view again, you’ll now see the new formatting applied.

 

 

Figure 490  

• • Mail Merge Step 6 – Complete the Merge - Now that’s done, and you can finish previewing your letters. But wait, there’s one more thing you can do! You can actually edit individual letters depending on any special notes you might want to make to certain customers. As you’ve seen Mail Merge allows you to personalize what’s normally called “Form Letters”. You can take it a step farther with custom messages, like “Bob, thanks for the interest in our new widget. I sent some samples out to you this morning.” or “Betty, it was so nice to see you at the convention last week!”

 

Figure 491  

• • Edit Individual Letters – There is a justifiable argument that if you’re going to take the time to edit individual letters through Word that you might as well do it by hand, but there is a certain caveat. If you are creating Mail Merge Letters, then it’s quite possible that you’ll create Mail Merge Envelopes as well. If you pull a letter out of the stack in order to personalize it, then you need to make sure that you put the letter back in exactly the right order otherwise you risk sending the wrong personalized letter to the wrong recipient(s). Talk about embarrassing, and it does happen!
• • If you decide on individual personalization, just follow the Wizard, indicating if you want to personalize all of your letters, the current letter, or a range. Word will open your selection(s) in a new document, where you can make your changes and then print. Note that this will disconnect you from your Excel data source, and the comments about Letters and Envelopes still applies.

 

Figure 492  

 

• • Ready to Print! – After all that you’re finally ready to print your letters. You can print to a local printer, PDF, or other source. If you choose that route, then you can follow the dialogs, although many times you’ll choose to send the entire print, fold, and stuff and mail job out to a mail house that specializes that. If you choose to do that you must also include the Excel data source, otherwise all the mail house will have is your letter with a bunch of dead links. This has caused more than one mailing to go out late. Most mail houses will have the most up-to-date software versions, but you should always check beforehand. If added a company logo or special fonts, you will also need to make sure that you include those.

 

Figure 493  

Finally, you should be aware that you are by no means limited to creating customer correspondence with Mail Merge. You can use any list-based data in Mail Merge, like nametags for an event, recipes, asset tags for corporate equipment, etc. And you’re not limited to creating letters; you can also create E-Mails, Envelopes, Labels and Directories.

 

• • Pivot Table Report – When you choose this format Excel isn’t going to create a Pivot Table for you. Instead, as you saw in the Pivot Table lesson, Excel will analyze the Access query data and prepare the Pivot Table dialog and Field List for you. It’s up to you to create the Pivot Table from there, which you’ll see next where we’ve summarized our order information by First Quarter sales only and used the Tabular Form layout.

 

Figure 494  

• • There is only one problem to using a Pivot Table as a Mail Merge source and that’s the blank rows that Excel inserts above the Pivot Table. Word won’t read the blank rows, so when your data comes over it won’t have any row headers, just ambiguous field labels like F3, F4, etc., but there are workarounds that are important to know, so we’re going to go that route. You could also just take the Pivot Table data only and copy it into another worksheet. The next step is to move onto Word to create the Mail Merge document and get it ready to receive your Excel data.

 

 

Figure 495  

Create your Word Document

Before you can create a Mail Merge you have to have a Word document to populate with your customer information. In this case we’ll be using the “Notice of Valued Customer Status” letter from the Office Online Template Gallery. You can access it through Word via File, New, Letters, Business Letters, Billing and Order Letters, but you can certainly choose any template or letter of your own. The first things you’ll see are the form fields that the template designers added for you. Note that these aren’t Mail Merge fields, but form fields that we’re going to replace.

Unit Summary: Lesson 12 – External Data Sources & Mail Merge

• • In this lesson you learned How to retrieve data in Excel from other data sources. While we certainly couldn’t cover every external data application you got a good idea of where you can look and how to do it. Once you get the hang of it it’s remarkably uncomplicated, provided the data source is readily accessible. Note that not all Internet data is formatted to be readily retrieved.
• • We moved onto importing external data directly into Excel, where it will be converted to an Excel Table, or choosing to import it as a Pivot Table in the case that your external data doesn’t meet your needs and requires further manipulation.
• • Next we walked through the six steps to creating a Mail Merge document in Microsoft Word, beginning with choosing a template from the Word Template Gallery, actually linking the document to our Excel data, then adjusting it based on the way that Word reads the Excel data since Pivot Table and Excel Table based data will be read differently.
• • We reinforced some of the Data integrity issues that we’ve discussed throughout the course and showed some examples of data gone bad and the responses you might expect to get from customers if they saw it (especially if it was addressed to them!)
• • Finally we looked at MERGEFIELDS to format Excel data that had reverted to its lowest numeric values when Word merged it into your document.

Review Questions – Lesson 12 – External Data Sources & Mail Merge

1. What are some of the external data sources that you can use to pull data into Excel?

a. __________________________________________________

b. __________________________________________________

c. __________________________________________________

d. __________________________________________________

2. What options do you have when returning external data to Excel?

a. __________________________________________________

b. __________________________________________________

3. What does Mail Merge let you do?

a. __________________________________________________

4. Is a Mail Merge limited to letters that you print?

a. __________________________________________________

5. When creating a Mail Merge document do you have to use a Template document?

a. __________________________________________________

6. Why is important to make sure that your customer data is verified before creating a Mail Merge?

a. __________________________________________________

7. What are some other Mail Merge uses outside of Customer Communication?

a. __________________________________________________

Lesson Assignment – Lesson 12 – External Data Sources & Mail Merge

• • Your assignment is to review the Excel workbook and Word documents provided with this lesson. Walk through the Mail Merge steps to become familiar with them.
• • Create some Excel data of your own and explore the different types of merges you can create, be it E-mails, Envelopes, Labels or a Directory.
• • Finally, if you have Access work on retrieving data in Excel from the Northwind 2007 sample database. If you don’t have Access, then try to retrieve some data from any available source, whether it’s a company data warehouse, or even the Internet.