Send Query Result as HTML Mail

,

Tested on SQL Server 2000.

This script use CDOSys to send a mail. No mapi profile required.

The query result is send as html body

Limitation: the result of the query does not have to exceed 8000 characters.

Thanks to Raja Mohamed for the query to html code.

Exemple

exec sp_send_MailHtml

'smtp.fr.oleane.com' --SMTP Server

'YourEmail@sql.com', --From

'daniel.eyer@free.fr', --To

'daniel.eyer@wanadoo.fr', --CC

'', --CCi

'List of USA Customers', --Subject

'select * from dbo.Customers Where Country = ''USA'''

Use Northwind

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

Alter PROCEDURE [dbo].[sp_send_MailHtml]
   @SmtpServer varchar(128),
   @From varchar(128),
   @To varchar(128),
   @Cc varchar(128),
   @BCc varchar(128),
   @Subject varchar(124)=" ",
   @Query varchar(4000) = " "
 /***
 *   Date:         March 2008
 *   Author:       daniel.eyer@free.fr
 *   Project:      Just for fun!
 *   Location:     Any database
 *   Permissions:  PUBLIC EXECUTE
 *   
 *   Description:  Send query result as HTML Mail 
 *   
 *   
 ***/

 AS

--Mail declaration
   Declare @iMsg int
   Declare @hr int
   Declare @source varchar(255)
   Declare @description varchar(500)
   Declare @output varchar(1000)

--HTML declaration
   declare @Columns varchar(8000)
   declare @ColHeader varchar(8000)
   Declare @SqlCmd varchar(8000)
   Declare @HTMLBody varchar(8000)

--************* Create the CDO.Message Object ************************
   EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT

--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
   EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
-- This is to configure the Server Name or IP address. 
-- Replace MailServerName by the name or IP of your SMTP Server.
   EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @SmtpServer

-- Save the configurations to the message object.
   EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

-- Set the e-mail parameters.
   EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
   EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
   If(@Cc Is Not Null)
	Exec @hr = sp_OASetProperty @iMsg, 'Cc', @Cc
   If(@BCc Is Not Null)
	Exec @hr = sp_OASetProperty @iMsg, 'BCc', @BCc
   EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
--   EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body

/*************************************************************************/
		
	-- drop temporary tables used.
	IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##TEMPhtml1')
	DROP TABLE ##TEMPhtml1
	IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##TEMPhtml2') 
	DROP TABLE ##TEMPhtml2
	
	-- prepare query
	set @SqlCmd = 'select * into ##tempHTML1 from (' + @Query + ') as t1'
	execute (@SqlCmd)
	
	--Prepare columns details
	SELECT @columns = 
		COALESCE(@columns + ' + ''</td><td>'' + ', '') + 
		'RTrim(convert(varchar(100),isnull(' + column_name +','' '')))'
		FROM tempdb.information_schema.columns 
		where table_name='##tempHTML1'
		
	--Prepare column Header
	set @colHeader = '<tr bgcolor=#EDFEDF align=Left>'
	SELECT @colHeader = @colHeader + '<td><b>' + column_name + '</b></td>'
	FROM tempdb.information_schema.columns where table_name='##tempHTML1'
	set @colHeader=@colHeader + '</tr>'
	
	--prepare final output
	set @SqlCmd = 
		'Select ''<tr><td>'' + ' + 
		@columns + 
		' ''</td></tr> '' into ##tempHTML2 from ##tempHTML1 ' 
	execute( @SqlCmd)
		
	--set @finalhtmlout= 
	set @HtmlBody =
		' <html> <body><style type="text/css" media="all"> ' +
		'table { margin-bottom: 2em; border-collapse: collapse } ' +
		'td,th {border= 1 solid #999; padding: 0.2em 0.2em; font-size: 12;} ' +
		'</style> <table width="100%"> ' + 
		@colHeader
	
	select @HtmlBody = @HtmlBody + [</td></tr>] 
	from ##tempHTML2
	
	set @HtmlBody = @HtmlBody + ' </table></body></htmL>'

	EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody',  @HtmlBody
	EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
		
	-- drop temporary tables used.
	IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##TEMPhtml1')
	DROP TABLE ##TEMPhtml1
	IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##TEMPhtml2') 
	DROP TABLE ##TEMPhtml2
		
/*************************************************************************/

-- Sample error handling.
/*   IF @hr <>0 
     select @hr
     BEGIN
       EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
       IF @hr = 0
         BEGIN
           SELECT @output = '  Source: ' + @source
           PRINT  @output
           SELECT @output = '  Description: ' + @description
           PRINT  @output
         END
       ELSE
         BEGIN
           PRINT '  sp_OAGetErrorInfo failed.'
           RETURN
         END
     END
*/
-- Do some error handling after each step if you need to.
-- Clean up the objects created.
   EXEC @hr = sp_OADestroy @iMsg
  
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
/*
exec sp_send_MailHtml 
   'smtp.fr.oleane.com' 		--SMTP Server
   'YourEmail@sql.com',			--From
   'daniel.eyer@free.fr',		--To
   'daniel.eyer@wanadoo.fr',	--CC
   '',								--CCi
	'List of USA Customers',	--Subject
	'select *						--Query to send
	from dbo.Customers 
	Where Country = ''USA''
	'

*/

Rate

4.43 (7)

Share

Share

Rate

4.43 (7)