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

  • 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.

  • 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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply