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
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
).
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';
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With