When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
Published: Monday, October 19, 1998

Marketing through Personalized Emailing


Introduction:
Since the advent of the computerized word processor and database, businesses have been using the two in unison to send out seemingly personalized form mail. Weíve all received such mail from a company, a letter or memo that has gone out to thousands of people, but maintains a personalized tone to it. Instead of starting out, "Dear Valued Customer," the letter begins with, "Dear Frank Smith." Other words, sentences, and phrases have a uniqueness to them as well, specific to the individual the letter has been sent to.

- continued -

Businesses have been using snail-mail form letters for many years, but are just beginning to apply the same logic to email. Imagine that you run a business that sells office supplies. By keeping a log of your purchasersí personal information (such as email address, name, location, business name, and products purchased), you will be able to send personalized emails to your customers. Imagine if you could alert your clientele to a sale on supplies they most often purchase, or to when you receive some new inventory youíve never stocked before.

In this article I am going to describe how to implement such a system using Active Server Pages and SQL Server. I recently had the opportunity to develop a system very similar to the one described above. In short, it is fairly straightforward and simple, and can be used to generate a very powerful marketing tool. This article is intended for those who are knowledgeable with both ASP and SQL. I will use the example of the office supply store described above.

This process, from an end userís standpoint, can be broken up into three parts: first, the contact information (if not already present) must be inserted into the database; second, the mailing list (if not already created) must be made; and lastly, the personalized email must be typed and sent. It will help to keep these three steps in mind as we begin developing our system.

The developer has a number of steps as well. To start off with, the contact table must be created and some kind of data entry method must be developed. Next, a mailing list table must be created, and a means for an employee to create a mailing list must be developed. Following that, a utility to type a form-based email must be implemented; finally a routine must be written to parse the email, making it personalized. Once the email has been personalized, it just needs to be sent to the appropriate contact(s). For the remainder of the article, I will step through these needed processes one at a time.

Step 1: Contact Table
For this system, letís assume that the contact table is defined as follows:

ContactID int   (PK)
FirstName varchar(50)
LastName varchar(50)
EmailAddress varchar(100)
CompanyID int   (FK)
InterestedInDesks bit
InterestedInFurniture bit
InterestedInPaperClips bit

CompanyID is a foreign key to a Company table. At our store, we keep track of the company a person is purchasing supplies for. If the person is buying supplies for personal use, we would simple create a new company using the customerís name and address for the appropriate company fields. Letís say that this Company table contains geographical location of the company, a list of all the products the company has ever purchased, and if the company belongs to our office supply storeís Preferred Customer Club. Finally, letís say that the "InterestedIn" fields were determined by a customer survey.

Step 2: Data Entry into the Contact Table
I do not wish to go into detail describing a data entry scheme for the contact table. This can be done quite simply with an Intranet page, or a VisualBASIC application (or straight through SQL, if youíd like). Choose whatever approach is best fitted for your company.

Step 3: Mailing List Table
We need to create a table that will store our mailing lists. For example, say that we want to create a mailing list that has contacts A, B, and C in it. It would be nice to be able to create this once, rather than having to specify the recipients each time we plan on sending a form-based email. A good approach is to first create a MailingList table, which will contain one row for each mailing list created in the system. Here is the definition for the MailingList table:

MailingListID int   (PK)
Name varchar(50)
CreatedBy int   (FK)
CreatedOn datetime
Description text

The Name is the name of the mailing list created. For example, one might want to call a mailing list, "First time Customers in December, 1998." Each time in the month of December a new customer was made, they would be added to this mailing list. The CreatedBy column references an Employee table of some sort, so you can keep track of who created the mailing list. The CreatedOn column contains the mailing list creation date, and the description is a text field that is a long explanation of the mailing list, in case the name doesnít fully describe the listís purpose.

The second table we will need will be a many-to-many table joining the MailingList table and the Contact table. Letís call this table MailingListContacts; it will be defined as follows:

ContactID int   (PK) (FK)
MailingListID int   (PK) (FK)
UpdatedBy int   (FK)
UpdatedOn datetime

This table has the ContactID and MailingListID as its primary key. It contains an UpdatedBy field that references the same Employee table that the MailingList CreatedBy column refers to. The UpdatedOn field is simply the date and time the field was created or updated.

With these tables in place, a user can create a mailing list and save it. We should now be able to create a user interface to enter contacts into a mailing list. Using ASP, I will show one of many possible ways to do this.

