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 are unique as well, specific to the individual the letter has been sent to.
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)
MailingListID
int (PK)
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 ugly but it serves a simple purpose so I did not spend a long time prettying it up. Let's look at the EDITSELECTION.asp file now. This files 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 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 supposed 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 which is just responding to one of two action requests. Once it has completed its task, 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 by 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 suffice. 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:
comprosemail.asp
... Option Explicit and include 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 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("selMailingListID")
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")
'Some variable set to the email address you want to send these emails as
objEmail.From = strYourEmailAddress
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 string 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 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 attest 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!
Scott Mitchell is a Part-time Consultant for Empower Trainers & Consultants and Full time computer science student at the University of Missouri - Rolla. Programming Skills: C/C++, Visual BASIC, Active Server Pages, and MS-SQL Server (working on my Perl skills!). Homepage: http://www.umr.edu/~mitchell
Parting Thoughts
Interested in learning more about Active Server Pages, SQL, JavaScript, and Perl? Need source code for common C/C++ algorithms? Looking for some good jokes and funny spoof web sites? Then stop by http://www.4GuysFromRolla.com, a web site run by four guys from the University of Missouri - Rolla.
Mailing List
Want to receive email when the next article is published? Just Click Here to sign up.
