Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


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

Use Active Server Pages code to read the mail from a basic SMTP e-mail mailbox on an intranet and put all the relevant fields into an Access database.

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 to store 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", "service@passable.com" set objFolder= objSession.inbox set objMessages = objFolder.messages response.write "Version=" & objSession.version & "<br>" response.write "Name=" & objFolder.name & "<br>" response.write "Class=" & objFolder.class & "<br>" response.write "Application=" & objFolder.application & "<br>" response.write "Parent=" & objFolder.parent & "<br>" response.write "Session=" & objFolder.session & "<br>" response.write "Number of messages=" & objMessages.count & "<br>" %>

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  for each 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:\Inetpub\Mailroot\passable.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:

<table border="1"> <tr> <th bgcolor="#FBFECF">Sender</th> <th bgcolor="#FBFECF">Subject</th> <th bgcolor="#FBFECF">Address</th> <th bgcolor="#FBFECF">Text</th> <th bgcolor="#FBFECF">Received</th> <th bgcolor="#FBFECF">Sent</th> <th bgcolor="#FBFECF">Imp.</th> <th bgcolor="#FBFECF">Size</th> <th bgcolor="#FBFECF">Recipients</th> </tr> <% for i = 1 to objMessages.count response.write "<tr>" response.write "<td>" & objMessages.item(i).sender & "</td>" response.write "<td>" & objMessages.item(i).subject & "</td>" Set objAddrEntry = objMessages.item(i).Sender response.write "<td>" & objAddrEntry.Address & "</td>" response.write "<td>" & objMessages.item(i).text & "</td>" response.write "<td>" & objMessages.item(i).timereceived & "</td>" response.write "<td>" & objMessages.item(i).timesent & "</td>" response.write "<td>" & objMessages.item(i).importance & "</td>" response.write "<td>" & objMessages.item(i).size & "</td>" set recip = objMessages.item(i).recipients response.write "<td>" for each person in recip response.write person & "<br>" next response.write "</td></tr>" next %> </table><P>

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:\inetpub\WWWROOT\smtp\smtpdb.mdb;" connstr=connstr & "DefaultDir=c:\inetpub\WWWROOT\smtp;" 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 any different from the previous run. However, when you open the Access database, you’ll see 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.

Rama Ramachandran is the Vice President of Technology with Imperium Solutions and is a Microsoft Certified Solution Developer and Site Builder. He has extensive experience with building database systems and has co-authored several books including "Professional Visual InterDev 6 Programming" and "Professional Data Access" (Wrox). Rama teaches Visual Basic and Web development at Fairfield University and University of Connecticut.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date