The process of Sql Server Database Mail configuration has three main steps i.e.

1. Create a Database Mail account
2. Create a Database Mail profile
3. Configure those two to work together

How to send Email from Trigger in SQL Server?

Enabling Database Mail

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

Mail Account Setup

EXECmsdb.dbo.sysmail_add_account_sp

    @account_name = 'Test'

   ,@description = 'Send emails using SQL Server Stored Procedure'

   ,@email_address = '[email protected]'

   ,@display_name = 'Test'

   ,@replyto_address = NULL

   ,@mailserver_name = 'smtp.gmail.com'

   ,@username = '[email protected]'

   ,@password = 'YourPassword#12345'

   ,@port = 587

   ,@enable_ssl = 1

GO

EXECmsdb.dbo.sysmail_add_profile_sp

    @profile_name = 'Test'

   ,@description = 'Send emails using SQL Server Stored Procedure'

GO

EXECmsdb.dbo.sysmail_add_profileaccount_sp

    @profile_name = 'Test'

   ,@account_name = 'Test'

   ,@sequence_number = 1

GO

Send the Email

EXECmsdb.dbo.sp_send_dbmail

    @profile_name = 'Test'

   ,@recipients = '[email protected]'

   ,@subject = 'Email from SQL Server'

   ,@body = 'This is my First Email sent from SQL Server :)'

   ,@importance ='HIGH'

GO

Delete Account Name from DB mail

/* EXECUTE msdb.dbo.sysmail_delete_account_sp  @account_name = 'Test' ; */

See the Status of Email

SELECT * FROMmsdb.dbo.sysmail_unsentitems

SELECT * FROMmsdb.dbo.sysmail_sentitems

SELECT * FROMmsdb.dbo.sysmail_faileditems

SELECTmailitem_id

    ,[subject]

    ,[last_mod_date]

    ,(SELECT TOP1 [description]

            FROMmsdb.dbo.sysmail_event_log

            WHEREmailitem_id = logs.mailitem_id

            ORDER BYlog_date DESC) [description]

FROMmsdb.dbo.sysmail_faileditems logs

These logs have all useful information that could help in troubleshooting, and we are in particular interested in the ‘description’ column as it holds detailed information about the error and what went wrong.