Step 4: The Mailing List User Interface
There are innumerous ways to create the user interface. I will use a web-based approach utilizing Active Server Pages. The system will be broken down into four pages: a main menu (MENU.htm), a screen to select what mailing list you want to edit (EDITSELECTION.asp), the screen users will actually assign various contacts to the current mailing list theyíve selected (UPDATEMAILINGLIST.asp), and finally the page to update the database, DBUPDATE.asp. Letís first examine the MENU.htm code:

MAIN.htm

<HTML>
<BODY>
      <P ALIGN=CENTER>
      <FONT SIZE=+2><B>Mailing List Menu</B></FONT>
      </P>
      <P>
      <A HREF="EDITSELECTION.asp">Edit an existing Mailing List</A>
      <BR>
      <A HREF="UPDATEMAILINGLIST.asp">Create a new Mailing List</A>
      <BR>
      <!-- You may want to add a link to a page where users can delete an existing mailing list -->

</BODY>
</HTML>


As you can easily see, this file just has two hyperlinks to the two ASP pages which actually do something. This page is very ugly, but it serves quite a simple purpose, so I did not spend an extraordinarily long time prettying it up. Letís look at the EDITSELECTION.asp file now. This fileís purpose is to show all of the current mailing lists to allow the user to select a particular one he or she wishes to edit.

EDITSELECTION.asp

<% Option Explicit %>
<!-- #include file="connection.asp"-->
<%
      Dim rsMailingLists, strSQL
      strSQL = "SELECT * FROM MailingList ORDER BY Name"
      Set rsMailingLists = Conn.Execute(strSQL)
%>

<HTML>
<BODY>
      <P ALIGN=CENTER>
      <FONT SIZE=+2><B>Select a Mailing List to Edit</B></FONT>
      </P>
      <P>
      <% Do while Not rsMailingLists.EOF %>
           <A HREF="UPDATEMAILINGLIST.asp?MailingListID=<%=rsMailingLists("MailingListID")%>">
                <%=rsMailingLists(ďNameĒ)%>
           </A>
           <BR>
           <BLOCKQUOTE>
                <%=rsMailingLists("Description")%>
           </BLOCKQUOTE>
           <P>
           <% rsMailingLists.MoveNext
      Loop %>
</BODY>
</HTML>



Again, this is not the most eye appealing HTML page, but it serves its purpose quite well. It lists all of the current mailing lists ordered by the name, and provides a hyperlink to the UPDATEMAILINGLIST.asp file. The <!--#include file="connection.asp"--> simply includes a file which makes the needed database connections and, if you wish, includes ADOVBS.inc.

The final file for our mailing list user interface is UPDATEMAILINGLIST.asp. This file is called when either a new mailing list is wanting to be created, or when an existing mailing list has already been created and is needing editing. If MailingListID is not passed through in the querystring then we know that we need to create a new mailing list. So, letís write this file piece-wise, starting with the code needed to determine if we need to create a new mailing list record or not:

UPDATEMAILINGLIST.asp

<% Option Explicit %>
<!--#include file="connection.asp"-->
<%
      Response.Expires = 0

      If Not IsNumeric(Request.QueryString("MailingListID")) then
           Response.Redirect "CreateNewMailingList.asp"
      End If

      'ASSERT: We are editing a current mailing list
      ...
%>


This will test to see if a mailing list has been created or not. If one has not (by determining if the querystring value MailingListID was passed in or not), the user is redirected to a file named CreateNewMailingList.asp. There they will enter the name and description of their mailing list, which will then call a file to insert the row into the MailingList table. That file responsible for inserting the row into the mailing list will get the newly added mailing list's identity value (MailingListID), and redirect the user back to UPDATEMAILINGLIST.asp, this time passing the MailingListID value through the querystring. (To see an example of obtaining the identity value from a newly inserted row, please refer to http://www.4GuysFromRolla.com/webtech.)

Letís continue with UPDATEMAILINGLIST.asp:

<%
      ' Code From above...

      Dim rsMailingList, strSQL, MailingListID

      MailingListID = Request.QueryStirng("MailingListID")

      'Get the Mailing List information for the current mailing list
      strSQL = "SELECT * FROM MailingList WHERE MailingListID = " & MailingListID
      Set rsMailingList = Conn.Execute(strSQL)


      ' Get the contact information for all the contacts who belong to this mailing list
      Dim rsContacts       strSQL = "SELECT ContactID, Name = LastName + ', ' + FirstName, EmailAddress FROM Contact C INNER JOIN MailingListContacts MLC ON C.ContactID = MLC.ContactID WHERE MLC.MailingListID = " & MailingListID
      Set rsContacts = Conn.Execute(strSQL)


      ' Get all of the possible contacts that can be added to this mailing list
      Dim rsPossibleContacts
      strSQL = "SELECT ContactID, Name = LastName + ', ' + FirstName FROM Contact WHERE ContactID NOT IN (SELECT ContactID FROM Contact C INNER JOIN MailingListContacts MLC ON C.ContactID = MLC.ContactID WHERE MLC.MailingListID = " & MailingListID & ")"
      Set rsPossibleContacts = Conn.Execute(strSQL)
