In this thread in Google Groups, here's some discussion regarding the problem. Below I attempt to implement the suggested solution in the post. This is a first pass, as I'd like to have some error handling in here as well, so I might update this later.
create proc custom_send_mail
@From varchar(100) ,
@To varchar(355) ,
@CC varchar(300) = NULL,
@BCC varchar(300) = NULL,
@Subject varchar(100)= ' ',
@Body varchar(8000) = ' ',
@attachments varchar(4000)=NULL,
@HTML_IND int = 0
AS
-- This used to call xp_smtp_sendmail!
declare @email_address nvarchar(128)
set @email_address = @From
--sp_help sysmail_account
declare @smtp_server varchar(100)
declare @profile_name sysname
declare @accountID int
declare @profileID int
declare @bodyFormat varchar(100)
set @bodyFormat = case when @HTML_IND = 0 then 'TEXT' else 'HTML' end
set @profile_name = NULL
set @accountID = 0
set @profileID = 0
set @smtp_server = 'your_smtp_server_here'
-- Check to see if there is already an existing profile
select top 1 @profile_name = [name]
from msdb.dbo.sysmail_account a
where a.email_address = @email_address
if @profile_name is NULL
begin
-- Generate a new profile/account name (based on a guid)
set @profile_name = left(replace(convert(varchar(128), newid()),'-',''),30)
-- create the account
exec msdb.dbo.sysmail_add_account_sp
@account_name = @profile_name
, @email_address = @email_address
, @display_name = @email_address
, @replyto_address = @email_address
, @description = 'System Generated Profile for Ad Hoc Email'
, @mailserver_name = @smtp_server
, @mailserver_type = 'SMTP'
, @port = 25
, @username = NULL
, @password = NULL
, @use_default_credentials = 0
, @enable_ssl = 0
, @account_id = @accountID out
-- do I need a profile as well? Yup, I do.
if @accountID > 0
begin
-- Create the profile
exec msdb.[dbo].[sysmail_add_profile_sp]
@profile_name = @profile_name,
@description = 'System Generated Profile for Ad Hoc Email',
@profile_id = @profileID OUTPUT
-- Add the relationship between the profile and the account
exec msdb.[dbo].[sysmail_add_profileaccount_sp]
@profile_id = @profileID,
@profile_name = @profile_name,
@account_id = @accountID,
@account_name = @profile_name,
@sequence_number = 1
end
end
-- Now send the email using the profile name (optionally created).
exec msdb..sp_send_dbmail
@profile_name = @profile_name
, @recipients = @To
, @copy_recipients = @CC
, @blind_copy_recipients = @BCC
, @subject = @Subject
, @body = @Body
, @body_format = @bodyFormat
-- [, [ @importance = ] 'importance' ]
-- [ , [ @sensitivity = ] 'sensitivity' ]
, @file_attachments = @attachments
-- [ , [ @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 ]
No comments:
Post a Comment