Design a custom mail process

Published on: 17th Jul 2012

Updated on: 17th May 2021

Overview

One important feature in every system is the email notification service. It is useful for the following situations:

• For workflow systems such as the application that requires approval.
• For notifying the system administrator on the current system status (such as server health report).
• For sending daily or weekly reports to the user.
• For sending a system error such as a crash report.
• And many other usages.

The notification does not limit to the above. You may apply this concept into the process logging feature as mentioned in the earlier blog or any other situation that requires user's attention.

Our approach

We are not using the MSSQL email notification feature to send the email out. That is because it limits us from sending the notification through email but not other mediums such as SMS or sends the message to another notification system.

The following table is designed to keep the mail record.

CREATE TABLE tb_mail (
    mail_id UNIQUEIDENTIFIER NOT NULL primary key, -- stores the value of NEWID().
    send_to_email NVARCHAR(MAX) NULL,   -- stores the recipient email address.
    cc_to_email NVARCHAR(MAX) NULL,     -- stores the CC of the current email.
    subject NVARCHAR(255) NULL,         -- stores the subject.
    body_text NVARCHAR(MAX) NULL,   -- stores the message.
    attach_file NVARCHAR(MAX) NULL, -- stores the attachment file name.
    created_on DATETIME NULL,       -- the record creation date/time.
    sent_status_id INT NULL,        -- the email sending status. '0'-pending, '1'-sent & '2'-failed to send
    sent_on DATETIME NULL,          -- the date/time of sending the email.
    mail_type_id INT NULL           -- the email type (optional)
);

In the module that is responsible for sending the email, you must implement the following feature:

• Able to send all the emails (where sent_status_id = 0) out.
• Able to send a specific email out upon request (which could be triggered through a TCP port or WCF).
• Able to send the message through other mediums (such as SMS) or other notification service.

Related posts

• Create user reminder

Jump to #MSSQL blog

Author

Lau Hon Wan, software developer.