Mail Merge using XSLT while sending mail in ASP.NET

Tweet

This post explains a way sending mails to multiple people by merge the content with some information.

Background:

The requirement is - In an organization the support team developing a website which is used for logging support tickets by the end user. Once the user logs the request, the application required to send email to the analysts by replacing ticket information such as ticket id, description in the mail.

For example let us consider the below mail:

Subject: New Ticket is been submitted by Krishnan Narayanan. Ticket Id - STP101.


Dear Muniswamy Thirumalai,

Krishnan Narayanan has submitted a new request on STP application. As per SLA, the ticket required the solution in 3 Days.

The Ticket information is:

Ticket Id: STP101

Purchase Order printing alignments are not proper

Hi. The Purchase Order printing is not properly aligned in the latest version published yesterday. As the customer expecting a perfect print, please rectify the alignment properly as soon as possible. Thanks, Thirumalai.

For more information on the ticket, click the url.

Please review the ticket and response as on time.
This mail is auto generated. Please do not respond to this email.

Regards,
STP SupportTeam

Considering the mail, there are two different types of data replaced such as the ticket information (Ticket Id, Title, Description) and the analyst information (Analyst Name, Analyst emailed).

Implementation Details:

XSLT is used for implementing the mail merge. Before going for actual implementation details, we will understand what XSLT is.

Extensible Stylesheet Language Transformations abbreviated as XSLT, used for transformation of one format (such as XML) to another format (such as HTML, XML, CSV, text). The XSLT is an XML script which has nodes for replacing the data from XML.

To use XSLT, we need to have three main inputs.

1. Input XML – This is an input to the transformation engine which holds the data for replacement in the XSLT script. It can be an XML file name or an XML reader.
   In our example, the ticket information such as Ticket Id, Requestor Name, Title, Description, Submitted Date and SLA Time as considered in input XML.
   
2. Output XML – This is the output file from the transformation engine. The format of the output file depends on the specification mentioned in the XSLT. The output can be an XML file name or text stream used for storing output after transformation.
   
3. Argument List – In case if some information is not present in the input XML, but required in the XSLT for data replacement. We can send the information as argument list.
   In our example, the Support Team information such as Support Analyst Name, Email Id etc., are not possible to include in input XML as the mail required sending different analyst. So each analyst will have different information and to be replaced before mail send.
   

For more information, look at the below url:
http://msdn.microsoft.com/en-us/library/windows/desktop/ms759096(v=vs.85).aspx

Below are some important XSLT nodes:

• The <xsl:output> node is to inform the transformation engine what format of output file is required.
  

  <xsl:output method ="xml" media-type="text/html" encoding="iso-8859-1" />

• The <xsl:value> node is for replacing the value which comes from the input XML.
  

  <xsl:value-of select="TicketId"/>

  Here the TicketId is a column name mentioned in the XML node.
  
• As mentioned earlier if there are some values are not present in the XML, the value can be passed as parameter. So to use parameters, we must first define what are the parameter name used in the XSLT
  

  <xsl:param name="FirstName"></xsl:param>
  <xsl:param name="LastName"></xsl:param>

  The FirstName is the analyst name we are planning to pass from C# Code. To use the parameter value in the XSLT script, we required to use $ prefixed with the parameter name.
  

  <xsl:value-of select="$LastName"/>
  <xsl:value-of select="$FirstName"/>

• Use &#160; for placing a space between two replaceable value. Normally the XSLT will not give space if the two nodes are adjacent each other (such as LastName FirstName), if we need a space we required to use as below.
  

  <xsl:text>&#160;</xsl:text>

There are lots of other nodes for defining XSLT, but as this post is not for explaining how to use XSLT we can go ahead for implementation.

Preparing for Implementation:

We are going to send the mail as HTML script and the HTML script will be created from XSLT transformation by getting the ticket information and analyst information as input. So before going for doing XSLT file, will have define how the output HTML script must be.

For Example let us consider the below HTML script for the mail defined in the background section –

<html>
  <body>
 <p>
   <b>
  Dear Muniswamy Thirumalai,
   </b>
 </p>
 <p>
   Krishnan Narayanan has submitted a new request on STP application. As per SLA, the ticket required the solution in 3 Days.
 </p>
 <p>
   The Ticket information is:
   <br/ >
   <div style="background:#BDBDBD; width:100%;">
  <b>Ticket Id: </b>STP101
  <br / >
  <br / >
  Purchase Order printing alignments are not proper
  <br / >
  <br / >
  Hi. The Purchase Order printing is not properly aligned in the latest version published yesterday. As the customer expecting a perfect print, please rectify the alignment properly as soon as possible. Thanks, Thirumalai.
  <br / >
   </div>
   <br / >
   For more information on the ticket, click the url. Please review the ticket and response as on time.
   <br / >
   <br / >
   This mail is auto generated. Please do not respond to this email.
   <br / ><br / >
   Regards,
   <br / >
   SupportTeam
 </p>
  </body>
</html>

