Enabling Database Mail

sp_configure 'show advanced', 1; 
GO
RECONFIGURE;
GO

sp_configure 'Database Mail XPs', 1; 
GO
RECONFIGURE;
GO

Checking Database Mail is running or not

EXEC msdb.dbo.sysmail_help_status_sp;

Starting Database Mail

EXEC msdb.dbo.sysmail_start_sp;

Stopping Database Mail

EXEC msdb.dbo.sysmail_stop_sp;

Checking Mail Queue Status

-- The state of mail queue should be 'RECEIVES_OCCURRING'
-- The column length gives the number of mails in the queue
EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail';

Adding Database Mail Account

-- Add valid smtp mailserver_name 
EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = 'Administrator',
    @description = 'Administrative e-mail account',
    @email_address = 'admin@syntaxhelp.com',
    @display_name = 'Automated Admin Mailer',
    @mailserver_name = 'smtp.myserver.com' ;

Adding Database Mail Profile

EXECUTE msdb.dbo.sysmail_add_profile_sp
       @profile_name = 'Administrator',
       @description = 'Administrative mail profile' ;

Adding Database Mail Account to Profile

-- Add unique integer Sequence number for respective account in a profile. Mail is sent through the account based on the order of sequence number. 
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'Administrator',
    @account_name = 'Administrator',
    @sequence_number = 1 ;

Listing all Database Mail Accounts

EXECUTE msdb.dbo.sysmail_help_account_sp ;

Listing all Database Mail Profiles

EXECUTE msdb.dbo.sysmail_help_profile_sp;

Listing all Database Mail Profiles with their respective Accounts

EXECUTE msdb.dbo.sysmail_help_profileaccount_sp;

Deleting Database Mail Account

-- Pass Account name or account id as the parameter
EXECUTE msdb.dbo.sysmail_delete_account_sp
    @account_name = 'Administrator' ;

EXECUTE msdb.dbo.sysmail_delete_account_sp
    @account_id = 1;

Deleting Database Mail Profile

-- Pass Profile name or profile id as the parameter
EXECUTE msdb.dbo.sysmail_delete_profile_sp
    @profile_name = 'Administrator' ;

EXECUTE msdb.dbo.sysmail_delete_profile_sp
    @profile_id = 1;

Deleting Database Mail Account from Profile

EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp
    @profile_name = 'Administrator',
    @account_name = 'Administrator' ;

Updating Database Mail Account

EXECUTE msdb.dbo.sysmail_update_account_sp
     @account_id = 1,
     @account_name = 'DBA',
     @email_address = 'dba@syntaxhelp.com',
     @display_name = 'Admin';

Updating Database Mail Account

EXECUTE msdb.dbo.sysmail_update_profile_sp
    @profile_id = 1,
    @profile_name = 'DBA'

Sending Database Mail

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Administrator',
    @recipients = 'divya@syntaxhelp.com',
    @copy_recipients = 'dba@syntaxhelp.com',
    @blind_copy_recipients = 'support@syntaxhelp.com',
    @body_format = 'TEXT',
    @body = 'Test Email',
    @subject = 'Automated Test Mail';

-- Send file attachments
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Administrator',
    @recipients = 'divya@syntaxhelp.com',
    @file_attachments =  'D:\File1.txt;D:\File2.txt'   
    @body = 'Test File Attachments',
    @subject = 'Files Attached';

-- Send mail with query results
-- Specify value 0 for @attach_query_result_as_file for including the contents in body and specify value 1 to send the results as attachment
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Administrator',
    @recipients = 'divya@syntaxhelp.com',
    @query = 'SELECT COUNT(*) FROM db.dbo.importedrecords' ,
    @subject = 'Imported Records Count',
    @attach_query_result_as_file = 1 ;

Sending HTML Mail

DECLARE @tableHTML  VARCHAR(MAX) ;

SET @tableHTML =
    '
HTML Mail
' +
    '' +
    '' +
    '
Table Header
' ;

EXEC msdb.dbo.sp_send_dbmail 
   @profile_name = 'Administrator',
   @recipients='divya@excellenceinfonet.com',
   @subject = 'HTML mail',
   @body = @tableHTML,
   @body_format = 'HTML' ;

Checking Mail Delivery status

-- Delivery Status can be viewed from column sent_status
-- Check all mails
select * from sysmail_allitems order by last_mod_date desc
-- Check sent mails
select * from sysmail_sentitems order by last_mod_date desc
-- Check failed mails
select * from sysmail_faileditems order by last_mod_date desc

Checking Failed Mail Reason

-- Column Description returns the Failure reason
select * from sysmail_event_log where mailitem_id ='1507'