Create a Database Mail Configuration Using T-SQL Script

In my last post (Configure Database Mail In SQL Server Database) i have explained how to configure database mail using GUI in SQL server Today i am going to explain how to configure database mail using T-SQL.

1. Create a database mail profile

--Creating a Profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'TestProfile',
@description = 'Test Mail Service for SQL Server'

2. Create database mail account

-- Create a Mail account for gmail.
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'TestAccount',
@email_address = 'Your Email',
@display_name='Fresh Code Hub',
@mailserver_name = 'smtp.gmail.com',
@port=587,
@enable_ssl=1,
@username='Your Email',
@password='Your Password'

3. Add database mail account to database profile

-- Add account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'TestProfile',
@account_name = 'TestAccount',
@sequence_number =1

4. Provide access to the profile

-- Granting access to the profile
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'TestProfile',
@principal_id = 0,
@is_default = 1

5. Send test Mail

--Sending Test Mail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'TestProfile', 
@recipients = 'To Email Here', 
@copy_recipients ='CC Email Here',             --For CC Email if exists
@blind_copy_recipients= 'BCC Email Here',      --For BCC Email if exists
@subject = 'Mail Subject Here', 
@body = 'Mail Body Here',
@body_format='HTML',
@importance ='HIGH',
@file_attachments='C:\Test.pdf';               --For Attachments if exists