Can we send a existing HTML file as part of body using Database mail ?

• vsamantha35

  SSChampion

  Points: 11551

  More actions

  January 20, 2016 at 11:43 am

  #399593

  Hi All,

  Is there a way to send an existing HTML file as body of my email instead sending it as attachment.

  I am using below query, but it is sending as an attachment. Any workaround for this?

  EXEC msdb.dbo.sp_send_dbmail

  @profile_name = 'TestProfile',

  @recipients = 'abcd@gmail.com',

  @body_format = 'HTML',

  @subject = 'Test Email',

  @file_attachments='G:\MySalesReport.htm',

  @attach_query_result_as_file = 0 ;

  Thanks in Advance.

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  January 21, 2016 at 2:31 am

  #1853383

  You could read the file in a variable and concatenate it to the message body:

  DECLARE @body NVARCHAR(MAX)

  SELECT @body = BulkColumn FROM OPENROWSET (BULK 'G:\MySalesReport.htm', SINGLE_BLOB) a

  EXEC msdb.dbo.sp_send_dbmail

  @profile_name = 'TestProfile',

  @recipients = 'abcd@gmail.com',

  @body_format = 'HTML',

  @subject = 'Test Email',

  @body = @body;

  You will have to enable ad-hoc distributed queries to make it work:

  EXEC sp_configure 'show advanced options', 1

  RECONFIGURE

  GO

  EXEC sp_configure 'ad hoc distributed queries', 1

  RECONFIGURE

  GO

  -- Gianluca Sartori