How to Do a Mail Merge to Email using Lotus Notes

Why do one?

In today's "green" world, it makes much better sense to send out emails than letters but you still want to personalize them. Sadly, by itself Lotus Notes doesn't support mail merge to email. Of course, we know that outlook does (but then it lets anyone and anything send emails for you - even when you don't want them to).

So, how to do it in Notes?

OpenNTF

The first port of call is OpenNTF (http://www.openntf.org/). This place is full of great things but most of them are really badly documented. Still, these guys give things away for free and they develop in their spare time, so we should be grateful for what we get.

There's a great little project there called MailMerge Excel to Notes. Go there, click on releases and download the ZIP file.

Getting to the Code

The installation is tricky though I've noted that since I asked the author about the install, it's been updated (so maybe these steps are less necessary).

Unzip the files to somewhere on your hard drive (eg: C:\temp). The version I downloaded had three files;

• MailMerge-CreateMerge.lss
• MailMerge-SendMerge.lss
• Lotus Notes Mail Merge Function.docx

The LSS files are not viewable in Notepad (not sure if this is a problem with those particular files) and I couldn't find any means of importing them into an agent. If you attach them to a mail message in Lotus Notes though, you can right-mouse click on them and choose VIEW.

From there, you can copy and paste them.

I copied and pasted both LSS files into text files for easy reference.

Creating the Agents

1. Open your mailbox in Lotus Domino Designer

2. Expand Code and double-click on Agents.

3. Click the Button marked New Agent

4. Give the Agent a Name, (Either Mail Merge\Send Mail Merge or Mail Merge\Create Mail Merge depending on which agent you're putting in). You're going to have to do this twice anyway.

5 . The new agent will start with a bit of comment code in it. Just highlight and delete it

6. Copy and paste the entire code of the relevant LSS file out of notes viewer (or notepad). Don't worry, the routines should all find their correct places.

7. Depending on how things go, you might end up with errors (I obviously did). These aren't code errors but are either related to the way that the Notes viewer displays things or the way it pasted from Notepad.

To fix these errors, follow the little red circles with crosses in them.

Wherever you find a line which ends in &_ the notes client expects a line immediately below it. My paste had blank lines - so I just removed them all.

8. When the Agent is free of errors, press Ctrl+S to save. If it saves ok, you can close it and repeat steps 2 - 8 for the other LSS file.

That's it. You're done with the Installation.

Preparing your Spreadsheet

The spreadsheet is simply and excel spreadsheet with TO SUBJECT FIRSTNAME and whatever other columns you want to merge. Note that I left off the CC column (it still works but it's probably better to include one).

Save your spreadsheet as an Excel File (eg: C:\temp\Merge.xls)

Preparing the Email

Next, we go into Lotus Notes and write an email. Don't bother with the TO, CC or SUBJECT because they'll be overwritten.

Just write your email and if you want to include any fields from your spreadsheet, just add them in square brackets.

Save your new email as a draft and close it.

Generating the Merge

Go into the drafts area of notes and make sure that you've got the right draft selected. Then choose Actions, Mail Merge and then Create Mail Merge.

You'll be prompted to select the file which contains the mail merge data. Browse for the excel file you saved earlier.

You'll get a nice little warning screen (if you forget the CC) and you'll get an ID for your mail merge. Make sure that you write this number down somewhere.

Click Ok to Continue.

Hopefully you'll get a message that says that you encountered 0 errors.

Your drafts area will also get a bit busier because there'll be more documents in it.

Sending the Messages

I'm guessing that you could send the messages individually if you wanted but it's best to simply select all of the documents in the draft area. You can do a Ctrl+A and even select those which aren't in the merge (they won't be sent).

From the menu, select Actions, Mail Merge and then Send Mail Merge.

You'll be prompted for the ID number that you wrote down earlier.

When you click Ok, your emails will be sent.

Final Bits

Just a reminder, before you send to a large group of people, make sure that you do a test first and make sure that you remove any test text from your email and spreadsheet - we all know about the "Dear Rich B@st@rd" letters that one particular UK bank sent out years ago.

Thank You

Finally, a really big thank you to David Turner and Sacha Chua for developing such a worthwhile project. IBM ... this should really be a feature in the default Notes Mail Template.

BTW: I used Version 1.0 of the Mail Merge utility, Notes 8.5.2 and Office 2003 to do this.