Database Mail replaces SQLMail. To configure Database Mail is 3 steps:
1. Configure a Database Mail profile (you can have as many as you want).
2. Add one or more accounts to each profile (these use SMTP). If you have more than 1 account in a profile, you can set the fail safe priority.
3. For SQL Server Agent, you have to configure which Database Mail profile to use to send emails.
To send an email with code:
USE msdb
GO
EXEC sp_send_dbmail @profile_name='PinalProfile',
@recipients='test@Example.com',
@subject='Test message',
@body='This is the body of the test message.
Congrates Database Mail Received By you Successfully.'
Only users who are members of the DatabaseMailUserRole in the msdb database can execute a stored procedure that sends mail.
You can query the history and status of emails using this syntax:
USE MSDB
SELECT *
FROM sysmail_mailitems
GO
SELECT *
FROM sysmail_log
GO