The format for the stored procedure looks something like this:
exec msdb.dbo.sp_send_dbmail @profile_name = 'profile_name'
,@recipients = 'recipients'
,@copy_recipients = 'copy_recipient'
,@blind_copy_recipients = 'blind_copy_recipient'
,@from_address = 'from_address'
,@reply_to = 'reply_to'
,@subject = 'subject'
,@body = 'body'
,@body_format = 'body_format'
,@importance = 'importance'
,@sensitivity = 'sensitivity'
,@file_attachments = 'attachment'
,@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
,@query_result_no_padding = @query_result_no_padding
,@mailitem_id = mailitem_id OUTPUT
As you can see, there are many different options available when using this stored procedure. Here are some items of note on the variables:
- @profile_name is the Profile Name used in the previous post. The email will not go out if the profile_name is not spelled correctly.
- @recipients is a comma separated list of recipients.
- @body_format is the format to use for the body of the email. The options are "Text" and "HTML". The HTML options allows you to use HTML formatting for the email (which includes line breaks, etc.)
- @file_attachment is a comma separated list of files to attach to the email.
- @query (query to execute), @execute_query_database (database to execute the query on), @attach_query_result_as_file (selected whether to put the results as an attachment or in the body of the email, and @query_attachment_filename (the name for the attached query results) can all work together to give you different options when sending your email.
As an example, let's say that I set up a stored procedure that emails customers once they have made a purchase. I have variables that I would use in the stored procedure to perform this. Those variables could include: @customernumber (customers account number), @ordernumber (order number), @customeremail (customers email address). Using these variables, I could then send out an email on order like so:
declare @query1 varchar(4000) = 'select * from CustomerOrder where custombernumber = ' + @customernumber
exec msdb.dbo.sp_send_dbmail @profile_name = 'Test Profile'
,@recipients = @customeremail
,@from_address = 'email@example.com'
,@reply_to = 'firstname.lastname@example.org'
,@subject = 'Customer Order'
,@body = 'Attached is the information for your order'
,@body_format = 'HTML'
,@query = @query1
,@execute_query_database = 'MyDB'
,@attach_query_result_as_file = 1
To read more on SQL Mail, visit the following: SQL Mail