Skip to main content

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.







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
Unknown said…
I can share another effective and easy to use Lotus Notes(NSF) mail file merge software provide by SysInfoTools Software. This software easy merge your NSF File with in second.

Visit here for more information: SysInfoTools NSF Merge

Best Regards
Unknown said…
NSF merge tool to merge number of NSF files without any duplicate data. This tool has many advanced features and functionalities through which you can easily merge multiple NSF files.
Learn more details about this tool, here:- http://www.nsfsecurityremover.com/nsf-merge.html
Anonymous said…
OpenNTF requires creating a LotusScript agent with the Domino Designer client which is not plausible for a non-technical user Non-technical users must download Print Merge Reporter (IBM Notes PMR tool) to perform mail merges to e-mail messages from IBM Notes without using any code or programming. Many IBM locations are using the IBM Notes PMR tool including IBM Madison Ave, IBM San Jose, IBM Boulder, IBM Haifa, IBM Cleveland and IBM Bethesda.



YouTube: Mail merge Excel to IBM Notes



More info: Print Merge Reporter for IBM Notes
Anonymous said…
OpenNTF requires creating a LotusScript agent with the Domino Designer client which is not plausible for a non-technical user Non-technical users must download Print Merge Reporter (IBM Notes PMR tool) to perform mail merges to e-mail messages from IBM Notes without using any code or programming. Many IBM locations are using the IBM Notes PMR tool including IBM Madison Ave, IBM San Jose, IBM Boulder, IBM Haifa, IBM Cleveland and IBM Bethesda.



YouTube: Mail merge Excel to IBM Notes



More info: Print Merge Reporter for IBM Notes
Apoorv said…
worked like a charm!!!! the new version is a bit simpler to use but your instructions were spot on!! Many thanks for making my new year emailing task easier.

Regards,
Apoorv
tempmail said…
Wow, this article really brings back memories of my early professional days when Lotus Notes was the go-to tool for so many businesses. Mail merge was such a lifesaver when it came to sending out personalized emails to a large group of recipients. It's fascinating to see how technology has evolved since then, but the basics of efficient communication remain timeless. Looking forward to more posts like this!

Popular posts from this blog

How to Change Your Notification Options for New Lotus Notes Mail in version 8.x

Don't worry, I'm not patronizing you (my readers), I just decided to re-document this for one of our internal users and thought you might want to be able to use it in your own user documentation. WHAT IS THIS DOCUMENT ABOUT? Some people who don't get a lot of mail, like to be notified when such an event occurs. Notification can be; via a sound via a pop-up box via the system tray (where the computer clock is) The pop up box looks like this; Other people, who like myself, get too much mail would rather not be notified. The aim of this document is to tell you how (and where) to turn these options on and off. CHANGING YOUR SETTINGS To change your settings from the Notes 8.x client; On the Menu, click File , then Preferences... On the left hand side , click on the little plus sign to the left of Mail to expand the options. Click on the option marked Sending and Receiving . In the middle section, under receiving, you can control your notifications. If you untick the box mark

How to Create an Auto-Response Mail Message in Lotus Notes 8.5.3+

Why would you do this? Suppose that you have an externally accessible generic email address for your company; support@mycompany.com or info@mycompany.com. You might expose this to the web and allow people to send messages to you. Setting up an auto-response email will tell the senders that their message reached its destination and that it will be dealt with accordingly.  It's also good practice to include links to FAQs or other useful information. Why 8.5.3 The techniques we'll be using here work in older versions of Notes but some of the options seem to have moved around in 8.5.3.  I figured it was a good time to show you where they've moved to. The Procedure Start Domino Designer and open the Mail file to be modified.  A really quick way to do this is to right-click on the application tab and choose "Open in Designer". In the Left hand panel of designer, expand Code and then double-click Agents.  A new window should appear. Click the action