** 17-Nov-2016 - Project seeking new owner **
David will unfortunately no longer provide any further development of these scripts.
Through Notes 7,8,9 and MS Excel 03/07/10/13, there are no reported Defects but at least two Feature Requests worth working on.
If you are interested in adopting this project, please contact David or the OpenNTF adminstrators.
Latest Release and Documentation (1.4.1)
Download Latest Project Release: MailMerge_1.4.1.zip
Download Latest Project Release Source: MailMerge_1.4_src.zip
Download Latest Installation Guide: Mail Merge in Lotus Notes - Installation 1.4.ppt
Download Latest User Guide: Mail Merge in Lotus Notes - User Guide 1.4.ppt
Release 1.4.1 - 24 July 2013 - Fixed package: v1.4 agent may run with v1.3 code (defect) -- Check version in first dialog.
Release 1.4 - 04 July 2013 - Enhancements: MIME format (request, request), multiple-recipients (request), new-lines (request), worksheet name (request). Fixes: batch numbers (defect) and forwarding (defect) -- Thanks everyone for the feedback.
Release 1.3.1 - 21 Nov 2011 - Copy Delivery Options (incl. 'Return Receipt') from template (request) ; fix for residual process (defect).
Release 1.3 - 31 Oct 2011 - Dynamic 'Reply To' support (request) ; fix for possible attachments defect ; warnings for missing attachments ; multiple attachments in collapsed section ; support more than 65536 records ; improved dialogs ; revised docs.
Release 1.2 - 29 Sep 2011 - Dynamic 'Attachments' support (request) ; 'BCC' support ; and improved error handling.
Release 1.1 - 27 May 2011 - Third agent to send only selected draft emails ; Import Export project compliance.
Release 1.0 - 17 May 2011 - Original release.
About Mail Merge 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
See the usage guide for more details, examples and screenshots
1. Example spreadsheet with column headers
2. Example Notes Email Draft Template with text tokens to replace with spreadsheet data
3. Example Generated Notes Email Draft from first data row, showing overwritten email field values, replaced tokens, and attachments
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. (discussion)
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. (discussion)
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. (discussion)
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.