%>


At this point we've created three recordsets. The first recordset, rsMailingList, contains a single row detailing the information for the current mailing list. The second recordset, rsContacts, is a recordset of the zero to many contacts who have currently been assigned to this mailing list. The final recordset, rsPossibleContacts, is a list of all the contacts who are currently not in the mailing list.

Now that we have this information, we need to display it to our users. We will need to show who is currently assigned to this mailing list, and a list of all who are not. We will also need to provide an "Add" button, to add an unlisted contact into the mailing list, and a remove button to remove a contact from the mailing list. Let us examine the code below to determine how we can do this (note: this is the same file, UPDATEMAILINGLIST.asp):

<HTML>
<SCRIPT LANGUAGE="JavaScript">
>!ó
      function AlterAction(strAction)
      // Purpose: change the action parameter
      {
           document.forms[0].DBAction.value = strAction;
      }

      function ValidateData()
      /* Purpose: to make sure that the user has selected an option before submitting */
      {
           if (document.forms[0].DBAction.value == "ADD")
                if (isNaN(parseInt(document.forms[0].selContact.value)))
                     // No value selected for selContact
                     return false;

           if (document.forms[0].DBAction.value == "REMOVE")
                if (isNaN(parseInt(document.forms[0].selMailingListContact.value)))
                     // No value selected for selContact
                     return false;

           return true; // A valid option has been selected
      }
// -->
</SCRIPT>
<BODY>
      <FORM METHOD="POST" ACTION="DBUpdate.asp?MailingListID=<%=MailingListID%>" ONSUBMIT="return ValidateData();">
           <INPUT TYPE=HIDDEN NAME=DBAction VALUE="">

           <TABLE WIDTH=100% BORDER=0>
                <TR>
                     <TD WIDTH=50% VALIGN=TOP>
                          <CENTER>
                          <B>Contacts:</B>
                          </CENTER>
                          <P>
                          <SELECT NAME="selContact" SIZE=1>
                          <% Do while not rsPossibleContacts.EOF %>
                               <OPTION VALUE="<%=rsPossibleContacts("ContactID")%>">
                                    <%=rsPossibleContacts("Name")%>
                               </OPTION>
                          <% rsPossibleContacts.MoveNext
                          Loop %>
                     </SELECT>
                     <P>
                     <INPUT TYPE=BUTTON VALUE="Add Contact" ONCLICK="AlterAction('ADD');document.forms[0].submit();">
                </TD>
                <TD WIDTH=* VALIGN=TOP>
                     <CENTER>
                     <B>Mailing List Contacts</B>
                     </CENTER>
                     <P>
                     <SELECT NAME="selMailingListContact" SIZE=1>
                     <% Do while not rsContacts.EOF %>
                          <OPTION VALUE="<%=rsContacts("ContactID")%>">
                               <%=rsContacts("Name")%>
                          </OPTION>
                     <% rsContacts.MoveNext
                     Loop %>
                     </SELECT>
                     <P>
                     <INPUT TYPE=BUTTON VALUE="Remove Contact" ONCLICK="AlterAction('REMOVE');document.forms[0].submit();">
                </TD>
           </TR>
      </TABLE>
      <P ALIGN=CENTER>
      <INPUT TYPE=BUTTON VALUE="Save Mailing List" ONCLICK="document.location.href='MENU.htm';">
      </P>
      </FORM>
</BODY>
</HTML>


This HTML, if you couldn't tell, will create two select boxes. The one on the left side of the page will be a list of all the potential contacts that could be added to the mailing list. The one on the right is a list of all the current contacts in the mailing list. To pretty this page up, you would probably want to add some conditionals to check for rsPossibleContacts and rsContacts end of file. You could then display a nice message informing your user that there are no contacts to choose from or no contacts in the mailing list.

