First and foremost, I am not saying this is a good use of Mail Merge, nor am I saying that this is anything you should do to make a website. This is only an example of Mail Merge being used for something other than writing form letters, something I did, which set me on the road to scripting languages. Think of it as an experiment in thinking outside the box, a saying I dislike because of its overuse.
For those of you that don’t know what Mail Merge is, it’s a component of Microsoft Word (and other word processing software), and in the words of my brother after explaining it to him, “so, it’s an auto copy and paste.” I prefer to think of it as an advance search and replace, but as long as you get the idea. And how does this work you might ask? Well, basically, you mark places in a document that are automatically filled in by Mail Merge, which gets the data to put in those places from a spreadsheet.
Note: I was using it to make includes (as in SSI, server side includes, .shtml or the likes), but you could make entire pages in this manner.
Say you want to generate some html that contains a linked image (an image that’s clickable), you could do something like this:
<a href="category name.html" title="Something descriptive about this link, which will show up when hovered over"><img src="top-level image folder name/category name/image name.jpg" /></a>
That’s easy enough when doing it one or ten times, but how about 100 or 1,000 or more? Mail Merge to the rescue. So, without further ado:
- Create and save your spreadsheet with required fields. Note: The spreadsheet and the Word doc should be put in a folder that will not be moved as Word links to the sheet and gets upset if you move things around. Also, to make things easier, get rid of any unused sheets (in the spreadsheet).
- category_name img_name
- cat_name_1 img_name_1.jpg
- cat_name_2 img_name_2.jpg
- cat_name_3 img_name_3.jpg
- cat_name_4 img_name_4.jpg
- cat_name_5 img_name_5.jpg
- cat_name_6 img_name_6.jpg
- cat_name_7 img_name_7.jpg
- Create and save your Word document with which you will run Mail Merge. To make things easier put it in the same folder as the spreadsheet.
- <a href="category name.html" title="Click Here To Go To category name"><img src="top-level image folder name/category name/image name.jpg" /></a>
- Start the Mail Merge Wizard (Word 2007, it’s under Mailings):
- Select document type: Letters. Next
- Select starting document: Use the current document. Next
- Select recipients: Use an existing list. Browse (browse and select your spreadsheet – make sure 1st row of data contains column headers is checked, Mail Merge recipients – ok). Next
- Write your letter:
- Highlight category name in “category name.html.” Click more items. Insert merge field, insert: Database Fields, Highlight category_name, Insert, close.
- Highlight category name in “Click Here To Go To category name.” Click more items. Insert merge field, insert: Database Fields, Highlight category_name, Insert, close.
- Highlight category name in top-level image folder name/category name/image name.jpg. Do insert as above. Close.
- Highlight image name.jpg in top-level image folder name/category name/image name.jpg. Click more items. Insert merge field, insert: Database Fields, Highlight img_name, Insert, close.
Your document should look like this now:
<a href="«category_name».html" title="Click Here To Go To «category_name»"><img src="top-level image folder name/«category_name»/«img_name»" /></a>
- Next, preview your letters. You can click on the double arrows to cycle through your letters and you should see:
- <a href="cat_name_1.html" title="Click Here To Go To cat_name_1"><img src="top-level image folder name/cat_name_1/img_name_1.jpg" /></a>
- <a href="cat_name_2.html" title="Click Here To Go To cat_name_2"><img src="top-level image folder name/cat_name_2/img_name_2.jpg" /></a>
- <a href="cat_name_3.html" title="Click Here To Go To cat_name_3"><img src="top-level image folder name/cat_name_3/img_name_3.jpg" /></a>
Etc., etc., etc.
- When you’re satisfied that it’s working as it should, Next, Complete the merge.
You will now be able to cycle through your generated mailings by using Mailings, Preview results. If the wrong info is showing, try clicking on preview results again.
Now that Mail Merge is set up, you can modify your spreadsheet (while the Word document is closed, and making sure to leave it where it is) by adding or changing data in fields. You can add or delete rows, but don’t change the number of columns. When opening your Mail Merge document again, it’ll ask you if you want to open a linked database. Just okay it and cycle on through. Now you can copy and paste your letters into html or a different spreadsheet or database.