In the SSMS expand the “Management”, right click on “Database Mail” and choose “Configure Database Mail” from the context menu
Click on the “Next” button in the “Welcome” screen
In the “Select Configuration Tasks” screen keep the default “Set up Database Mail by performing the following tasks:” and click on the “Next” button to continue
If the Database Mail is not activated yet the configuration will inform you and you’ll be able to activate it by clicking “Yes”
To manually activate the “Database Mail” configure it by executing:
USE master
GO
sp_configure ‘show advanced options’,1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure ‘Database Mail XPs’,1
GO
RECONFIGURE
GO
In the “New Profile” screen enter the profile name (eg. MailProfile1). Then click on the “Add” button to enter the email address and the mail server details
Click “OK” in the “Database Account” screen, and click on the “Next” button to continue.
In the “Message Profile Security” screen mark the MailProfie ad Public and click on the “Next” button
In the next screens just hit “Next” to continue.
Now we can test with simple body text:
use msdb
go
sp_send_dbmail
@profile_name=’MailProfile1′,
@recipients = ‘someuser@tempdomain.com’,
@body = ‘This is a body text’
Or send a query results by email:
use msdb
go
sp_send_dbmail
@profile_name=’MailProfile1′,
@recipients = ‘someuser@tempdomain.com’,
@body = ‘body’,
@execute_query_database = ‘msdb’,
@query = ‘SELECT subsystem_id FROM syssubsystems’