To send Database Mail using stored procedures, you must be a member of the DatabaseMailUserRole database role in the msdb database.
Execute permissions for sp_send_dbmail default to all members of the DatabaseMailUser database role in the msdb database.
Go to Surface Area configuration for features and expand Database engine option and select DatabaseMail option and checked Enable Database mail Stored procedure and click OK or Apply button.
The code sample below sends the e-mail through stored procedures:
-- Creates a new Database Mail account
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'KadetEmailAlertAccount',
@description = 'Mail account for sending email alert for Kadet.',
@email_address = 'prachi_purwar@keane.com',
@replyto_address = 'prachi_purwar@keane.com',
@display_name = 'Prachi Purwar',
@mailserver_name = 'MAIL101.KDS.KEANE.COM'
-- Create a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'KadetEmailAlertProfile',
@description = 'Profile used for sending email alert for Kadet.'
-- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'KadetEmailAlertProfile',
@account_name = 'KadetEmailAlertAccount',
@sequence_number = 1
-- Grant access to the profile to all users in the msdb database
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'KadetEmailAlertProfile',
@principal_name = 'public',
@is_default = 1
-- Send the mail to recipients
-- @body_format can have any one value TEXT or HTML
-- @copy_recipients as CC and @blind_copy_recipients as BCC are optional
parameters
-- @file_attachments as file attachement is optional parameter
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'KadetEmailAlertProfile',
@recipients='emailid1@yahoo.com;emailid2@yahoo.com',
@body = 'Hello',
@body_format = 'TEXT',
@subject = 'Wishing'
|
No responses found. Be the first to respond and make money from revenue sharing program.
|