Send mail as html format from SQL Server DB with attached file and/or embedded picture/video

Sometimes we need to send task/operation status report as a well-formatted mail, known as HTML formatted mail. So that, we need to setup a DB mail environment to provide mail from DB end.

Also, below description can be used to send mail as plain text format from DB end... 

To send a mail from DB end, we have to follow below 3 simple steps...

NOTE: make sure login user have granted on the role “DatabaseMailUser” and have permission to execute MSDB procedure “sp_send_dbmail”  ... always "Admin" role is great :)

1.       Create a Database Mail Profile

2.       Send a test mail to test Mail Profile is working

3.   Execute Send Mail Command to Send a Mail (HTML and/or plain text) Using above DB Mail Profile.

See below detail (step-by-step) description of above points...

Connect to your SQL Server database. You can use any user, ie SQL user and/or Windows authentication.

Step - 1. Create a Database Mail Profile:

After login to DB look at the option "Database Mail" under "Management" of Object Explorer window [normally Object Explorer located on the left side of MSSM] section... See Figure1:"DB Mail-1"

All we have to create a DB Mail profile using "Database Mail" option. We can do this by using Wizard Guide and SQL command.

1.1: Wizard Guide: steps are as follows. You have to follow the on screen instruction.

A.    right click on the "Database Mail" option then click "Configure Database Mail". A new window will open and Click next if you are in the welcome page.

B.    Check the option “set up database mail by performing the following tasks:”, Then click next. See “Figure 3: Select Conf Task”

C.    Fill-up the “New Profile” page by providing required information.

Give a name for this new profile: Test_Profile_Name

Write a short description for this profile (its optional) : Test_Profile_Desc

 

D.    Now click on “Add…” under the SMTP section. See Figure 3: New Profile

E.     New window will come named “Add Account to Profile”, see figure4- add account to profile. In this section you have to create a new account, if you don’t have one, to send the mail.

F.     Now you have to fill-up “New Database Mail Account” form.

Give a name: Test_Mail_Account

Give a desc : Test_Mail_Desc

Give an Email address to send a mail from this address: test@test.com

Provide display name to show in the recipient inbox: Test_Display_Name

Provide a reply address to use when recipient like to reply the mail: I leave it blank because of this is a system generated mail.

Provide Mail Server Name from which server will send the mail: testMailServer.com

Provide server port number: 25 (this is default to 25, if not provide the actual port for the provided mail server)

If encryption required please check the option SSL required.

G.    Now provide the SMTP Authentication information:

There are three options: you have to choose one.

·            Windows autho: by this DBMailAccount will use the user, who run the DB Engine service.

·            Basic Autho: it’s a SQL Server user and password.

·            Anonymous Autho: if DB Engine allow guest user to login.

H.    Click “Finish”. Ok, you are done to setup a Mail Profile named : Test_Profile_Name

Step - 2. Send a test mail to test the Mail Profile, Test_Profile_Name,  is working : go to “Send Test E-Mail…”

Now check test result: go to “Database Mail Log”

 

Step - 3. Execute Send Mail Command to Send a Mail (HTML and/or plain text) Using above DB Mail Profile.

As “HTML” format:

-----------------------------------------

EXEC msdb..sp_send_dbmail

 @profile_name = 'GILEAD_Mail'

 ,@recipients = 'Saleh.Faize@bd.imshealth.com;saiful.azam@bd.imshealth.com'

 ,@copy_recipients = 'RSaha@bd.imshealth.com'

 ,@subject = 'A Message From GILEAD DB ADMIN!'

 ,@body = '<b>TEST</b> <i>Mail</i> <font color="FF0000">Body</font> from <font color="00FF00"><b>DataBase End</b></font> J'

 @body_format = 'HTML';

-----------------------------------------

Received mail would be like below image---

As “Plain Text” format:

-----------------------------------------

EXEC msdb..sp_send_dbmail

 @profile_name = 'GILEAD_Mail'

 ,@recipients = 'Saleh.Faize@bd.imshealth.com;saiful.azam@bd.imshealth.com'

 ,@copy_recipients = 'RSaha@bd.imshealth.com'

 ,@subject = 'A Message From GILEAD DB ADMIN!'

 ,@body = 'Mail body as plain text J'

 @body_format = 'Text';

-----------------------------------------