MailMerge Excel to Notes

LotusScript agents to allow a user to create, preview and send personalised mail-merge emails through Lotus Notes to a set of recipients based on Excel spreadsheet data.

This function was developed to address business and environmental concerns in sending paper-based, personalised mass mail, and to overcome several practical limitations in using Microsoft Office’s built-in mail merge email function with Notes email clients; such as: requiring DAMO, Word and Outlook to be installed, and only supporting attachment-based or plaintext emails.

With authors permission, the original concept and code for this project was provided by Sacha Chua. Her original script, blogged in July 2009, cracked the core problem in reading Excel spreadsheet data, through OLE in LotusScript, into a Memo template, to create a mail-merge function in Notes. This was later enhanced over several re-releases and was contributed to OpenNTF Alliance under an Apache 2.0 licence (Mail Merge (1.0.0)). Massive thanks to Sacha for all her work. You can visit her blog at: http://livinganawesomelife.com

Sacha's script I have rewritten with a number of design and useability improvements to make it more capable and suitable for business use:

• an object-oriented approach to managing the Memo and Spreadsheet objects to assist code readability and maintainability
• use of the Notes Common 'MemoCopy' class to generate copies of email messages and fields in a Domino-standard way (including mail Delivery Options since 1.3.1)
• validation of input template and spreadsheet tokens to avoid user error
• a preliminary count of spreadsheet rows (and draft emails which will be created!)
• an integrated batching function for generating, previewing and sending mail
• environment variables to recall most recently used values
• many more dialogs for user confirmation and feedback and informative error handling
• ability to embed dynamic attachments in generated emails (since 1.2)
• ability to convert to MIME format for external recipients (e.g. those using Outlook) (since 1.4)

The agents are also bundled in an NSF file for easy installation by a Domino Dev/Admin.

I thought I'd give this back to the open-source Domino developer community to use and let others make suggestions and further contributions as needed. This is my first contribution to OpenNTF, so if you have any bug reports or suggestions for new features, feel free to let me know and I'll do what I can. Please also leave a rating or review if you and your users like this project!

Domino Developer/Admin Installation

There are two ways of installing these agents into your selected Notes database or template: 1.a. Install this project via the OpenNTF Import and Export for Designer plug-in, OR 1.b. Manually open the bundled MailMerge.nsf file in Domino Designer and in the Navigator, under 'Code' -> 'Agents', copy and paste the agents into your database. Three agents will be installed:

• Mail Merge\Create Mail Merge
• Mail Merge\Send Mail Merge
• Mail Merge\Send Selected Mail Merge (optional)

2. Ensure you save and sign the agents so the end-user can execute them. See installation guide for more details and screenshots

User Mail Merge process

1. Arrange the mail merge data in an Excel Spreadsheet, just as if you were setting up for a MS Word-based Mail Merge. Include columns for To, Cc, Bcc, Reply To, Subject and Attachments, and any other variable data you want to include in your email (e.g. Surname). 2. Draft a new email in Notes to use as a mail merge template, containing tokens in UPPERCASE and square brackets (e.g. [SURNAME]), to be replaced with the corresponding spreadsheet data. Save it as a draft. 3. Select the draft email in Notes and run the 'Create Mail Merge' Notes agent. 4. When presented with a dialog, browse for the spreadsheet file. 5. Confirm to create a batch of draft emails using the draft template and spreadsheet data. 6. Manually preview and validate resulting draft emails. 7. Run the 'Send Mail Merge' Notes agent entering a batch number. See the usage guide for more details, examples and screenshots

Dependencies and Limitations

• Notes Client and Microsoft Excel must be installed on users Windows machine. Tested with Lotus Notes 7.x/8.5.x/9 Client on Windows XP/7, with Microsoft Excel 2003/7/10/13 OLE. Does not support Linux/Mac clients at this stage due to the OLE dependency, but it shouldn't be hard to add CSV support.
• The agents are designed to be installed in a classic Domino 8.5 Mail template (mail85.ntf) or Domino 7 Mail template (mail7.ntf), or similar.
• The Use "Common" import line in the 'Create Mail Merge' agent code may only work in English-language Domino mail databases. For other languages check your respective Script Library name. For example, change the line to: Use "Common_ja-JP" if your template is Japanese language to get it to compile.

About the Author

David Turner, BSc Hons, is a Senior Analyst/Programmer specialising in enterprise Java and Web technology. His passion for solving problems has fostered a proficiency in a wide range of software programming languages and development environments. He enjoys applying research and innovative web technology to engineering projects, with a focus on useability, maintainability, and maximising potential re-use by following industry-recognised design patterns and open standards.