Mail Merge with Personalised HyperlinksI recently had a piece of work to do for a client that appeared on the surface to be a simple task. Just mail merging a document using a standard excel spreadsheet. What I thought would be a quick job turned into something much longer when I discovered there were hyperlinks in the spreadsheet that needed including in the merged document.

Now, this is itself isn’t a problem, if all we wanted to do is display the full hyperlink in the final document. In this case, the merge would work, but the link itself would be transformed to plain text rather than a clickable link – annoying, but not the end of the world. This problem was one step more complicated than that! I wanted to display the same ‘link text’ (e.g. the words ‘click here’) on every merged document, but have a different link address (URL) for each document. There was a good reason for this, the letters were going out to individuals, directing them to their own personal page on a website, so we needed this level of personalisation and because the links were so long and ugly, the option to just display the the full link address on each page wasn’t possible.

There’s a great little workaround for this issue by adding a hyperlink field around the mail merge field and here’s how to do it.

Firstly, set up your mail merge document as usual, adding merge fields the way that you would normally (Reminder: Mailings > Start Mail Merge > Select Recipients > Add merge field) Make sure that the link text that you want already appears in the body of your letter. In the example below we are using the words ‘click here’. You can also see a sample of our Excel data, with the name of the recipient and their personalised web link.
Merge_Hyperlinks2Merge_Hyperlinks1

When you are ready to insert your hyperlink fields, highlight the text that you want to use as your link text (in this case ‘click here’) and apply a hyperlink using the first URL from your list of data (Note: this can actually be any URL you choose, real or made up).

Press ALT+F9 to open the field codes, and you will see your link now displayed like this: { HYPERLINK “http://yourlinkhere.com” }

Merge_Hyperlinks3

 

You now need to change this code so that your merge field for the URL replaces the URL displaying currently. Do this by placing your cursor after the word ‘HYPERLINK’ and deleting everything up to the closing bracket. Now insert your merge field in the usual way by clicking Mailings tab > insert merge field > your merge field So for example if your merge field is “Page URL”, you need to change the code to look like this:

{ HYPERLINK { MERGEFIELD PageURL }}

Merge_Hyperlinks4

 

Once you have inserted your merge field, you can then merge your document in the usual way.

Select Finish & Merge > Edit individual documents. The resulting document will have individual hyperlink fields and you should see that the URL for each is personalised.

Merge_Hyperlinks5

Toggle the field codes again by pressing ALT + F9

Then update all hyperlinks in the document by pressing CTRL+A followed by F9

Your hyperlinks should now be updated and your document is ready to be printed. You can check your hyperlinks by clicking on them or hovering over them to display the link address.

Merge_Hyperlinks6

14 COMMENTS

    • Hi Lynn, this article is looking at mail merging in Microsoft Word, I think the process is slightly different if merging into an email. Which email client are you using? Perhaps I can make that a subject for a future article.

      • Is there any update for e-mail merging fails as first merged data in every each hyperlink adsress . I am using outlook2007 client

  1. Thanks for the very helpful post. I’m trying to set up a template email that incorporates individualized links like the article demonstrates. However, when I save my document with the mergefields and open it later, the hyperlinked mergefields are all set to the first URL I have in my spreadsheet/data source. It seems I would have to redo the hyperlinked mergefield trick you explain every time I wanted to use my template.

    Is there a way around this?

    • Hi Michael – did you ever find a way round the link reverting when you save it? I’m having the same problem and can’t find a solutions.

      Thanks

  2. The CtrlA and F9 is a step missing on other directions thanks. I am able to get clickable links but they are the same for all records.

    Using Word Excel and Outlook 2007. All other data in the merge changes for each record. Just the link stays at the first value.

    Thanks for any help.

  3. I’m creating bios for a couple hundred people and one of the fields is their email. I want the email to be a hyperlink in the word bio (I don’t want to “click here”). The email is a hyperlink in the excel document but when I merge it isn’t a hyperlink. How do I do this?

LEAVE A REPLY


*