A common requirement for many web applications we develop is a function that allows users to generate
personalized batch emails. This does not pose any significant challenges if the extent of customization
is limited to a personalized greeting while the body of the message consists of a standard text. All we
need to do is to identify the individuals which should be contacted, loop through the returned records,
and send out the emails using the Send method of the System.Web.Mail.SmtpMail object. Somewhere
along this process we can add the personalized greeting, either at the point of data retrieval or while
we loop through the list of emails to be sent out.
For some web applications, however, the requirements go beyond the simple personalized greeting and
entail a more typical mail merge. Information from the database has to be merged into one or more
paragraph. In addition, users often need to be able to preview and in some cases modify the email
messages before sending out the emails.
We looked at a number of different solutions including third party software to create web-based email
merges. What we settled on in the end was using Xsl templates to transform the dataset that contains
the data to be merged, taking advantage of the fact that we can a) easily convert a dataset into
an the Xml representation and b) that we can fill a dataset from an Xml stream. The latter point
is important because we need to be able to present the merged data to the user for review and possible
adjustments.
We created this article based on some code we created for a real-life application. So, before we go
into any further details we need to establish the framework and clarify our set of assumptions and
requirements which guided the development of this solution.
The data for this mail merge is retrieve from two tables, the Project table and the Issue
table displayed below. We simplified and denormalized the data from the actual web application
for the purpose of this article:
 |
| Data Model diagram |
The Project table holds information about project funding request that were submitted by an
applicant. The submitted project proposals are reviewed and a project is then either approved,
meaning a project will be funded, or denied. Projects can also be tentatively approved pending the
resolution of one or more issue. The issue information is stored in the issues table.
After the review process notification emails are sent out and users should be able to generate all
emails in a single process merging the information entered about each project with the information
stored in the template corresponding to the entered project status (Approved, Denied, or Tentative).
Users will also be able to review the mail messages before sending out the emails and make changes
as needed.
 |
| Process Flow diagram |
The Process Flow diagram to the right outlines the tasks involved in merging the information from
the database into emails that can be sent out. The tasks can be grouped into two blocks. The tasks in
the first block handle the data retrieval and the data transformation and the tasks in the second block
deal with the actual mailing of the emails which includes the email review
In this article we are focusing on the tasks of the first block, retrieving the data and transforming
the data. The data display and the mailing of the emails is rather simple once the data has been
transformed.