MSSQL has a service called Database mail and it is used from MSSQL 2005 till today. This service is predecessor of SQL Mail.
I will write about Database mail, and how to set it up.
First of all, you have to open a port 25 (SMTP), so you can send mail from server to the “world”.
Second, we must enable Database Mail
sp_CONFIGURE ‘show advanced’, 1
GO
RECONFIGURE
GO
sp_CONFIGURE ‘Database Mail XPs’, 1
GO
RECONFIGURE
GO
After that, we must give permission of sending mail to SQL Agents service account. My Agent is running under domain service account. Add that user to DatabaseMailUserRole in msdb database
After adding the user to msdb database, next we must create mail profile.
if your mail server requires authentication, enter it in SMTP authentication, if not leave it blank.
Leave it blank, because you setup an account in msdb database.
Set Database mail system parameters.
Last, but not least you have to enable sending alerts from SQL Agent.
Now, test email settings
USE msdb
GO
EXEC sp_send_dbmail @profile_name=’SQL TEST’,
@recipients=’test@sql.com’,
@subject=TEST,
@body=’Body, Successfully sent an e-mail’
Database Mail, of course saves all outgoing e-mails.
You can check it in msdb database, in tables
sysmail_allitems
sysmail_sentitems
sysmail_unsentitems
sysmail_faileditems
And of course you can check the log in table sysmail_log.
Good Luck