Thursday, May 19, 2011

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.







9 comments:

Dave said...
This comment has been removed by the author.
Gavin Bollard said...

Hi David,

I'm having a look at the new version. It turns out that my text editor (ConTEXT) won't read LSS files properly.

Notepad works well, and it means that I don't have all of those extra steps.

I'll have a think about the installation procedure and contribute some ideas as soon as I get a chance.

Dave said...

Hi Gavin,

Glad you liked it. I tried to think of everything that would make it more functional, robust and easier to use, and figured the end product was far too good to keep all to myself, so put it back into the open-source community! :)

Thanks also for the detailed install instructions. I did improve the documentation this morning and provided a new release, but it seems you encountered way more hassles that I was expecting; it wasn't supposed to be that hard!

I've opened a discussion on OpenNTF on how I could improve the installation process. So feel free to leave any suggestions there.

- David

Sacha Chua said...

That's awesome! Thanks for building on my work and sharing your improvements!

Dave said...

Thanks Gavin,

I've included a revised version of your presentation as the authoritative installation and usage guide for the project.

Release 1.1 of the MailMerge Excel to Notes project is much easier to install as the agents are now pre-configured in a bundled NTF file; it only takes one drag-drop or copy-paste.

MailMerge Excel to Notes

Regards,
David Turner

LxJones said...

Hi,
I was trying on Lotus notes 8.5 and found more simple way to do this.
1- Open up the Mailmerge.nsf file in your lotus notes mail client.
2- Go to "view" -> Agent and check all three files are there.
3- Copy the First file
4- Go to your mailbox tab, View-> Agent, here you should see a list of existing agents/applications...Paste your file and rename it. Do the same for remaining two files
That's it... you are done.
You can than create a draft, make ready our excel and follow Gavin's step.

Thanks

Allen said...

I just wanted to confirm that LxJones method of installation works.

Anonymous said...

it worked for me. only problems I am having is when I have to CC mutiple people. It only sends a CC to teh first person I list on the CC

Anonymous said...

Use a pipe character | between addresses in the To, CC or BCC col of the spreadsheet, not comma.
Had to choose a character which is not valid in an email address!
Same between files in an Attachments col.
-David