Thursday, 16 May 2013

Send emails with SQL Server using Html files

4 comments

Sending emails from MS SQL Server using the Database Mail is easy. Once the Database Mail is active and configured (see this blog for quick configuration) the emails can be send using the SQL script below:
USE msdb
GO
EXEC sp_send_dbmail @profile_name='EmailProfile',
@recipients='test@example.com',
@subject='Test message',
@body='This is a test message.'
In cases where there is a need to send emails in html format - creating the content inside of SQL script is not so convenient.
With the help of OPENROWSET function we can read the content of html file, which contains the properly formatted and tested html.

For example below is the html file that we can easily open in the browser and validate it displays correctly the way we want. It contains the html and body tags which wraps the content that will be inserted into the email’s body.

  
       

Test Email

This is a test message
Below is the SQL script which will send the emails by reading the designated html file. The script will remove the leading and ending html and body tags and assign the html content to the email’s body.
DECLARE @SmtpProfile VARCHAR( 50) = 'EmailProfile ' ;
DECLARE @html_file NVARCHAR( 100) = 'c:\test-email.html' ;
DECLARE @Subject NVARCHAR( 255) = 'Test Message' ;
DECLARE @Recipients VARCHAR( 255) = 'test@example.com' ;

DECLARE @Body NVARCHAR( MAX);
DECLARE @ParmDefinition nvarchar( MAX) = N'@Body NVARCHAR(MAX) OUTPUT' ;
DECLARE @sql NVARCHAR( MAX) = 'SET @Body = (SELECT REPLACE(REPLACE(f.BulkColumn, '''', ''''), '''', '''') FROM OPENROWSET ( BULK ''' + @html_file + ''', SINGLE_NCLOB ) f)';

EXEC sp_executesql @sql, @ParmDefinition, @Body OUTPUT;

EXEC msdb ..sp_send_dbmail @profile_name=@SmtpProfile,
                           @recipients=@Recipients,                                            
                           @subject=@Subject,
                           @body_format= 'HTML',
                           @body=@Body;

Please note that the OPENROWSET function in the example above takes SINGLE_NCLOB flag, which reads a file as nvarchar for Unicode support. For varchar use SINGLE_CLOB.

Pretty straightforward, isn’t it.

4 comments:

  1. This is extremely helpful. When I run this on the html file that I have, I get an error message that the input file is not in unicode (SQL Server error 4809). The html file contains Japanese characters so I need to be able to use the SINGLE_NCLOB flag instead of SINGLE_CLOB. Can you point me to how I can get that file to be read as unicode?

    ReplyDelete
  2. Hi Bart,

    Try to open your html file in Notepad (Windows). Then save it as a new file with Unicode encoding ( you should see the drop-down near the Save button in Save As dialog).

    ReplyDelete
  3. That worked perfectly. Thank you so much for your help.

    ReplyDelete