Configure Database Mail in SQL Server to send e-mail from SQL Server Database

This article explains, How to configure Database Mail to send e-mail from SQL Server database. So, we explain this step by step as follows:

What is Database Mail ?

Database Mail is an enterprise solution for sending e-mail messages from the SQL Server Database Engine. Using Database Mail, your database applications can send e-mail messages to users. The messages can contain query results, and can also include files from any resource on your network.

STEP 1 : 

First of all, We need Microsoft SQL Server Management Studio (SSMS) installed on our system (PC or Laptop) to send mail from sql  server. So if you do not have installed SSMS on your system, then first download and install it from here.

STEP 2 : 

1. Now Open SSMS and connect it to your SQL Server instance.
2. Expand Management option, there you can find Database Mail option. As you can see in below image.
   
   
   
   
3. Now Right click on Database Mail, and select Configure Database Mail  option.
4. This will open a Database Mail Configuraiotn wizard. Select next to continue.
5. Now choose first option , Set up Database Mail by performing th  following tasks, and click next.
   
   
   
   
   
6. Now give your database mail Profile Name and its description as whatever you want. Here i am giving "Test Profile" as profile name. In SMTP account section, Select Add account option to create new SMTP account as shown in below image.
   
7. Now in New Database Mail Account window, fill up the details as shown in below image. here i am using gmail account to send email. So i used smtp.gmail.com as Server name and 587 as Port number.
   

   

   
   

   In SMTP Authentication and Outgoing Mail Server, use your email address and password from which you want to send e-mail. Also make sure that " The server requires a secure connection" check box must be checked for the sequrity purpose. Now click ok when you done and click next to go to next screen.
8. The next screen is about to make Database mail profile either public or private. You can leave this option and move to next screen and again select next to finish the creation process of Database Mail profile.

STEP 3 : 

Now you all set up to send e-mail. Now go to Management and right click on Database Mail and select Send Test E-mail... option to send test mail as shown in below image.

Choose Test Profile as Database Mail Profile from dropdown and enter To email address, Subject and Body, and click Send Test E-Mail. Now your e-mail has been sent successfully from SQL Server.

STEP 4 : 

If you want to send mail using SQL query then you can use system database 'msdb' and its stored procedure 'sp_send_dbmail' as follow :

EXEC msdb.dbo.sp_send_dbmail 
     @profile_name = 'Test Profile'
     @recipients = 'receipntaddress@gmail.com',
     @subject = 'Test Mail',
     @body = 'This is the test mail.',
     @body_format = 'HTML'

To check status of your sent e-email, you can execute following query in SQL Server : 

USE msdb

GO

SELECT *

FROM   sysmail_allitems

In this query result, you can find all the details about sent mail from sql server. You can check the sent_status column to check the status of mail.

So, this is all about Database Mail in SQL Server to send e-mail. I hope you understand my explanation and you like this article. Thank you.