Welcome to ciysys blog

Design a custom mail process

Published on: 17th Jul 2012

Updated on: 17th May 2021


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

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:

Related posts

Jump to #MSSQL blog


Lau Hon Wan, software developer.