OddThinking

A blog for odd things and odd thoughts.

Mail-Merge: A War Story


The Problem

It’s true: We were sending over 1500 emails. But, it wasn’t spam, honestly! We had a prior relationship with each of the recipients, and they had opted-in to the product announcement mailing list. It was time to survey them.

Depending on which random web-site that you believe, scientific research shows that a personalised salutation (e.g. Dear Fred, rather than Dear Sir/Madam) either increases response rates by at least 5% or almost makes no difference. I suspect the real answer is somewhere in between, and decided to personalise the greeting.

While the URL pointing to the on-line survey included a coded, personally-identifying field – including their name, we weren’t betraying their trust, honestly! We informed them that their identity was being encoded. It not only allowed me to personalise the greeting, but to get a picture of responses by subgroup (e.g. by country, or by version of the product).

The Solution to the Problem

So, how do you send 1500 slightly-different emails? Microsoft Word includes mail-merge software that will talk directly to Microsoft Outlook to send the emails, reading the details from a database or spreadsheet.

I ran a little test against four email addresses, and the mail-merge worked fine. All four emails went out in a few seconds.

The Problem with the Solution to the Problem

However, the URL appeared as a long string of text, rather than as a link that could be clicked. The recipient would have to cut-and-paste the result into a browser address bar, rather than just clicking on the link to start the survey. How much would that little bit of extra effort affect the response rate? I shuddered to think.

The Solution to the Problem with the Solution to the Problem.

I spent some time trying to make the URL actually appear as a link with Microsoft Word’s mail-merge but I was unsuccessful. I discussed it with my colleagues, and the suggestion was made to move from HTML-formatted mail to plain-text formatted mail. This step is counter-intuitive (URLs should work better in HTML!) but made sense, because rather than sending the wrong HTML (i.e. missing the anchor tag), I was leaving it to the email client to recognise the URL, and to automatically mark it up. Many email clients (including Microsoft Outlook, in use by many of the product’s user-base) will do this automatically.

The New Problem

I steadied myself, and started the mail-merge.

Up popped a dialog box:

Dialog Box

Nice one, Microsoft Office developers! No, seriously! I think making this check was probably very successful in slowing the spread of a number of email-worms. I mean, don’t get me wrong. The dialog box could be better in several key aspects:

  • Don’t offer to approve for 1 minute, 2 minutes, 5 minutes, etc. Does anyone care if they only time-limit period is 10 minutes? That would remove another unnecessary choice from the user.
  • On the other hand, I do want to say “This is an application I use every day. You can trust it permanently.”
  • It could contain some information about the process trying to read my email address. Am I being asked to approve Microsoft Word or some new amazing worm that waits for me to start an mail merge and then latches on to Outlook in the confusion!
  • It was also a little confusing in context – Word should not have been trying to read my email addresses stored in Outlook, but just send some email to the addresses I had already provided in Excel. Maybe it was a new amazing worm!

But no matter – I selected Yes.

Up popped another dialog box:

Dialog Box

Well, this dialog box is more accurate, at least. It described exactly the situation I was expecting. However, this security is less powerful. Any self-respecting email-based worm would carry its own SMTP-stack around with it, and avoid this security point.

Notice the progress bar. It deliberately forces the user to wait for five seconds before the Yes button appears:

Dialog Box

I clicked Yes, and to my horror, another dialog box popped up – just like the previous one.

It expected me to click once for every email I wanted to send – at a maximum rate of one every five seconds. Let’s see, that’s 1500 * 5 / 3600… over 2 hours of repeated clicking, even at the optimal rate!

I started to panic, and came up with some wild ideas to try (with the assistance of my bemused colleagues.)

  • Redemption, a separate library to avoid the restrictions? Useful if I was building my own worm (and couldn’t be bothered implementing my own SMTP-stack) but no good here. Office didn’t use this library.

  • A hack that offers better security pop-ups from Outlook and permanent approval? Installed it but coudn’t get it to have any effect. (A hint to the product manager: Don’t let the developers write the user documentation, without assistance!)

  • Software version differences between the machine I ran the original test on and machine I was running the final mail-merge? I did the test on machine in my study at home, and the actual merge on my machine at work. I raced home to re-try it, but it was now prompting for the same security approval there!

I wondered what I had changed since the test run… and could only think of one thing.

The New Problem = the Solution to the Problem with the Solution to the Problem.

I changed the format of the email back from plain-text to HTML, and re-launched the mail-merge. I can hear you scoffing at my superstition; what could possibly relevant about the format of the plain-text email that would affect the security of the system?

Well may you scoff, but it sent out all the emails in HTML format, without complaint, at a rate of about 100 per minute.

That’s right. The Office developers have put tighter security around sending plain-text than they have sending HTML email!

Crazy.

p.s. Clicking No on the dialog box aborts the sending of one piece of email, only. There’s no other way to gracefully quit from the middle of a mail-merge. I had to kill the process, or click past the dialog 1500 times!


Comments

  1. Great story.

    There’s so much here that is indistinguishable from malice that it’s hard to know where to begin.

    As far as I’m concerned, Word has no mail merge.

  2. The thing about trusting applications that you use every day, is that if they’ve been hijacked by a macro-virus, then suddenly that application isn’t nearly as trusty as it used to be. I really only want to trust apps to send email while I’m present, and when I approve. But then I’m not trying to spam survey my customers :)

    Also, are you sure you didn’t manage to get the hack working for HTML emails? It seems possible that their hack isn’t complete, but MS’s security is (however unlikely that sounds).

    Lastly, you weren’t able to beautify the hyperlink in the HTML? I thought you’d try to wind up with a “Take the survey” link, rather than just displaying the URL.

  3. Unbelieveable.

    For future reference, AutoHotKey lets you automate mouse clicks. I have a few neat little scripts, including one that enables and disables the second monitor for my laptop.

    http://www.autohotkey.com/

  4. Found the real solution here. wow what a kludge! took me hours to find this. Microsoft is lame but OpenOffice doesn’t do it either so…

    Microsoft Knowledge Base article 912679

  5. Nice find, Brendan!

    This article provides two solutions to the “The Problem with the Solution to the Problem” – that is putting in merged hyperlink addresses. One of the solutions is to include a macro, which is indeed a kluge. The other is a more reasonable approach (including the mail-merge field inside a hyperlink field) but is almost impossible to guess – especially using the wizard.

    The other problem remains – that a mail-merge of plain text email addresses requires an acknowledgement of every single email sent.

Leave a comment

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <br> <code> <del datetime=""> <dd> <dl> <dt> <em> <i> <ins datetime="" cite=""> <li> <ol> <p> <q cite=""> <strike> <strong> <sub> <sup> <u> <ul>