--Setting up the Mail account and profile for sending email from SQL Server
--Creating a Profile
	Exec msdb.dbo.sysmail_add_profile_sp  
		@profile_name = 'ProfileName'  ,
		@description =  'description' 

-- Create a Mail account for SMTP Server.
	EXEC msdb.dbo.sysmail_add_account_sp   @account_name =  'AccountName',  
		@email_address =  '[email protected]' ,  
		@display_name =  'DisplayName' ,   
		@replyto_address =  '[email protected]' ,   
		@description =  'description',   
		@mailserver_name =  'smtpserveraddress '   ,
		@mailserver_type =  'SMTP'   ,
        @use_default_credentials =  0 

-- Adding the account to the profile
		EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
		@profile_name = 'ProfileName',
		@account_name = 'AccountName',
		@sequence_number =1 ;

-- Granting access to the profile to the DatabaseMailUserRole of MSDB
		EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
		@profile_name = 'ProfileName',
		@principal_id = 0,
		@is_default = 1 ;

--Sending Test Mail
		EXEC msdb.dbo.sp_send_dbmail
		@profile_name = 'ProfileName',
		@recipients = '[email protected]',
		@body = 'Database Mail Testing...',
		@subject = 'Database Mail from SQL Server';

--To check the mail status
		select * from msdb.dbo.sysmail_allitems 

	 
	 --Mails sent on same day
	 SELECT * FROM msdb.dbo.sysmail_mailitems WHERE sent_date > DATEADD(DAY, -1,GETDATE())

	 --mails failed on same day
	 SELECT items.subject ,
       items.recipients ,
       items.copy_recipients ,
       items.blind_copy_recipients ,
       items.last_mod_date 
      -- l.description
FROM   msdb.dbo.sysmail_faileditems AS items
       LEFT OUTER JOIN msdb.dbo.sysmail_event_log AS l 
                    ON items.mailitem_id = l.mailitem_id
WHERE  items.last_mod_date > DATEADD(DAY, -1,GETDATE())


  --Tables involved for Mail settings and sending
     
	 select * from msdb.dbo.Sysmail_server
	 select * from msdb.dbo.sysmail_account
	 select * from msdb.dbo.sysmail_profile
	 select * from msdb.dbo.Sysmail_allitems
	 select * from msdb.dbo.Sysmail_log
	 select * from msdb.dbo.Sysmail_configuration

Following steps to be completed to configure an email account and send email from SQL Server

Sending Mail in MS SQL

sql, mail

SQL