Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

sp_send_dbmail embed mhtml file in body

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

like image 669
Pops Avatar asked Mar 17 '17 21:03

Pops


2 Answers

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

like image 124
ahoxha Avatar answered Oct 16 '22 15:10

ahoxha


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';
like image 34
Pops Avatar answered Oct 16 '22 13:10

Pops