sp_send_dbmail embed mhtml file in body

Question

I have an SSRS report that I need to embed in the body of an email using the sp_dbmail stored proc in SQL Server. I'm able to do this using the front end of Outlook by attaching the .mhtml export of the SSRS report using the "Insert as Text" option when attaching a file.

Is there a way I can do this using the sp_dbmail sproc?

I'm using SQL Server 2014 Standard

Answer 1

Yes, it is possible by reading the contents of the file into a variable, then passing it over to the sp_send_dbmail. Here's how you can do it:

declare @htmlBody varchar(max)

SELECT @htmlBody=BulkColumn
FROM   OPENROWSET(BULK N'c:\test\test.html',SINGLE_BLOB) x;



EXEC msdb.dbo.sp_send_dbmail
    @profile_name = N'Email', -- you should use the profile name of yours, whatever is set up in your system.
    @recipients = 'recipient_email_id',
    @subject = 'Test',
    @body = @htmlBody,
    @body_format = 'html',
    @from_address = 'sender_email_id';

This will embed the contents of c:\test\test.html into the email's body. Of course, you can add more to the body.

UPDATE:

This works only if the file you are reading contains HTML contents. If you want to make it work for mhtml, you need to convert the mhtml file to html(See the answer posted by @Pops for details on how to convert mhtml to html).

Answer 2

In case people are wondering, this is how I converted the mhtml to html using SQL.

declare @source varchar(max), 
@decoded varchar(MAX)

SELECT @source =BulkColumn
FROM   OPENROWSET(BULK N'c:\test\test.mhtml',SINGLE_BLOB) x;

SET @source = SUBSTRING(@source,CHARINDEX('base64',@source,1)+10,LEN(@source))
SET @source = SUBSTRING(@source,1,CHARINDEX('-',@source,CHARINDEX('base64',@source,1)+10)-5)
SET @decoded = cast('' AS xml).value('xs:base64Binary(sql:variable("@source"))', 'varbinary(max)')

EXEC msdb.dbo.sp_send_dbmail
@profile_name = N'Email', -- you should use the profile name of yours, whatever is set up in your system.
@recipients = 'recipient_email_id',
@subject = 'Test',
@body = @decoded,
@body_format = 'html',
@from_address = 'sender_email_id';