In this article I will explain with an example, how to send email in SQL Server using Stored Procedure.
This article will illustrate how to send email using sp_send_dbmail Stored Procedure in SQL Server by using GMAIL SMTP settings.
This article is applicable to following SQL Server versions i.e. 2008, 2008R2, 2012, 2014, 2016 and 2017.
 
 
Unblocking the sp_send_dbmail Stored Procedure in SQL Server
The very first step is to unblock the Stored Procedure sp_send_dbmail used for sending emails using SQL Server otherwise when you execute the Stored Procedure you will get following error:
SQL Server blocked access to procedure 'dbo.sp_send_dbmail' of component 'Database Mail XPs' because this component is turned off as part of the security configuration for this server.
 
To unblock simply copy, paste and execute the following SQLs in the SQL Server Query editor window.
USE MASTER
GO
 
SP_CONFIGURE 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
 
SP_CONFIGURE 'Database Mail XPs', 1
RECONFIGURE WITH OVERRIDE
GO
 
SP_CONFIGURE 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO
 
Once executed, the following messages will appear which confirms, everything went OK.
Send Email in SQL Server using Stored Procedure
 
 
Creating Account for sending emails in SQL Server
Account contains information such as Settings, Logins, etc. about the Mail Server which will be used for sending emails using the sp_send_dbmail Stored Procedure in SQL Server.
The sysmail_add_account_sp Stored Procedure is used to add Account to SQL Server.
To create an Account, simply copy, paste, edit (according to your settings) and execute the SQL in the SQL Server Query editor window.
Note: Make sure you enter valid GMAIL settings for @email_address, @username and @password.
 
EXEC msdb.dbo.sysmail_add_account_sp
    @account_name = 'Mudassar_Mail_Account'
   ,@description = 'Send emails using SQL Server Stored Procedure'
   ,@email_address = 'youremail@gmail.com'
   ,@display_name = 'Mudassar Ahmed Khan'
   ,@replyto_address = 'youremail@gmail.com'
   ,@mailserver_name = 'smtp.gmail.com'
   ,@username = 'youremail@gmail.com'
   ,@password = 'GMAIL PASSWORD'
   ,@port = 587
   ,@enable_ssl = 1
GO
 
Note: For complete documentation on sysmail_add_account_sp Stored Procedure, please refer here.
 
 
Creating Profile for sending email in SQL Server
Profile contains information about Account. An Account needs to be added to a Profile and when email is sent, the name of the Profile needs to be supplied to the sp_send_dbmail Stored Procedure in SQL Server.
The sysmail_add_profile_sp Stored Procedure is used to add Profile to SQL Server.
To create a Profile, simply copy, paste, edit (according to your settings) and execute the SQL in the SQL Server Query editor window.
EXEC msdb.dbo.sysmail_add_profile_sp
    @profile_name = 'Mudassar_Email_Profile'
   ,@description = 'Send emails using SQL Server Stored Procedure'
GO
 
Note: For complete documentation on sysmail_add_profile_sp Stored Procedure, please refer here.
 
 
Adding Account to Profile for sending email in SQL Server
In order to add Account to Profile, the sysmail_add_profileaccount_sp Stored Procedure is used.
To add an Account to a Profile, simply copy, paste, edit (update your Profile and Account names) and execute the SQL in the SQL Server Query editor window.
EXEC msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'Mudassar_Email_Profile'
   ,@account_name = 'Mudassar_Mail_Account'
   ,@sequence_number = 1
GO
 
Note: For complete documentation on sysmail_add_profileaccount_sp Stored Procedure, please refer here.
 
 
Sending email using sp_send_dbmail Stored Procedure in SQL Server
Finally, after all the above steps, SQL Server is now ready to send emails using the sp_send_dbmail Stored Procedure.
Note: All the above steps i.e. unblocking sp_send_dbmail, creating Account, creating Profile and adding Account to Profile are to be done only ONCE as all these settings are being saved in the SQL Server Database.
 
To send an email, simply copy, paste, edit the recipient, subject and body and execute the SQL in the SQL Server Query editor window.
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Mudassar_Email_Profile'
   ,@recipients = 'recipient@gmail.com'
   ,@subject = 'Email from SQL Server'
   ,@body = 'This is my First Email sent from SQL Server :)'
   ,@importance ='HIGH'
GO
 
Once executed, the following messages will appear which confirms, your email has been queued.
Send Email in SQL Server using Stored Procedure
 
 
Checking Emails Status and Errors using Log Tables
The following tables can be used to find the status of Emails whether they are still Not Sent, Sent or Failed.
Unsent Emails
The Emails which are not sent due to any reason are available in the msdb.dbo.sysmail_unsentitems table.
SELECT * FROM msdb.dbo.sysmail_unsentitems
 
Sent Emails
The Emails which were sent without any problems are available in the msdb.dbo.sysmail_sentitems table.
SELECT * FROM msdb.dbo.sysmail_sentitems
 
Failed Emails
The Emails which are failed and were not sent are available in the msdb.dbo.sysmail_faileditems table.
SELECT * FROM msdb.dbo.sysmail_faileditems
 
Above table does not store the details of the Error i.e. the Error Message. The details of the Error are present in the msdb.dbo.sysmail_event_log table.
The following Query gets list of Failed emails as well as the details of the Error.
SELECT mailitem_id
    ,[subject]
    ,[last_mod_date]
    ,(SELECT TOP 1 [description]
            FROM msdb.dbo.sysmail_event_log
            WHERE mailitem_id = logs.mailitem_id
            ORDER BY log_date DESC) [description]
FROM msdb.dbo.sysmail_faileditems logs
 
Send Email in SQL Server using Stored Procedure
 
 
Email sending errors related to GMAIL SMTP Server
The errors occurring during sending emails using GMAIL SMTP are covered in the following article.
GMAIL Error: The SMTP server requires a secure connection or the client was not authenticated
 
 
Screenshot
Send Email in SQL Server using Stored Procedure