Sending Email From MS SQL server using sp_send_dbmail stored procedure

Sometimes we may required to send the reports to the client via Email from SQL Jobs or we need to send Job success email to DBA once a Job is completed successfully. For such situations, MS SQL server provided a predefined stored procedure sp_send_dbmail for sending Emails from SQL directly in MSDB system database. By using this procedure we can send an email to the specified recipients. The message may include a query result set, file attachments, or both. But using this procedure needs to Configure the Database Mail in SQL Server.

Configure the Database Mail

Please follow the below steps

In the Management Studio, Go to Management -> Database Mail -> Right click on it and Select Configure Database Mail 

It will starts a wizard to configure the email server. Click on Next. 

Select "Set up Database Mail by performing the following tasks" Option and click on Next button. The remaining options are useful to edit the settings once created.

Provide the Profile name and description (optional). Now add SMTP account details by click on ADD button. We can add multiple SMTP accounts to one Profile.

It will lists the existing SMTP accounts with other Profiles. You can add existing account.

To add new account click on "New Account" button.

Enter the Name of the Account and SMTP details and click on OK button. Here I used Gmail SMTP details. Click on the Next button once you added all your SMTP accounts. 

In the next screen, select the access for your Profiles. 

* Public profiles will be accessed by all the users

* Private profiles will be accessed by some specific users only.

In the next screen, we can configure the Retry Attempts, Retry Delay, Attachment File Size and the Extensions that are allowed, Logging Level etc.

Click on OK once your configuration done and Finish in the next screen.

Once your configuration completed, you can verify it by sending the Test Email. For that, again go to Management -> Database Mail -> Right click on it and Select "Send Test E-Mail" option. Select the required profile and give the To email and click on "Send Test E-Mail" button. If your configuration is okay, you can get the email.

Sending Email using sp_send_dbmail Stored Procedure

Once your configuration completed, you can execute the sp_send_dbmail as below to send email.

EXEC [msdb].[dbo].sp_send_dbmail  
    @profile_name = 'My Test Profile',  
    @recipients = 'yourname@gmail.com',  
    @body = 'This mail is generated from SQL Proc.',  
    @subject = 'This is SQL Mail test' ;

Following query will send the Query results as attachment in the email. 

EXEC [msdb].[dbo].sp_send_dbmail 
 @profile_name='My Test Profile',
 @recipients='yourname@gmail.com',
 @subject='Test Report from SQL',
 @body='Hello, Please find the attachment. Thank you.',
 @query= 'SET NOCOUNT ON;SELECT * FROM [Student].[dbo].test2',
 @attach_query_result_as_file=1,
 @query_attachment_filename = 'Test.csv',
 @query_result_separator = ','

Following will send the body as HTML

EXEC [msdb].[dbo].sp_send_dbmail 
 @profile_name='My Test Profile',
 @recipients='yourname@gmail.com',
 @subject='Test Report from SQL',
 @body='Hello,<br/><h2>Please find the attached Details</h2><br/><br/>Thank you<br/><b>Administrator</b>',
 @body_format='html',
 @query= 'SET NOCOUNT ON;SELECT * FROM [Student].[dbo].test2',
 @attach_query_result_as_file=1,
 @query_attachment_filename = 'Test.csv',
 @query_result_separator = ','

You can find all the parameter which can be used with sp_send_dbmail here.
Happy Coding! 😊

Gopikrishna