3

I'm currently using T-SQL to send an email with attachment from a stored procedure:

EXEC @return_status = msdb.dbo.sp_send_dbmail
    @profile_name = 'ProfileName',
    @recipients = @recipients,
    @body = @mailbody,
    @subject = @subject,
    @file_attachments = @full_attachment_filename

I would like to set a custom header on the email being sent. Is there a way to do that using the sp_send_dbmail stored procedure?

More info: the SQL server version is 2005, and it's sending the email through a Microsoft Exchange server (2003).

Scott Whitlock
  • 13,739
  • 7
  • 65
  • 114
  • 1
    I'm not sure what you mean by "custom header", but in the generic sense, header information would be prepended to `@mailbody` and it would be displayed at the top of each email message. – Dave Mason Aug 18 '14 at 13:33
  • @DMason - specifically I want to add the `X-Auto-Response-Suppress: ALL` email header (which should prevent Outlook's out-of-office assistant from auto-replying unnecessarily). This doesn't have anything to do with column headings (sorry if that wasn't clear). – Scott Whitlock Aug 18 '14 at 13:38

2 Answers2

2

The default sp_send_dbmail does not have the ability to edit header information as per your question...

I know this because I can see the input parameters and code definition:

USE [msdb]
EXEC sp_help sp_send_dbmail

You could however, edit the profile so that replies (auto or otherwise) go to an email address like: donotreply@youremailaddress.co.uk or leave it blank?

gsc_dba
  • 103
  • 8
  • That could work for some cases, but in this case the system does process responses automatically, so people need to be able to reply. – Scott Whitlock Aug 29 '14 at 16:13
  • You could combine my idea with this post to create a transport rule in exchange for the email address you specify in the reply section. http://social.technet.microsoft.com/Forums/exchange/en-US/f2d2fb56-2c75-4515-900a-71d0dc3b2ca4/how-to-set-header-of-email-as-xautoresponsesuppress-oof-dr-rn-nrn?forum=exchangesvrsecuremessaging – gsc_dba Aug 29 '14 at 16:32
  • It seems like that's possible in Exchange 2013, but we're using Exchange 2003. – Scott Whitlock Aug 29 '14 at 20:03
  • My only other thought is to setup a custom rule in "Outlook" to automatically move out of office replies to a custom folder or the deleted items? Here is an example: http://office.microsoft.com/en-gb/outlook-help/manage-email-messages-by-using-rules-HA102749402.aspx – gsc_dba Sep 01 '14 at 12:01
1

The answer is no, gsc_dba's answer is correct, but I'd like to go a bit deeper. I reviewed the code that makes up sp_send_dbmail, the table it writes into, msdb.dbo.sysmail_mailitems has no fields to accommodate custom headers nor any field that remotely fits the bill, so even if you bypassed the system stored procedure and wrote to the table directly there still isn't a way to make database mail handle it.

Elliott Whitlow
  • 11
  • 1
  • 3
    Welcome to Stack Overflow! This should probably have been a comment, not an answer. With a bit more rep, [you will be able to post comments](http://stackoverflow.com/privileges/comment). – Nathan Tuggy Feb 15 '15 at 02:17
  • I disagree Nathan Tuggy and Esoteric Screen Name, the answer IS no, but that doesn't quite cover it, the answer is no and you can't work around it AT ALL. I not only answered the question I went much deeper and made sure that you couldn't work around the limitation even if you went directly at the table. Given these factors it is an answer. – Elliott Whitlow Feb 16 '15 at 04:40
  • @ElliottWhitlow: I didn't see your reply until just now (and I assume Esoteric didn't either; there was no @ sign for notifications), but the reason I said this should be a comment was because it was essentially just adding more weight to the other answer in the same vein. – Nathan Tuggy May 04 '15 at 19:23