Complete ASP / SQL Server / SMTP messaging system

Background

The idea was to reduce the number of bad emails that were coming into our webmaster email but we wanted to send an actual email response. This system was what I came up with however, once I got started, I couldn't stop. I added all the normal email features (except for attachments) like replies, forwards and personal folders. I also added an address book so you could store contacts and then use them to send the replies and forwards to multiple people.

Since the address that the SMTP mail is sent from doesn't exist, so there isn't any risk of people responding to it.

Since I have a content management system that uses a domain_id identifier, I wanted to be able to offer the Webmaster Email program to many of my clients without having to create a database for each one of them, so I added the domain_id identifier to allow many domains to use it without cross contamination. You will see that almost all functions will filter by this identifier.

The database is SQL Server. I tried to use an access database but I had trouble getting Access to support multiple recordsets in a single SQL call. Instead of describing the database in detail, I have included a script that will build the SQL database. The data_access.asp contains the username and password that is written into the SQL script. These can be changed as long as they are the same in both places. I use a separate config file when I integrate these. For the demo, I simply hard coded them.

This application is full of some fairly complex code so I hope everyone enjoys perusing it. A lot of the functions and modules can be used in more applications than this one. If you need a native SMTP email creator, I am including mine with this app.

Using the code

Install

The code simply needs to be copied to any ASP virtual directory. No global.asa is needed. A database script is provided and just simply needs to be ran on the SQL Server with create database privileges.

Configure

In the install folder include/data_access.asp, simply replace the settings with your server info and you're all set. In my applications, this is an application variable. You may handle it however you wish.

Run

The application has two parts:

1. The submit message page that you put out on the public side and an ASP to process the posted data.
   • post.htm
   • update_message.asp
2. The Webmaster email application that you use internally to see and respond to the messages.

I have provided a page (default.htm) so you can get to both for demo purposes. It might be a good idea if these are on the same server to secure the webmaster email directory with either a login screen or Windows authentication.

SMTP

The SMTP email works by simply placing a structured text file in the mailroot of the server. SMTP needs to be configured on the server for the files to be picked up and actually sent. The code will create the email in a temp directory first and then move the item over. This eliminates the problem of SMTP trying to pick the file up before we are done writing it. I put in some naming functions to ensure uniqueness in a multi-user environment. To configure the paths to these two directories, open mod_main.asp in the include directory and change the path names as needed.

Database

The database is fairly simple as far as table structures go, but there are some triggers to do cascading deletes and some that manage the read items and notification functions. I had a working copy at one time that sent internal mail with this system and it supported notifications when a person read an item. It also supported priority messages. The database still has fields for these items but I have since removed the code. A clever person could hook this feature back up. Let me know if I can provide any assistance and/or provide some of my old code to let you submit messages internally. Really the only difference is, the internal system had a list of users and the messages were tied to the unique id of those people. Therefore a login was required in order to identify the user and allow each person to read their own mail. There was a series of flags when a sender or a recipient read or deleted an item. I stored settings for each person and allowed them to move the item around separately. To each user, it looked like they were moving their own copy of the message but it was really the same data record being updated.

The trigger to delete the record once both the recipient and the sender deleted the item from their mailboxes is still in the database.

Tables

1. messages
   SQL

   [id] [int] IDENTITY (1, 1) NOT NULL ,
   [msg_text] [varchar] (8000) ,
   [user_id] [varchar] (100),
   [last_modified] [datetime] NULL ,
   [active_flg_sender] [int] NULL ,
   [msg_subject] [varchar] (500) NULL ,
   [notify_req] [int] NULL ,
   [was_delivered] [int] NULL ,
   [priority] [int] NULL ,
   [msg_recip] [varchar] (1000) NULL ,
   [folder_id] [int] NULL ,
   [domain_id] [int] NULL ,
   [parent] [int] NULL

2. msg_folders
   SQL

   [id] [int] IDENTITY (1, 1) NOT NULL ,
   [folder_name] [varchar] (100) NOT NULL ,
   [domain_id] [int] NOT NULL

3. msg_recip
   SQL

   [id] [int] IDENTITY (1, 1) NOT NULL ,
   [recip] [varchar] (50) NULL ,
   [msg_id] [int] NULL ,
   [active_flg_recip] [int] NULL ,
   [msg_read] [int] NULL ,
   [reply_id] [int] NULL ,
   [foward_id] [int] NULL ,
   [read_time] [datetime] NULL ,
   [folder_id] [int] NULL ,
   [reply_date] [datetime] NULL

4. user_addresses
   SQL

   [id] [int] IDENTITY (1, 1) NOT NULL ,
   [first_name] [varchar] (50) NULL ,
   [last_name] [varchar] (50) NULL ,
   [email] [varchar] (150) NULL ,
   [domain_id] [int] NULL 

Code

I have put comments in all the files so they are hopefully self explanatory but just in case, here is a quick summary of the files in the system. I am not including the HTML files in this list simply because there isn't any real code in them. Besides, there is only two: default.htm, and post.htm.

1. addressbook.asp – This is the main window for the address book. It has an IFRAME for the actual listings.
2. addresses_inc.asp – This file is inside an IFRAME in the addressbook.asp window. I only do this because I needed a separation so I could have scroll bars.
3. contactdetail.asp - This file is the input form to add a contact. For this system, I only store the first name, last name and email. You certainly could store whatever you wanted.
4. inframe.asp – This is the same as default.asp. It can be included in a page or be a main page.
5. messagedetail.asp – This is the actual message window. It displays just like a regular email window with options to reply and forward the message.
6. sendmessage.asp – This is the window that pops up if you click reply or forward on a message. It has a button to access the address book for additional addresses. It automatically fills out the “TO” field, the subject (with the classic RE and FW prefaces in place) and the original message.
7. update_message.asp – This file is used by the public HTML form to post a new message into the system.
8. updatecontact.asp – This file is the backend code to add/edit and delete a contact.
9. updatefolder.asp – This file is the backend code to add/edit and delete a personal folder.
10. updatemessage.asp – This file is the backend code to perform all operations on a message such as reply, forward, move, delete and mark as read. When a message is replied to or forwarded, this file calls the make_email.asp to generate the email text file. For the demo, this has been turned off.
11. viewmessages.asp – This file is basically the inbox listing. It is included by inframe.asp. It shows all new postings.
12. viewreadmessages.asp – This file shows all listings that have been read but have not been moved to another personal folder. It is included by inframe.asp.
13. viewsentmessage.asp – This shows all sent emails. It is included by inframe.asp.
14. viewpersonalfolder.asp – This is a general personal folder window. It is the same file used by all the personal folders. It shows any message that was moved to a folder regardless if it was read or not. If it was not read, an icon appears next to the unread message.
15. CDIwm.js – This is a global JavaScript functions module. It is included in almost every file.
16. mod_main.asp - This is a global include in every ASP page. It is where I put all superfluous functions. It includes the data_access.asp which is the global data connection module. For this application, mod_main is empty but I include it since it is one of my standard architecture components.
17. data_access.asp - This is a module that I use in all my applications. It contains all the necessary functions to connect easily to a database using ADO. I use it in VB, ASP and even access.
18. make_email.asp – This is the general-use native SMTP email creation module. It handles all the creating, naming and moving of the file based on the paths configured in mod_main.asp.