Designing a custom mail process

Another important feature in every system is the email notification. The email notification is useful for the following situations:

• Workflow such as the application that requires approval;
• Notifying the system administrator on the current system status (such as server health report);
• Sending daily or weekly reports to the user;
• An error has happened;
• System crash report;
• etc.

The email 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.

Below is the table to store the email record. But, we are not using the MSSQL email notification feature to send the email out. You have to rely on external program to load the email contents and send it out. This gives you a choice of implementing this idea in any database engine.

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 email subject.
    body_text NVARCHAR(MAX) NULL,  -- stores the email 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 (sent_status_id = 0) out and
• Able to send a specific email out upon request (which could be trigger through a TCP port or WCF).