Handling E-mail with HTML, CDO, and SMTP, Part Two

n part one, I showed you how to create a basic SMTP e-mail system on an intranet.  In this portion, you will use Active Server Pages code to read the mail from the mailbox and put all the relevant fields into an Access database. If you haven’t already done so, you should read part one. This article picks up where the previous one left off, so you’ll need the code, server configuration, and mail message described in that article to successfully follow part two.

Create a Database in Access 97
Later in the article, you’ll need an Access 97 database that has been configured tostore the e-mail messages. You can download mine, or create your own right now. To create your own, make sure you have the latest Data Access Components installed (MDAC 1.5c or 2.0). Open Access 97 and from the File menu, click New. Then click Blank Database and click OK. In the File New Database dialog box, save the database as smtpdb.mdb. Next, click the Tables tab and then click New. In the New Table dialog box, click Design View and then click OK. Finally, add these fields:

Field Name           Data Type          
ID AutoNumber
From Text
Email Text
Subject Text
MessageText Memo
DateTime Date/Time

From the File menu, click Save and save the table as maillist. When asked to create a Primary Key, click Yes. Then exit Access, saving any changes.

Build the Web Project
Now that you have a database in which to store the e-mail messages, you need to move into your development environment to create the code. I wrote these instructions using the Release Candidate 1 version of Visual InterDev 6. The precise steps may vary in the final release version and certainly have changed from VID 1.0. First, run Visual InterDev 6 and from the File menu, click New Project. In the New Project dialog box, click the New tab and click New Web Project. In the Name box, type smtp and click Open. In the Web Project Wizard?s Step 1 of 4, type the name of the Web server and click Next. After VID contacts the server, in Step 2 of 4, type smtp in the Name box and then click Next. We?ll leave the design work till later. Click Finish. VID goes to work setting up the project files.

Construct the Web Page
With the environment in place, you can create the main Web page and add code to view the e-mail information. From the Project menu, click Add Web Item>Active Server Page and click ASP Page. In the Name field, type smtp.asp and click Open (see Figure 1). Click on the Source tab of the smtp.asp window. You may have to rearrange and resize some windows to get a good view. After the BODY tag, add this code (if you’re copying and pasting, depending on your source, you may need to Paste as HTML):

<%  Set objSession = server.CreateObject("CDONTS.Session")  objSession.LogonSMTP "Service", "[email protected]"  set objFolder= objSession.inbox  set objMessages = objFolder.messages  response.write "Version=" & objSession.version & "
" response.write "Name=" & objFolder.name & "
" response.write "Class=" & objFolder.class & "
" response.write "Application=" & objFolder.application & "
" response.write "Parent=" & objFolder.parent & "
" response.write "Session=" & objFolder.session & "
" response.write "Number of messages=" & objMessages.count & "
" %>

After saving your changes, open the smtp.asp page in the Web browser. You?ll see the information that is available via the Collaboration Data Objects for NT Server (CDONTS). In this case it shows that there is one e-mail message (see Figure 2).

The previous code shows the extent to which you are dealing with parent objects, child objects, and collections in CDONTS.   An inbox contains folders, and messages reside inside the folder. You’ll notice that the next snippet contains a collection of messages, and within it, a collection of recipients. You use  foreach to loop through and retrieve whatever is available in the collections.

View the Message Contents
Now that you know you can see into the directory where the e-mail is stored(c:InetpubMailrootpassable.com), you can start looking at the actual message contents.You create an HTML table, including the headers and then loop through the messages. As you work through the loop, you parse the message to get the various items?the sender’s name, the subject, e-mail address, message text, and more.

In smtp.asp, add this code below the previous code:

  <%  for i = 1 to objMessages.count  response.write ""  response.write ""  response.write ""  Set objAddrEntry = objMessages.item(i).Sender   response.write ""  response.write ""  response.write ""  response.write ""  response.write ""  response.write ""  set recip = objMessages.item(i).recipients  response.write ""  next  %>  
