STEP 1 Create Email Account in SQL Server:
— CREATE ACCOUNT
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = ‘ACCOUNT1’,
@description = ‘Mail account for ACCOUNT1 Database Mail’,
@email_address = ‘EMAIL@ACCOUNT1.co.uk’,
@display_name = ‘SQL EMAIL’,
@username=‘USER’,
@password=‘PASS’,
@mailserver_name = ‘mail.ACCOUNT1.co.uk ‘
STEP 2 Creat Email Profile in SQL Server:
— CREATE PROFILE
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = ‘ACCOUNT1_PROFILE’,
@description = ‘Profile used for ACCOUNT1 database mail’
STEP 3 Link email account to email profile:
— LINK ACCOUNT TOPROFILE
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = ‘ACCOUNT1_PROFILE’,
@account_name = ‘ACCOUNT1’,
@sequence_number = 1
STEP 4 Send email:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘ACCOUNT1_PROFILE’,
@recipients = ’email@test.co.uk’,
@subject = ‘TEST’,
@body = ‘THIS IS A TEST MESSAGE’,
@body_format = ‘HTML’;
The possible sp_send_dbmail variables arelisted below:
sp_send_dbmail [ [ @profile_name = ] ‘profile_name’ ]
[ , [ @recipients = ] ‘recipients [ ; …n ]’ ]
[ , [ @copy_recipients = ] ‘copy_recipient [ ; …n ]’ ]
[ , [ @blind_copy_recipients = ] ‘blind_copy_recipient [ ; …n ]’ ]
[ , [ @subject = ] ‘subject’ ]
[ , [ @body = ] ‘body’ ]
[ , [ @body_format = ] ‘body_format’ ]
[ , [ @importance = ] ‘importance’ ]
[ , [ @sensitivity = ] ‘sensitivity’ ]
[ , [ @file_attachments = ] ‘attachment [ ; …n ]’ ]
[ , [ @query = ] ‘query’ ]
[ , [ @execute_query_database = ] ‘execute_query_database’ ]
[ , [ @attach_query_result_as_file = ] attach_query_result_as_file ]
[ , [ @query_attachment_filename = ] query_attachment_filename ]
[ , [ @query_result_header = ] query_result_header ]
[ , [ @query_result_width = ] query_result_width ]
[ , [ @query_result_separator = ] ‘query_result_separator’ ]
[ , [ @exclude_query_output = ] exclude_query_output ]
[ , [ @append_query_error = ] append_query_error ]
[ , [ @query_no_truncate = ] query_no_truncate ]
[ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ]
