Idea Transcript
Mail Merge in Microsoft Word with Personalised Hyperlinks October 19, 2017
Steph Middleton-Foster
Office Skills, Popular, Word
23
Updated October 2017 Following a number of enquiries and comments on this particular topic, we’ve refreshed and updated this article with information relevant to recent versions of Microsoft Office. I 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. In addition, we wanted to merge in the email address of their account manager, and make it clickable so that they can email their advisor with any problems. There’s a great little workaround for creating dynamic links in a mail merge by adding a hyperlink field around the mail merge field; and here’s how to do it.
Step 1: Setup your mail merge 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) Now here is the important bit. If you want the full email address or URL as shown in your source data to show up in your merged document, then simply insert the mergefield in the usual way. However, if you want to place a link behind some standard text (i.e. “click here” as in this example) 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.
Step 2: Create your hyperlink fields 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 key CTRL+K to 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). Do the same for your email field by highlighting the “email” merge field in your body text, press CTRL+K to open the hyperlink wizard then make sure you choose link type “email address” before typing any email address in the email address field.
What you’ll then see is that your email address field has changed to a clickable link email address.
Step 3: Create your dynamic links Now comes the wizardry we need to create your dynamic links. Press ALT+F9 to toggle your field codes, and you will see your link now displayed like this: { HYPERLINK “http://yourlinkhere.com” }
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’. Delete everything between the inverted commas and place your cursor there.
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 put your cursor between the inverted commas after the word HYPERLINK and insert the mergefield “Page URL”. The code will change to look like this: { HYPERLINK “{ MERGEFIELD PageURL }”} Your finished document will look something like this…
Once you have inserted your merge fields and are happy with them, toggle the field codes again by pressing ALT + F9 You should notice that your links have merged to match the first row of data in your database. If you preview the document and hover over your links at this stage, it may look like the merge has worked, but worry not, this is ok. We will update the fields in the next step.
Step 4: Finish your merge You can now proceed to finish your mail merge in the usual way. Select Finish & Merge > Edit individual documents. The resulting document will have individual hyperlink fields and if you should see that the URL for each is personalised.
There’s now one final step you have to do which is important. You need to update all hyperlinks in the document. Do this by pressing CTRL+A to highlight the entire document, 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.
mail merge, microsoft word, personalised hyperlinks, Word tips
Steph Middleton-Foster http://www.outhouseuk.com Technical expert for VA Professional Magazine, Steph Middleton-Foster is an award winning virtual assistant and Managing Director of Outhouse-UK Ltd, a leading UK virtual assistant service based in Worcestershire.
Related Posts
How to change the shape of autoshapes and images in Microsoft Word
Create custom style sets for your client documents July 22, 2014
February 7, 2014
Use mail merge to create personalised invitations in Publisher February 9, 2017
23 . Leave new Jonathan This works when creating individual documents, but can it work for sending emails? Thank you for your help Reply
Steph Middleton Yes it works for emails too. The main thing to remember is to set the hyperlink field around your merge field when setting up your document as without this, any hyperlinks are converted to plain text when merged to email. Reply
Chris I don’t think it does Reply
Lynn I am not seeing this happen with email – when I merge, it merges the first hyperlink to all emails. Reply
Steph Middleton-Foster 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. Reply
Can Inam Is there any update for e-mail merging fails as first merged data in every each hyperlink adsress . I am using outlook2007 client Reply
sue this is perfect, just what I was looking for, thanks for sharing Reply
Steph Middleton-Foster Glad that you found it useful Sue! Reply
Derek Irvin I was really excited to find this article. But, how do I get it to merge with an email? Reply
Michael 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? Reply
Chloe 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 Reply
Doug 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. Reply
Akis Have you found a way of doing that directly inside an email marketing tool where you want to mailmerge personalized links? Reply
Jen 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? Reply
smita It didn’t work for me – followed the steps as per the article. all the records got the first record’s value. did i miss any step? Reply
Steph Middleton-Foster Hi Smita, did you follow the steps right to the end? Including press “Finish & Merge” then highlight all and update values by pressing CRTL+A+F9? That’s the secret to making this formula work. I’ve just refreshed to article to make those steps clearer. Reply
Sara Same as smita I’m still struggling with this. Can create the hyperlink but each email returns the value of the first URL in my spreadsheet. Any ideas on how to make it dynamic? Any help would be greatly appreciated! Reply
Steph Middleton-Foster Hi Sara, can you tell me what version of Microsoft Word you are using? Reply
Steph Middleton-Foster Hi Sara, did you follow the steps right to the end? Including press “Finish & Merge” then highlight all and update values by pressing CRTL+A+F9? That’s the secret to making this formula work. I’ve just refreshed to article to make those steps clearer Reply
James Whale Thanks for this article – very helpful.
However, I echo what other commenters have said – it doesn’t work if merging to email. One simply ends up with a very
long Word document whose pages, although each contains different, personalised dynamic links, cannot then be emailed out to individual recipients, as it’s now just one long document. :/ Reply
Steph Middleton-Foster Hi James, Sorry to hear you are struggling, I know a few people have had the same issue. Can you tell me what version of Microsoft Office you are using? I plan to look into this and write an updated article for this topic but there are different solutions for the different versions Thanks! Steph Reply
cat THANK YOU! This is PERFECT! Note: you cannot “shortcut” the method by copy/pasting your code and editing it. You need to follow all the steps for each different URL you need. And the final CTRL-A, F9 is CRITICAL (I somehow read over that part!). THANK YOU for posting this! Reply
Steph Middleton-Foster You’re welcome Cat, glad it was helpful! And yes you are right, the important part is following the process in full and not trying to shortcut it. Reply