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

ahoxha · Accepted Answer · 2017-03-23T10:19:11.537

8

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

edited Mar 23 '17 at 10:19

answered Mar 21 '17 at 10:49

ahoxha

1,919
11
21

Thanks for the response. When I try what you suggested, it embeds the text of the .mhtml file (like what I would see if I opened it up in notepad). It doesn't present the report as it would look if I opened the file in IE. – Pops Mar 21 '17 at 14:29
Can you post a minimal example `.mhtml` file (your report)? Did you set the `@body_format = 'html'`? – ahoxha Mar 21 '17 at 15:02
I see where the problem is. It works only if the file is HTML but not MHTML. – ahoxha Mar 21 '17 at 16:01
For me it's working to embed HTML templates, which is useful. – Turik Mirash Mar 21 '17 at 16:23

Pops · Answer 2 · 2017-03-21T17:45:50.947

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';

sp_send_dbmail embed mhtml file in body

2 Answers2