Mail merges are like Marmite. You either love them or hate them.
Fortunately, I’m in the Loving Them camp.
I think it’s because it’s one of those tasks that’s not often done, so when you need to do one, you have to start all over again and remind yourself how to do it and as there are quite a few steps, it can be challenging to remember them all, and in order.
So, for those that need to re-learn the process each time, Microsoft has been very helpful in setting up a Step-by-Step Mail Merge Wizard.
That’s all well and good, but having done more mail merges in my time than I care to recall, there are a couple of pointers and helpful hints that you don’t get told and here are a couple that I came across as I did them over and over again.
In my Corporate days within the Private Equity world, each investor (of which there were many) required regular updates about their investments. Each quarter, a personalised letter and statement itemising all their information would be issued, but often additional letters and statements when extra funding was required or distributed to them would need to be sent. The easiest and quickest way was via a mail merge.
True fact: I had an issue with a mail merge back in 2004 when I couldn’t bring through a comma in figures over a thousand from Excel to Word. Nothing I did worked. Something so simple eluded me. I knew it should be possible, but I just couldn’t fathom it out. But I needed it to work. So, I contacted Microsoft directly and a lovely chap called Dave Chow, based in Canada, and I worked for five days trying to figure it out. In the end, an option was unchecked. I needed to have ‘Confirm file format conversion on open’ checked. When you’re in Word, this can be found in ‘File – Options – Advanced – General’. I still have the email exchange paperwork to this day.
I’m not going to tell you how to do a mail merge here, as it’ll turn into a book, but I am going to give you a couple of helpful hints that served me well when I was doing them week in week out (or day in day out, as it often was).
One of the most important elements within a mail merge is the data source. It is worth spending time ensuring the data source is correct and tidy. What do I mean by this? You need to ensure that:
- all the information is correct,
- there are no typos,
- the addresses look as they should when the merge is done for labels or letterheads; and
- the information is set out how you want it to appear in your merged document.
You don’t want to go to through the merged document page by page removing things such as commas in addresses, or extra spaces or lines. It is best to do this at source so, if you use the data source again for another task, it’s all ready as it should be. If you spot an error, it’s very easy to be lazy and change it on the final merged document, but it also should be changed at source too.
In my opinion, the best place to store your data is in an Excel spreadsheet. The information can be stored in a variety of ways and can also grow and grow.
If you have lots of columns with information within them, ensure you also have a ‘header’ for each column. If you don’t have one, add one. This ensures that the column of information is identified and has a clear, identifiable merge field.
If you merged this data source as it is, the middle column will not be identified as ‘Column C’. If you’re not on the ball, you’ll get yourself into a right pickle. 😉
Whilst we’re on the data source… if you’ve inherited either the original template (unmerged document) or a data source, ensure that both the headers in the data source and the unmerged document merge fields match. If they don’t, you’ll get an error message for each one (and if you’re doing labels, you’ll get an error for each label!). The best way to overcome this, if you forget to check, is to close the document, don’t save it, and start again after you’ve matched the headers and merged fields.
This can be a challenge as sometimes the dates merge across in whole numbers rather than in the date format you need.
If you need to merge across dates in dd/mm/yyyy format as opposed to dd.mm.yyyy format, you’ll need to adjust the unmerged Word document to bring the date across as you need it.
So, to do this in your unmerged Word document:
- Press Alt + F9 (this brings up the full merge field)
- In the date field (see Dob below), you need to add: \@”dd/MM/yyyy”
- Make sure the M is capital M (for month), otherwise it’ll think it’s m for minute
- Press Alt + F9 to go back to the usual looking fields.
When doing a mail merge, I always save the documents I’m using as:
unmerged name of document
data source name of document
merged name of document
This will help identify each and, if you need to re-do, it will be a lot quicker to start again.
Another thing to note is that any change you make to the data source, you will need to re-save and then re-link it to the unmerged document otherwise your changes won’t pull through.
If you often find yourself in this boat, save all your files to the desktop as it’ll be a lot quicker to find them there. Then, when you’re done, move them to the correct file location.
There are more helpful hints I could add, but it seems that Microsoft has excelled itself and ironed out the issues.
Also, as Microsoft has developed, there is now an option to merge directly to Outlook, so you can send personalised emails using mail merge. I’ve not actually used this method as I’ve not yet had the need, but I’m looking forward to the challenge of finding out how, as and when the need arises, but I’m sure the process will be just like doing a mail merge in Word.
There are a number of platforms that you can use to personalise emails these days (Mailchimp, Mailerlite to name a couple), but I firmly believe that Word / Outlook and Excel still have their place and should be used a lot more. I mean, who doesn’t love a personalised bit of correspondence?
If you need a set of letters, labels or statements sent to your clients that require individualising (or even if you want to put your Christmas Card addresses on labels rather than writing them each year!), and you’re not sure where to start, give me a shout and we’ll set up a data source and unmerged documents that can be utilised time and time again.