Send Mail In SQL Server

Enabling Database Mail

EXEC sp_configure @configname = 'show advanced options', @configvalue=1;
reconfigure;
GO
EXEC sp_configure @configname = 'Database Mail XPs', @configvalue = 1;
reconfigure;
GO

Creating a Profile

EXEC msdb.dbO.sysmail_add_profile_sp 
	@profile_name='MailProfile', 
	@description = 'Mail Profile for SQL Server';
SELECT * FROM msdb.dbo.sysmail_profile;

Create a Mail Account for gmail

EXEC msdb.dbO.sysmail_add_account_sp   
	@account_name = 'EmailAccount', 
	@email_address='finance2k16@gmail.com', 
	@mailserver_name='smtp.gmail.com', 
	@port = 587,
	@enable_ssl = 1, 
	@username = 'finance2k16', 
	@password = 'fin@nce2k!6';
SELECT * FROM msdb.dbo.sysmail_account;

Adding the Account to the Profile

EXEC msdb.dbo.sysmail_add_profileaccount_sp 
	@profile_name = 'MailProfile', 
	@account_name = 'EmailAccount', 
	@sequence_number=1;
SELECT * FROM msdb.dbo.sysmail_profileaccount;

Granting access to the Profile to the DatabaseMailUserRole of MSDB

EXEC msdb.dbo.sysmail_add_principalprofile_sp 
	@profile_name = 'MailProfile', 
	@principal_id = 0, 
	@is_default = 1;

Sending Test Mail

EXEC msdb.dbo.sp_send_dbmail 
	@profile_name = 'MailProfile', 
	@recipients = 'npchithanh@yahoo.com.vn', 
	@body='Database Mail Testing....', 
	@subject = 'Datbase mail from SQL Server';
GO

Verifying, check status column

SELECT * FROM msdb.dbo.sysmail_allitems;
SELECT * FROM msdb.dbo.sysmail_server;
GO

Send an e-mail message with attachments

EXEC msdb.dbo.sp_send_dbmail 
	@profile_name = 'SQLProfile', 
	@recipients='npchithanh@yahoo.com.vn', 
	@body='Send With File', 
	@file_attachments='C:\hr.sql';
GO

Send an e-mail message with the results of a query

EXEC msdb.dbo.sp_send_dbmail 
	@profile_name = 'SQLProfile', 
	@recipients='npchithanh@yahoo.com.vn',
	@query = 'select * from hr.dbo.Department', 
	@subject ='Database Department';
GO

Send an HTML e-mail message

DECLARE @tableHTML NVARCHAR(1024) = '<p style="color:red">' + 'Hello' + '

'; EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQLProfile', @recipients = 'npchithanh@yahoo.com.vn', @body = @tableHTML, @body_format='HTML', @subject = 'Mail with HTML'; GO