• Anonymous
  • Login
  • Register



MailMerge Excel to Notes

Owners David Turner Project Creation May 17, 2011
Contributors Sacha Chua Last Release Nov 20, 2011
Downloads 5020Download Latest Release
Rating
(4 ratings)
Description Create and send personalised mail-merge emails through Notes from spreadsheet data


Latest Release and Documentation (1.3.1)

Download Latest Project Release:  MailMerge_1.3.1.zip

Download Latest Installation Guide:  Mail Merge in Lotus Notes - Installation 1.3.ppt

Download Latest User Guide:  Mail Merge in Lotus Notes - User Guide 1.3.ppt

 

Release Notes

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).

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

 

Example Screenshots

See the usage guide for more details, examples and screenshots

Example Spreadsheet

1. Example spreadsheet with column headers

 

Example Notes Email Template
2. Example Notes Email Draft Template with text tokens to replace with spreadsheet data

 

Example generated file
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 Excel must be installed on users machine. Tested with Lotus Notes 7.x/8.5.x Client on Windows XP/7, with Microsoft Excel 2003/7/10 OLE. (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.
 



Projects can have short and readable URLs, e.g. http://mobilecontrols.openntf.org.

You can request a short URL to your project by sending a mail to support at openntf dot org.
Owners are the people with maximal rights. For example they can create releases and add more contributors.

In order to define multiple owners use "," (comma) as separator.

Because of data privacy you cannot lookup other people's names but you have to type them in.
Enter the contributors or owners here who you want to receive notifications and questions by email.

In order to define multiple owners use "," (comma) as separator.

Because of data privacy you cannot lookup other people's names but you have to type them in.
In addition to the owners additional OpenNTF contributors have to be listed here. Contributors don't have the same access rights as owners, e.g. they cannot create releases.

In order to define multiple owners use "," (comma) as separator.

Because of data privacy you cannot lookup other people's names but you have to type them in.
Enter a short description of the project here (up to 100 characters).

The short description shows up in the project UI and in some of the views.
In this field you should enter a more detailed description of your project.

You can use the rich text editor for rich text formating. You can also enter HTML to embed objects, e.g. to embed a YouTube video or a screenshot of the project. In this case use '[' and ']' to mark the passthrough HTML as such.
A Catalog Release is for projects that are production ready and that are end-user "install friendly".

This property describes whether any release of this project has been added to the catalog.

Learn more about the catalog.
GitHub can be used as source control repository for OpenNTF projects. In this case the Issues from GitHub are used instead of the Defects from OpenNTF.

Contact ip-manager@openntf if you want to have a GitHub project created in the OpenNTF space.

Learn more about OpenNTF on GitHub