Sender Subject Address Text Received Sent Imp. Size Recipients
" & objMessages.item(i).sender & "" & objMessages.item(i).subject & "" & objAddrEntry.Address & "" & objMessages.item(i).text & "" & objMessages.item(i).timereceived & "" & objMessages.item(i).timesent & "" & objMessages.item(i).importance & "" & objMessages.item(i).size & "" for each person in recip response.write person & "
" next response.write "

View the page again in your browser. This time, you see the contents of the message and some of the other key information about the message such as the time the message was received (see Figure 3).

Having shown that you have access to the e-mail message, you can now take the data from the message and insert it into the Access database.

Insert the E-Mail Into the Database
Although you created the Access database earlier, you still need to configure it so you can write your e-mail message(s) in it. In Visual InterDev, create the database connection. In Windows Explorer, drag the Access database, smtpdb.mdb and drop it in the Visual InterDev Project Explorer. Edit your global.asa file to point the Web server to the database file on the server. For instance:

connstr="DBQ=c:inetpubWWWROOTsmtpsmtpdb.mdb;"    connstr=connstr & "DefaultDir=c:inetpubWWWROOTsmtp;"    connstr=connstr & "Driver={Microsoft Access Driver (*.mdb)};"    connstr=connstr & "FIL=MS Access;"    connstr=connstr & "ImplicitCommitSync=Yes;"    connstr=connstr & "MaxBufferSize=512;"    connstr=connstr & "MaxScanRows=8;"    connstr=connstr & "PageTimeout=5;"    connstr=connstr & "SafeTransactions=0;"    connstr=connstr & "Threads=3;"    connstr=connstr & "UID=admin;"    connstr=connstr & "UserCommitSync=Yes;"    Session("DataConn_ConnectionString") = connstr    Session("DataConn_ConnectionTimeout") = 15    Session("DataConn_CommandTimeout") = 30    Session("DataConn_RuntimeUserName") = "admin"    Session("DataConn_RuntimePassword") = ""

Still in Visual InterDev, open smtp.asp and add this code, which writes the information from the e-mail file into a new database record.

<%  Set OBJdbConnection = Server.CreateObject("ADODB.Connection")   OBJdbConnection.Open Session("DataConn_ConnectionString")  Set smtpdb = Server.CreateObject("ADODB.Recordset")  smtpdb.ActiveConnection = OBJdbConnection  smtpdb.CursorType = 1 ' adOpenKeyset  smtpdb.LockType = 3 ' adLockOptimistic  smtpdb.Source = "maillist"  smtpdb.Open  for i = 1 to objMessages.count  smtpdb.AddNew  smtpdb("From") = trim(objMessages.item(i).sender)  smtpdb("Subject") = trim(objMessages.item(i).subject)  smtpdb("MessageText") = trim(objMessages.item(i).text)  smtpdb("Email") = trim(objAddrEntry.Address)   smtpdb("DateTime") = objMessages.item(i).timesent  smtpdb.Update   smtpdb.MoveFirst  ' objMessages.item(i) ' Uncomment to delete the message  next  %>

Run smtp.asp again. Unless there’s an error, the results on screen may not look anydifferent from the previous run. However, when you open the Access database, you?llsee that a record has been inserted (see Figure 4).

Notice that I’ve commented out objMessages.item(i) from the previous code snippet. That’s to avoid deleting messages during testing.  To avoid duplicate items in the database, you need to uncomment the line. Of course, in a serious production environment you would probably want to test for a successful insertion or copy the mail message to another directory as a backup.

Clean Up the Code
You complete the script by logging off your e-mail access session and then setting the objects to Nothing.

<%  objSession.Logoff  Set OBJdbConnection=Nothing  Set smtpdb =Nothing  Set objSession=Nothing  Set objFolder= Nothing  Set objAddrEntry = Nothing  %>

In these two articles, you’ve seen that you can use HTML, Active Server Pages, and CDONTS to send e-mail messages to the SMTP server. You can selectively retrieve messages from a mail folder, display the contents on a Web page and store each message as a record in a database. From here, you could generate mail lists, sort and analyze the e-mail or automatically post  messages directly on a Web page. You could even use a component such as AspHTTP to turn the e-mail message into a CGI form and submit the contents to yet another application across the Web.

Click here for the full source code and Access database used in this article.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

Recent Articles: