We are putting into place a video repository deletion rule, for the first run we need to alert the 170 video owners that their videos fall into this policy and will be deleted.

Here’s how I went about notify those users with a email merge

Start off by creating the data set of Mail Merge Recipients in Excel

How my data set looks:

Now start a Mail Merge in Word and load in the above data set as the recipients

Step 4 of the Step-by-Step Mail Merge Wizard is to write your e-mail message

This is how mine looks once the Paragraph button has been pressed

& the Field codes are showing, press Alt + F9 to toggle to this code

What does the above all mean!! – I’ll try and explain as easily as I can

Dear GivenName is displaying the GivenName of the users ID taken from Azure AD using the following method this has been add in Word by simply using Insert Merge Field then the GivenName column.

I’m next displaying the Owner ID at this point and Setting an ID1 flag again that field, to create the extra { } you must use Ctrl + F9 – Do NOT type them, your merge will not work

I’m then comparing the Owner column with an offset by one on Owner_Compare column, this is so the merge knows when I’ve come to the end of entries for one particular user. By an offset of 1 I mean this (copy the full Owner column addresses, insert a blank line at A2 across and paste in what is in your clipboard to Owner_Compare column then delete the row A1) so you have something like this:

Then on the main code block line To do this use Ctrl + F9 to insert { } then type in IF and Insert Merge Field from the menu of MERGEFIELD Owner then = and insert more { } braces and insert MERGEFIELD Owner_Compare from the menu. Then insert move braces { } and put in { SET ID2 “{MERGEFIELD Full_URL}” }

Now the really complex part on how to get clickable text in the email which will take you to a URL – It might look as simple as writing { IF ID1 = ID2 “” “{ HYPERLINK { MERGEFIELD Full_URL }{ SET HL1 “”} \ * MERGEFORMAT } but it isnt.

**If you do not want a clickable hyperlink, then just replace the above with { IF ID1 = ID2 “” “{ MERGEFIELD Full_URL } and now jump to the end of this post**

To get the HYPERLINK you need to do the following :

In Word Select Insert tab & Find Quick Parts / Field…

Select Hyperlink

This will paste in the following to your merge document { HYPERLINK \* MERGEFORMAT } now press Alt + F9 and you will see Error! Hyperlink reference not valid. double click on the word Hyperlink to highlight it, now use Insert Merge Field to select the text of the link, in my case this is Name now you will see something like

Which is the current recipients file inserted in the text, delete all the text around this, so you are then left with

Now press Alt + F9 & insert the merge field of the URL so you have something which looks like this

Now copy that into the code block

**Be careful after editing your mail merge several times you might notice that this then shows as something like this, at this stage your whole code is messed up and you will need to paste back in the above formatted code, otherwise all your links will be incorrect locations based on one of your users and not necessarily the user you are on.**

I noticed the above happened on every single load of Word !!! But do not worry, if you followed the above you’ve wont see the issue I had. The explanation is below.

Completely unacceptable, so I did so more searching and figured out that you need to add a Bookmark to the HYPERLINK to stop the static URLs, so it looks something like the below, the part you care about is that it has a { SET HL2 “” } before the \ *. This should stop you from having to edit the Mail Merge document each time before you run a merge. Please note, I’ve also noticed that there seems to be a limit of Bookmarks allowed – its around 44 – 48 I think. I need 50 ! Typical..

Okay now copy your IF block and change the IF to a NEXTIF (I’ve noticed people not doing this and some versions of Word then ignore your first record). Now paste that down the page as many times as you like or as many times as your computer can handle, you may notice your machine slowing down a this stage. I have it 50 times in my email merge – meaning it’ll display up to 50 entries per email, if there are that many available for that user.

Right now is the time to test your email merge to a small set of email addresses and check that the URLs been sent are correct, test again before the day comes that you send it to the full amount of people – and just before, do make sure that in Alt + F9 mode, it hasnt replaced the formatting of the url to a actual url.

One further part to note, I attempted to get to be sent from a alternative mailbox, I couldn’t because Office365 needed a licence on the alternative account, which it did not have.

Good luck – this took me 4 days to crack.