|
This Issue
In this issue we are going to show you how to create an HTML-based list server. We use a combination of technologies, including SQL Server, SMTP Server, Active Server and Stephen Genusa's ASPMail Component. The SQL Server is used to store the email address of the participants. The SMTP Server is used to send the outgoing email. Active Server is used to tie the SQL Server, the ASPMail Component and the HTML pages together. Finally we use Stephen Genusa's ASPMail component as a COM interface to the SMTP Server. The main work happens in three Active Server pages, with one table in the SQL Server database. But, the magic lies within the ASPMail component, without it we wouldn't be able send email from the Active Server pages.
Getting Started
Before getting started you will have to install the ASPMail component and have the Email table and AddEmail store procedure created.
Subscribing
To subscribe to the list server, the user goes to an Active Server Page and types in his Email Address. The Active Server page then writes the Email Address to a SQL Server and sends the user a piece of email to confirm that they have been signed up.
In the confirmation email, there is a URL that the user can click on to unsubscribe from the list server.
Unsubscribing
To unsubscribe, the user can click on the URL that comes with his confirmation email address. This URL goes to an Active Server page which removes the users name from the SQL Server Database.
Alternativly, the user could unsubscribe the same way that they subscribed. That is by typing their email address in an HTML form which submits the address to the Active Server page mentioned above.
Sending Email
In order for the administrator to send email to all the users in the list server, there is a separate page that only the list server administrator can get to. This page goes through the database of users one by one and sends each one the same piece of Email.
ASPMail Component
ASPMail is an Active Server Component that resides on the Active Server Machine. By using Active Server pages we can call the ASPMail Component and send out email.
Downloading
The ASPMail Component can be downloaded from the ASP Developer's Site.
Creating the Table
In order to store the email address of the members of the mail list, we need to have a table in SQL Server. Here is what it should look like: CREATE TABLE dbo.Email
(
Email_Id int IDENTITY NOT NULL PRIMARY KEY,
Email_Address varchar (75) NULL ,
Email_Date datetime NULL DEFAULT GetDate()
)
AddEmail Stored Procedure
AddEmail checks to make sure that the email address is not present in the Email table before adding the address. This ensures that the email address isn't added twice. Here is the stored procedure: CREATE PROCEDURE AddEmail
@EmailAddress varchar(75)
AS
DECLARE @EmailId int
SELECT @EmailId=Email_Id
FROM Email
WHERE Email_Address=@EmailAddress
IF (@EmailId IS NULL)
BEGIN
INSERT INTO Email (Email_Address) VALUES (@EmailAddress)
END
Creating a DSN
You will need to create an ODBC system DSN on the Internet Information Server that can be used to access the table from the Active Server pages. We called our DSN Email. Make sure that you click on Options >> and put in the database name.
The Referring Page
It is recommended that the user first sees an HTML form that can be filled in and submitted to the signup.asp page. This form should use the POST method submitting the user's email address as Email. An Example of this page can be seen in our mailing list sign up and comes with the download of the example.
Objective
The objective of the signup.asp page is to allow a user to subscribe to the mailing list by making a call to an Active Server page. This is done in two parts. The first step is to generate an email message and send it to the email address that is submitted on the request to the page. If the email doesn't return an error then the email address is added to the database. The second step is adding the email address to the database. This is done through a stored procedure call AddEmail, which can be seen in the SQL section. AddEmail makes sure that there is not a duplicate copy of the Email address in the database.
The Code
Here is the Code for the page. Notice that you will have to redefine the constants at the top of the page in order for it to work properly.
<%
' Configure these constants for your enviorment<BR>
<BR>
Const SMTPServer = "smtp.server.com"
Const ProgramName = "Example Auto Mailer"
Const ProgramAddress = "webmaster@smtp.server.com"
Const ExampleVRoot = "http://www.server.com/mailer"
Const SQLDSN = "Email"
Const SQLLogin = ""
Const SQLPassword = ""
' Get Email Address From URL
EmailAddress=Request.QueryString("Email")
' Construct Email Body
StrBody = "This email message is in response to you signing up with " & ProgramName & Chr(13) & Chr(10)
StrBody = StrBody & "If you want to remove yourself from " & _
the mailing list click on the link below:" & Chr(13) & Chr(10)
StrBody = StrBody & Chr(13) & Chr(10)
StrBody = StrBody & ExampleVRoot & "/remove.asp?Email=" & EmailAddress & Chr(13) & Chr(10)
StrBody = StrBody & Chr(13) & Chr(10)
StrBody = StrBody & "- " & ProgramName & Chr(13) & Chr(10)
%>
<HTML>
<HEAD>
</HEAD>
<BODY>
<%
' Create and configure ASPMail Active Server Component
Set Mailer = Server.CreateObject("SMTPsvg.Mailer")
Mailer.RemoteHost = SMTPServer
Mailer.FromName = ProgramName
Mailer.FromAddress = ProgramName & "|" & ProgramAddress
Mailer.Recipient = EmailAddress
Mailer.CC = ProgramAddress
Mailer.Subject = ProgramName & " Sign Up"
Mailer.BodyText = StrBody
Mailer.SMTPLog = "c:\smtplog.txt"
' Send the Email
if Mailer.SendMail then
' Insert the Email Address into the Database
sql = "EXECUTE AddEmail @EmailAddress='" & EmailAddress & "'"
Set connOnline = Server.CreateObject("ADODB.Connection")
connOnline.Open SQLDSN,SQLLogin,SQLPassword
Set RS = connOnline.Execute(sql)
connOnline.Close
%>
The Email Address has been succesfully added to the mailing list
<%
else
%>
Error sending Email to <%=EmailAddress%>
You have not been added to the Emailing List,
please check the Email Address you submitted and try again
<%
End If
%>
</BODY>
</HTML>
Objective
The objective of this page is to allow users to remove their email name using HTTP. In other words, users are allowed to unsubscribe themselves from the mailing list by making a call to an Active Server page.This page is not very secure because anyone could remove someone else's name, however they would have to know that the person subscribed to the mailing list to start with. There is opportunity to expand the example to include passwords or compare the cookie and the user name before removing the user.
The Code
Here is the Code for the page. Notice that you will have to redefine the constants at the top of the page in order for it to work properly.
<%
' Configure these constants for your enviorment
Const SMTPServer = "smtp.server.com"
Const ProgramName = "Example Auto Mailer"
Const ProgramAddress = "webmaster@smtp.server.com"
Const ExampleVRoot = "http://www.server.com/mailer"
Const SQLDSN = "Email"
Const SQLLogin = ""
Const SQLPassword = ""
EmailAddress=Request.QueryString("Email")
'Construct the Good-bye Body
StrBody = "This email message is in response to you removing yourself from" & _
ProgramName & Chr(13) & Chr(10)
StrBody = StrBody & "If you want to add yourself back to the mailing " & _
"list click on the link below:" & Chr(13) & Chr(10)
StrBody = StrBody & Chr(13) & Chr(10)
StrBody = StrBody & ExampleVRoot & "/signup.idc?Email=" & EmailAddress & Chr(13) & Chr(10)
StrBody = StrBody & Chr(13) & Chr(10)
StrBody = StrBody & "- " & ProgramName & Chr(13) & Chr(10)
' Delete the Email Address
sql = "DELETE Email WHERE Email_Address='" & EmailAddress & "'"
Set connOnline = Server.CreateObject("ADODB.Connection")
connOnline.Open SQLDSN,SQLLogin,SQLPassword
Set RS = connOnline.Execute(sql)
connOnline.Close
%>
<HTML>
<HEAD>
</HEAD>
<BODY>
<% Set Mailer = Server.CreateObject("SMTPsvg.Mailer")
Mailer.RemoteHost = SMTPServer
Mailer.FromName = ProgramName
Mailer.FromAddress = ProgramName & "|" & ProgramAddress
Mailer.Recipient = EmailAddress
Mailer.CC = ProgramAddress
Mailer.Subject = ProgramName & " Removal"
Mailer.BodyText = StrBody
Mailer.SMTPLog = "C:\smtplog.txt"
' Send the Email
Mailer.SendMail
%>
<%=EmailAddress%> has been removed from the mailing list
</BODY>
</HTML>
The Referring Page
It is recommended that send.asp is called from a static .htm page. This static page should contain an HTML form that the Administrator can fill out. The Form should POST to the send.asp page submitting both a Message and a Subject. An example of this page (admin.htm) can be seen in the download.
Objective
The send.asp page is used by the administrator of the mailing list only. The send.asp page sends email to all the members of the mailing list. The mailings are sent a piece at a time. This protects the privacy of the members of the mailing list.
The Code
Here is the Code for the page. Notice that you will have to redefine the constants at the top of the page in order for it too work properly.
<%
Set Mailer = Server.CreateObject("SMTPsvg.Mailer")
Set connOnline = Server.CreateObject("ADODB.Connection")
' Configure these constants for your enviorment
Const SMTPServer = "smtp.server.com"
Const ProgramName = "Example Auto Mailer"
Const ProgramAddress = "webmaster@smtp.server.com"
Const ExampleVRoot = "http://www.server.com/mailer"
Const SQLDSN = "Email"
Const SQLLogin = ""
Const SQLPassword = ""
Message=Request.Form("Message")
Subject=Request.Form("Subject")
' Open a SQL Connection
connOnline.Open SQLDSN,SQLLogin,SQLPassword
' Initalize the Count
Count=0
%>
<HTML>
<HEAD>
</HEAD>
<BODY BGCOLOR=#FFFFFF LINK=#0000FF ALINK=#0000FF VLINK=#0000FF TEXT=#000000>
Subject: <%=Subject%><BR>
Message: <%=Message%><P>
<%
' Select all addresses to mail to
sql = "SELECT Email_Address FROM Email"
Set RS = connOnline.Execute(sql)
Do While not RS.eof
' Get The Email Address from the Server
EmailAddress=RS("Email_Address")
'Clear The Email Message
Mailer.ClearBodyText
' Compose the Email Message
Mailer.BodyText = "This email message is in response to you signing up with"
Mailer.BodyText = ProgramName & Chr(13) & Chr(10)
Mailer.BodyText = "If you want to remove yourself from the mailing list click on the link below:"
Mailer.BodyText = Chr(13) & Chr(10)
Mailer.BodyText = Chr(13) & Chr(10)
Mailer.BodyText = ExampleVRoot & "/remove.asp?Email="
Mailer.BodyText = EmailAddress & Chr(13) & Chr(10)
Mailer.BodyText = Chr(13) & Chr(10)
Mailer.BodyText = Message & Chr(13) & Chr(10)
Mailer.BodyText = Chr(13) & Chr(10)
Mailer.BodyText = "- " & ProgramName & Chr(13) & Chr(10)
' Send the Email Message
Mailer.RemoteHost = SMTPServer
Mailer.FromName = ProgramName
Mailer.FromAddress = ProgramName & "|" & ProgramAddress
Mailer.Recipient = EmailAddress
Mailer.CC = ""
Mailer.Subject = Subject
Mailer.SMTPLog = "c:\smtplog.txt"
Mailer.SendMail
Count=Count+1
%>
<%=Count%>) <%=EmailAddress%> is done!<BR>
<%
RS.MoveNext
Loop
RS.close
connOnline.Close
%>
<P>
All Done!!
</BODY>
</HTML>
Download
You can download the complete source for the sample contained in this issue:040697.zip (4K)
|
