MSSQL - Design a custom mail process

Published on: 17th Jul 2012

Updated on: 3rd Aug 2025

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.
Send an invoice to the customer.
Send a reminder to the staff.
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.

The program that is sending the email

Once we have a table that is keeping track the messages, the next thing that we need to design a program to send those messages. You can use any programming language to send the message such C#, Node.js or PHP.

Our approach is that we develop a messaging library to handle the detail implementation using SMTP. Then, all the projects that we have done will be relying on this library for sending the messages.

With this approach, it helps in shortened the development time.

Create user reminder

Back to #MSSQL blog

Back to #blog listing

Overview

Our approach

The program that is sending the email

Related posts