The ticket information required to be created as XML script and given as input to the transformation engine. Let us have XML script as defined below.

<?xml version="1.0" encoding="utf-8" ?>
<Ticket>
  <TicketId>STP101</TicketId>
  <RequestorFirstNameName>Narayanan</RequestorFirstNameName>
  <RequestorLastName>Krishnan</RequestorLastName>
  <SubmittedDate>24-Dec-2011</SubmittedDate>
  <SLATime>3 Days</SLATime>
  <RequestTitle>Purchase Order printing alignments are not proper</RequestTitle>
  <RequestDescription>Hi. The Purchase Order printing is not properly aligned in the latest version published yesterday. As the customer expecting a perfect print, please rectify the alignment properly as soon as possible. Thanks, Thirumalai.</RequestDescription>
  <TicketUrl>https://companyname.com/support/tickets.aspx?tid=STP101</TicketUrl>
</Ticket>

As the analyst information as passed as argument list, we don’t required it as XML script. But I have it as XML script to define the list of analyst information (for making easy).

<?xml version="1.0" encoding="utf-8" ?>
<SupportTeam>
  <SupportPerson>
    <FirstName>Thirumalai</FirstName>
    <LastName>Muniswamy</LastName>
    <EMailId>firstname.secondname@gmail.com</EMailId>
  </SupportPerson>
  <SupportPerson>
    <FirstName>Mohammed</FirstName>
    <LastName>Moinuddin</LastName>
    <EMailId>firstname.secondname1@gmail.com</EMailId>
  </SupportPerson>
</SupportTeam>

Now let us start defining XSLT script.

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">

  <!-- Defining the output to be in HTML script -->
  <xsl:output method ="xml" media-type="text/html" encoding="iso-8859-1" />

  <!-- Defining parameter First Name and Last Name. The same parameter name must be used while passing the value from .NET code-->
  <xsl:param name="FirstName"></xsl:param> 
  <xsl:param name="LastName"></xsl:param>
  
  <!-- Taking each Ticket Node in a loop -->
  <xsl:template match="Ticket">
    <html>
      <body>
        <p>
          <b>
            <!-- Replacing the <Last Name><Space><First Name> values. Here the Last Name and First Name are parameters passed from .NET Code. -->
            Dear <xsl:value-of select="$LastName"/><xsl:text> </xsl:text><xsl:value-of select="$FirstName"/>,
          </b>
        </p>
        <p>
          <!-- Replacing <Requestor Last Name> <Space> <Requestor First Name>. Here the Requestor Last Name, First Name are comming from Ticket Node (input XML) -->
          <xsl:value-of select="RequestorLastName"/><xsl:text> </xsl:text><xsl:value-of select="RequestorFirstNameName"/> has submitted a new request on STP application. As per SLA, the ticket required the solution in <xsl:value-of select="SLATime" />.
        </p>
        <p>
          The Ticket information is:
          <br / >
          <!-- The XSLT contains the same HTML script we are using in normal HTML script -->
          <div style="background:#BDBDBD; width:100%;">
            <b>Ticket Id: </b>
            <xsl:value-of select="TicketId"/>
            <br / >
            <br / >
            <xsl:value-of select="RequestTitle"/>
            <br / >
            <br / >
            <xsl:value-of select="RequestDescription"/>
            <br / >
          </div>
          <br / >
          <!-- If we use node except defined as per XSLT and to replace the values with in "", use "{Name of XMLCol}". -->
          For more information on the ticket, click the <a href="{TicketUrl}">url</a>. Please review the ticket and response as on time.
          <br / >
          <br / >
          This mail is auto generated. Please do not respond to this email.
          <br / ><br / >
          Regards,
          <br / >
          SupportTeam
        </p>
      </body>
    </html>
  </xsl:template>
</xsl:stylesheet>

The XSLT defined above is the body of the mail. But in our case, the Subject line also has ticket information values. So we required another XSLT for transforming subject info.

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
  <xsl:output method ="text" media-type="text/html" encoding="iso-8859-1" />
  <xsl:template match="Ticket">
    New Ticket is been submitted by <xsl:value-of select="RequestorLastName"/><xsl:text>& #160;</xsl:text><xsl:value-of select="RequestorFirstNameName"/>. Ticket Id <xsl:value-of select="TicketId" />.
  </xsl:template>
</xsl:stylesheet>

Now we are finally going for C# source code. The body XSLT required analyst information as argument for transformation, so we required XSLT transformation for each analyst by looping the analyst XML node. But considering the subject XSLT, we have only ticket information replaced. So it can run at initial stage before the analyst loop is starting. So the list of functional flow would be

1. Run the Transformation for Subject XSLT for getting subject information.
2. Open the Analyst XML file and run a loop for getting each analyst node.
   
   • Run the Transformation for Body XSLT by passing analyst First Name, Last Name for getting body HTML script.
   • Send an SMTP mail for the analyst mail id by passing the subject and body information.
3. End.

Below is the screen shot of a mail received by an analyst.

Download the working copy of the source code in C# here and VB here.