Word & Excel - Creating Standard Letters with Mail Merge

Well, I began writing the second piece to my last article about scripting, but I soon realized that I'm going to have to make it a three-part series to do it justice, and so I haven't had the time to finish it up. But I thought I'd write another post in the meantime. Have you ever had to write a "standard" letter (such as a confirmation letter or a bill to clients who are overdue)? Typically, it involves figuring out who to send the information to and then writing up all those letters. A simple technique you can use instead involves the use of Microsoft Word's "Mail Merge" feature. I'll walk you through a simple example.

To begin, you might want to start with a blank document. Once opened, go to "Tools | Letters and Mailings | Mail Merge", and choose "Letters" to continue on. The next step you could use templates or from an existing document (if you've already started to write the letter), but in this case we simply want to start with the "current document". In the next step, you are asked to select the recipients of the letter. If you feel comfortable selecting queries or tables from an ODBC connection, this is the most direct route. Otherwise, if you have a dB that allows you to download your information into Excel (or the information is already there), you can just select the Excel file ... or if you're writing to a set of clients about a new project, maybe you want to use "Outlook contacts" if you use Microsoft Outlook. We'll choose "Use an existing list" and then click the "Browse" link to find an Excel file. You should already have a list started*, and when you browse to that file you should select the sheet or named range with the contact information (and any other relevant information you want to include) from the "Tables" option that pops up. Typically, Word is pretty good at figuring out what information should be included.

The next step is to click the "Next" link at the bottom, and begin to write your letter. You'll write your letter as you normally would, but instead of writing any particular piece of information that is only associated with a particular person or company (like their account balance), you can choose to insert fields (either "standard" items, or you can be more flexible and click the "More items..." link) and precisely define what fields should be inserted where. Once you have completed the letter, you are then able to preview an example letter in the next step. If you need to make corrections, simply go back with the "Previous" link at the bottom.

The final step is to create the new document. If you want the option to edit particular letters for some reason, you can choose "Edit individual letters..." and it will create a new document with all the letters filled in. Alternatively, you can simply choose to "Print" the document and all will be printed out with the appropriate information filled in.

I hope this provided you with insights on how to use this feature. Good luck!

*NOTE: You should have a list in Excel with no extra columns and no blank rows. You should also include a "header" column that describes each column in the list, so that you can easily select which fields to insert when writing the letter. Finally, if you name the entire list (select the entire list in Excel and hit Ctrl-F3, then name it something like "Contact_Info"; see here for more information about naming ranges and cells), you can EASILY select it when Word prompts you which table to select.

Scripting Languages

Well, hello to everyone! I'm so sorry I haven't written in so long. The wedding was fantastic, but took up a lot of my time :) Then, once I got back I was thrown right in the mix of school activities again, so even though I haven't written I've been a busy man.

I just wanted to write a quick post about scripting languages. You may have heard the term "scripting" before, or maybe not. If you know VBA (which some of my other posts have touched on), then you know scripting. But I also thought it might be a nice chance to step away from MS Excel for a moment and see what other technologies you can use to help do your job more effectively.

Script languages, as defined by Wikipedia are:

computer programming languages initially designed for "scripting" the operations of a computer. Early script languages were often called batch languages or job control languages. A script is more usually interpreted than compiled, but not always.

You really don't have to know what that means. All it says is that these tools are meant to help you control your environment more powerfully than, say, Excel. You can do many (if not all) the things you can do with a regular programming language: loops, conditional execution, etc. Why do I think you might be interested in this? Let's say that every month you are performing some type of reconciliation, and the file you reconcile to comes in a text form, but not a normal delimited file (i.e., not csv, not tab-delimited, etc.). You can see that it is clearly structured, but not in a way that Excel can recognize. In the past, you may have used a program like ACL or IDEA to define "records" and that are then processed to give you a comma-delimited file or something similar. This is where scripting could save you time and money.

The languages that I'll refer you to are (in order of my personal preference):

1. Python
2. Perl
3. Ruby (never used, but heard it is very good)
   

You might even consider SQL a scripting language, but it is limited to databases whereas the 3 previously mentioned are ubiquitous. Also, as I mentioned before, VBA is a scripting language but it is limited 1) to the Microsoft environment and 2) Typically to Microsoft programs like Excel or Word (although you can use it elsewhere, I never have).

All 3 programs are open source (and therefore free as in beer and free as in speech), so you will never be limited to a vendor and you will never be constrained by a licensing cost or issue. And all 3 languages are EXTREMELY powerful. The task previously mentioned could easily be handled by anyone with at least a minimal proficiency in any of the three languages. After downloading (e.g., Python) you can write a program to open a text file and process every line in the file as follows:

f = open("file_name", "r")
for line in f:
  #process line
f.close()

This would indicate the filename was "file_name", the "r" indicates that you want to open the file for "r"eading, and the "f" is simply a variable. The "for line in f:" is a looping construct that reads every line in the file. The "#process line" would be replaced by some suitable code that would process each line (perhaps adding a total to the previous total, etc.). Finally, you close the file with f.close().

As you can see, with just a little more cursory knowledge, you can do some pretty powerful things fairly easily. I'll continue sometime next week with an additional discussion of Python, but I suggest that you check out the websites above if you are at all interested thus far. Don't be intimidated! It is fairly easy to get going, once you give it a shot. Good luck!