ASP: create an automatic email alert

Recently, I had to create an automatic alert for a company intranet. The final user wanted to receive an e-mail - and just one email - a month prior to an expiration date. Basically I had a list of names and a related date which represent an issue date for a document. This document expires after one year and the final user needed to receive an email alert a month before the expiration date.

It looks like an easy task, doesn't it? In the following post, we are going to see the logic behind it and how to build the alert system.

Some assumptions

As usual we need to consider some things before going into the theory behind it all.
As said above, we need a table (called 'table') in our database with a date field. The date is representing our expiration date, and we call it 'exp_date'.
Then we need another field, that we can call 'email_notification'.
There will surely be other fields in our table, however, for the purpose of the following exercise, we don't really care. Let's say we have just another field, called 'name' (aside for the usual id field).
Thus, our table structure is the following:

id - int (unique)
name - varchar(255)
exp_date - smalldatetime
email_notification  - numeric(18,0) - default value = 0

That is all.

The theory behind it

What we want to achieve is quite simple. A month prior to the expiration date, an email alert should be sent to a specific recipient.
In my situation, the user wanted to receive only one email. However, following the logic I will shortly explain, we can create different email alerts.
We basically need to create a query that is retrieving data from our table, filtering the resulting recordset by exp_date and email_notification.
After that, we send an email for each found record (or just one email for all the found records) and then update the email_notification field (adding 1 to the present value).

The above will give us the possibility of retrieving only the records that have an exp_date in a month and that have 0 as value in email_notification.

Following this logic, we can always create different alert levels. As an example, we can get records that have an exp_date in a week and that have 1 as value in email_notification.


If you've noticed, we set 0 as default value in our table for email_notification. 0 (zero) in the email_notification field is always telling us that no email alert has been sent. One (1) means that one email has been sent. And so on.

When the exp_date field is updated (when a new document is received and a new expiration date is set), we need to change the email_notification field back to 0 (zero)... remember that when you build up the related form which I will not explain here.

That is the logic. Now let's see how it works.

The code

Let's start with the code. You need to create a page for it or put it in your homepage. Be sure that the code is fired quite frequently, otherwise everything here is useless. In my case, I put it in the company intranet homepage which is opened by many people on a daily basis.
I will explain the code one piece at a time.

<%
Dim chk
Dim chk_cmd

Set chk_cmd = Server.CreateObject ("ADODB.Command")
chk_cmd.ActiveConnection = yourconnectionstring
chk_cmd.CommandText = "SELECT id, name, exp_date, email_notification FROM table WHERE getdate() > DATEADD(month, 11, exp_date) AND email_notification = 0"
chk_cmd.Prepared = true

Set chk = chk_cmd.Execute
%>

The above is our command object (please change yourconnectionstring with your connection string). The query is retrieving records that have an exp_date + 11 months < than today's date and that have email_notification equal to 0.
Let's make an example. The exp_date is 2011-01-01. DATEADD(month, 11, exp_date) is 2011-12-01. The record will be retrieved when today is greater than 2011-12-01 and email_notitifcation is 0 (zero). If you would like to know more about DATEADD please see here.

<% If Not chk.EOF Or Not chk.BOF Then
While (NOT chk.EOF)

This is the beginning of our repeat region.

Dim name
Dim id
Dim exp_date
Dim ema

name = chk.Fields.Item("name")
id = chk.Fields.Item("id")
exp_date = chk.Fields.Item("exp_date")
ema = "recipient@company.com"
   
Dim objCDO
Set objCDO = Server.CreateObject("CDO.Message")
    objCDO.From = "sender@company.com"
    objCDO.To = ema 
    objCDO.Subject = "Expiration Alert"
    objCDO.HTMLBody = "The present alert to notify you that " & name & " has an expired document (expiration date: " & exp_date & ").

Please request a new document with a valid date."
    objCDO.Send()
Set objCDO = Nothing

The above will send an email alert to recipient@company.com. What you need to change in the above snippet is the recipient email address, the sender email address and eventually the body text and the subject of the email.

Dim editCmd

Set editCmd = Server.CreateObject ("ADODB.Command")
editCmd.ActiveConnection = yourconnectionstring
editCmd.CommandText = "UPDATE table SET email_notification = email_notification+1 WHERE id = " & id
editCmd.Prepared = true
editCmd.Execute
editCmd.ActiveConnection.Close

The above snippet is updating the email_notification field. The query, as you can see, is based on the id field which, in our example is unique.

  chk.MoveNext()
Wend
end if
%>

We close the repeat region.

Conclusion

As I said before, in our example, the code is sending separate emails for each expiring item in the table. However, as an exercise, try and change the code in order to send one cumulative email. Believe me, it is very easy.

Hope you enjoyed the article and please let me know your thoughts.