Once the user clicks on Add or Remove, the form is submitted, calling the file DBUPDATE.asp. First, though, the action (a hidden variable) is set to let DBUPDATE.asp know what, exactly, it is suppose to do. Then another JavaScript function, ValidateData() is called, to determine if an option has indeed been selected or not. (One way to circumvent having to write the ValidateData() function is to use the HTML keyword SELECTED in the first OPTION tag of both drop-down listboxes. Let's now study the code for DBUPDATE.asp:

DBUPDATE.asp

<% Option Explicit %>
<!--#include file="connection.asp"-->
<%
      Dim ContactID, action, strSQL, MailingListID
      action = Request.form("DBAction")
      MailingListID = Request.QueryString("MailingListID")

      if action = "ADD" then
           ContactID = Request.QueryString("selContact")
           strSQL = "INSERT INTO MailingListContact (MailingListID, ContactID, UpdatedBy, UpdatedOn) SELECT " & MailingListID & "," & ContactID & "," & userID & ",getdate()"

           Conn.Execute(strSQL)

           Response.Redirect "UPDATEMAILINGLIST.asp?MailingListID = " & MailingListID
      Elseif action = "REMOVE" then
           ContactID = Request.QueryString("selMailingListContact")
           strSQL = "DELETE FROM MailingListContact WHERE MailingListID = " & MailingListID & " AND ContactID = " & ContactID

           Conn.Execute(strSQL)

           Response.Redirect "UPDATEMAILINGLIST.asp?MailingListID = " & MailingListID
      Else
           'An error!! I'll let you decide how to handle this.
      End If
%>


DBUpdate is a fairly simple file, just responding to one of two action requests. Once it has completed, it sends the user back to UPDATEMAILINGLIST.asp, passing the MailingListID back. The userID value symbolizes the current user of the system and their corresponding EmployeeID value. This can be implemented via cookies / Session variables, or through another process. A detailed discussion of this topic, however, is beyond the scope of this article.

So there we have the mailing list user interface. It is very rough around the edges, but it does get the job done. Through this interface, your office supply store employees will be able to create and edit mailing lists, adding and removing your numerous contacts. There is only one major function left from the userís standpoint, and that is to write and send a form-based email to a mailing list.

Step 5: A Form-Based Email Editor
To be able to create form-based emails, you will need to write a custom email editor. I would recommend using VisualBASIC, but you can do this with the Internet or Intranet as well. In my example, we will just use an HTML page, using a form TEXTAREA as the editor. In practice, I would recommend the use of the ActiveX textbox control, but a textarea will sufice. Let's look at what an example input might look like, and what the output should look like:

Say we want to email a mailing list with the two following contacts:

Name: Bob Smith
Company: XYZ Corp.

Name: Ted Williams
Company: ABC Corp.


Now, say that the following email was typed:

Hello <FIRSTNAME> <LASTNAME>:
How are things at <COMPANYNAME>? I just wanted to send you an email to inform you of our new, low prices. Have a great day!

We would want the following output to be two email messages, one sent to Bob Smith reading:

Hello Bob Smith:
How are things at XYZ Comp? I just wanted to send you an email to inform you of our new, low prices. Have a great day!

And the email to Ted Williams would read:

Hello Ted Williams:
How are things at ABC Comp? I just wanted to send you an email to inform you of our new, low prices. Have a great day!

To be able to achieve these results, we will need to define a set of keywords, such as <FIRSTNAME>, <LASTNAME>, and <COMPANYNAME>. I will just use these three in my example. To really get the most power out of this system, you would probably want to define keywords like <IF_INTERESTEDINDESKS> ... </IF_INTERESTEDINDESKS>, so that those contacts who were interested in desks would see what appeared between those two tags.

For this system, we will make our email writing employees type in the tags by hand. By using an ActiveX control, though, you can have the users click a button and have the tag automatically inserted for them. Here is the code for an email editor:

COMPOSEEMAIL.ASP

... Option Explicit and inlucde connection.asp headers ...
<%
      Dim rsMailingLists, strSQL
      strSQL = "SELECT MailingListID, Name FROM MailingList ORDER BY Name"
      Set rsMailingList = Conn.Execute(strSQL)
%>

<HTML>
<BODY>
      <FORM METHOD=POST ACTION="SendEmail.asp">
           <B>Mailing List:</B>
           <SELECT NAME="selMailingListID" SIZE=1>
<% Do While not rsMailingList.EOF %>
                <OPTION VALUE="rsMailingList("MailingListID")%>">
                     <%=rsMailingList("Name")%>
                </OPTION>
           <% rsMailingList.MoveNext
           Loop %>
           </SELECT>
           <P>
           <B>Subject:</B>
           <INPUT TYPE=TEXT VALUE="" NAME=txtSubject SIZE=40>
           <P>
           <CENTER>
                <B>Email Message:</B>
           </CENTER>
           <TEXTAREA COLS=50 ROWS=10 NAME=txtEmail></TEXTAREA>
           <P>
           <INPUT TYPE=SUBMIT VALUE="Send Email!">
      >/FORM<
</BODY>
</HTML>


This file simply gets the email message entered by a user, then sends it off via a form to SendEmail.asp. Northing too fancy here. Areas for improvement include adding a ValidateData() JavaScript function to make sure that they enter an email message (your contacts might get peeved if you sent them all a blank email message), and, as aforementioned, turning the TEXTAREA into an ActiveX control to allow for button clicks to insert the special tags. (For more information on creating your own ActiveX, please visit http://www.4GuysFromRolla.com/webtech.)

Now comes, in my opinion, the most interesting part of the entire project, writing the parser to interpret a form-based email to a mailing list into several individual, personalized emails. For my example I will use the CDONTS.NewMail object. There is plenty of information on the Internet for this object, so I won't be going into implementation details here.

Step 6: Parsing the Email
From the ASP code above in the COMPOSEMAIL.asp file, when the user selects to send his or her email, the email message and mailing list ID is passed to a file Iíve chosen to name SendMail.asp. It is in this file that the meat of our processing will occur.

SENDMAIL.asp

... Option Explicit and connection.asp header ...

<%
      Dim objEmail
      Set objEmail = Server.CreateObject("CDONTS.NewMail")

      Dim strEmailMessage, MailingListID
      strEmailMessage = Request.form("txtEmail")
      MailingListID = Request.form("MailingListID")

      Dim rsReceipients, strSQL
      strSQL = "SELECT C.* FROM MailingListContacts MLC INNER JOIN Contacts C ON C.ContactID = MLC.ContactID WHERE MailingListID = " & MailingListID
      Set rsReceipients = Conn.Execute(strSQL)


      'OK, letís talk about what we have / know at this point in the code
      'We have the email message text in strEmailMessage
      'We have a list of all of the Contact information for the contacts on the Mailing List
      'We have an instance of a CDO object

      'So, what do we want to do now?
      'Letís step through the contact recordset, parsing the email message for each contact

      'Set the subject
      objEmail.Subject = Request.form("txtSubject")

      Do while not rsReceipients.EOF
           'We just need to find all occurances of our keyword in the email
           'body and replace them with data from our recordset

           strEmailMessage = Replace(strEmailMessage, "<FIRSTNAME>", rsReceipients("FirstName"))
           strEmailMessage = Replace(strEmailMessage,"<LASTNAME>",rsReceipients("LastName"))

           ...

           'Whatever other replacements you deeem necessary

           objEmail.To = rsReceipients("EmailAddress")
           objEmail.From = strYourEmailAddress 'Some variable set to the email address you want to send these emails as
           objEmail.Body = strEmailMessage

           objEmail.Send

           'Move to the next receipient
           rsReceipient.MoveNext
      Loop
      Set rsReceipients = Nothing
      Set objEmail = Nothing

      Response.Redirect "Confirmation.htm"
%>


Confirmation.htm could be a simple HTML page which alerts the user that the emails were sent off successfully. It could also contain a report listing the email receipients, etc. In the Do ... Loop above, you can greatly extend the power of your personalized email by using conditionals. You can say:

If Not rsRecepient("InterestedInDesks") Then
      strEmailMessage = DeleteTextBetween(strEmailMessage,"<IFINTERESTEDINDESKS>","</IFINTERESTEDINDESKS>")
End If


If you wrote a function DeleteTextBetween to remove all of the text from a stirng between (and including) two substrings, then what this snippet of code would essentially do is delete the "If Interested" text from those who weren't interested in desks.

Conclusion and Thoughts for Improvement:
As this article has hopefully shown you, form-based, personalized emails are not difficult to use or implement, and they can increase a companyís profit by informing their customers of certain sales or new mechandise. The system I showed you in this article is one in its technical infancy; it could be greatly expanded to include numerous tags, conditional sentences or paragraphs, and a more user-friendly email editing interface. This project could be extended to use the Office '97 tools as well.

Another powerful addition to this system would be to create dynamic groups which could be used in the creation of mailing lists. For example, you could create a group named "Contacts who belong to companies who spend more than $10,000 a month on office supplies." This group's members would be calculated on the fly each time you use this group, based on data in the system. I can atest from personal experience that a system like the one descibed is not too difficult to implement. If you are interested in learning more about how to extend the system to include groups like I mentioned, please do not hesitate to contact me! Thanks! If you have any questions about this article, please email me at mitchell@4guysfromrolla.com


ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article