蓓姬

2013-04-09

[MSSQL] HTML DBMAIL

  • 1330
  • 0

...

CREATE PROCEDURE [dbo].[HTML_DBMAIL] 	
AS
BEGIN

DECLARE @YYYYMMDD VARCHAR(50)=CONVERT(VARCHAR(8),GETDATE(),112)
DECLARE @subject VARCHAR(100)='['+@YYYYMMDD+'] ***'
DECLARE @recipients VARCHAR(MAX)='***'
DECLARE @copy_recipients VARCHAR(MAX)='***'
DECLARE @body VARCHAR(MAX)=''

SET @body=N'<FONT FACE=CALIBRI SIZE=2>Dear All,<BR><BR> Here is today ***, <BR>'
SET @body=@body+'<TABLE BORDER=1>'
SET @body=@body+'<TR BGCOLOR=yellow>'+
			'<TD STYLE=FONT-FAMILY:CALIBRI;FONT-SIZE:10PT>ORDNUM</TD>'+
			'<TD STYLE=FONT-FAMILY:CALIBRI;FONT-SIZE:10PT>ITMNUM</TD>'+
			'<TD STYLE=FONT-FAMILY:CALIBRI;FONT-SIZE:10PT>CLIMAT</TD>'+
			'<TD STYLE=FONT-FAMILY:CALIBRI;FONT-SIZE:10PT>SHIPDD</TD>'+
			'<TD STYLE=FONT-FAMILY:CALIBRI;FONT-SIZE:10PT>SHPQTY</TD>'+
			'<TD STYLE=FONT-FAMILY:CALIBRI;FONT-SIZE:10PT>FILNAM</TD></TR>'
SELECT @body=@body+'<TR>'+
			'<TD STYLE=FONT-FAMILY:CALIBRI;FONT-SIZE:10PT>'+ORDNUM+'</TD>'+
			'<TD STYLE=FONT-FAMILY:CALIBRI;FONT-SIZE:10PT>'+ITMNUM+'</TD>'+
			'<TD STYLE=FONT-FAMILY:CALIBRI;FONT-SIZE:10PT>'+CLIMAT+'</TD>'+
			'<TD STYLE=FONT-FAMILY:CALIBRI;FONT-SIZE:10PT>'+SHIPDD+'</TD>'+
			'<TD STYLE=FONT-FAMILY:CALIBRI;FONT-SIZE:10PT>'+SHPQTY+'</TD>'+
			'<TD STYLE=FONT-FAMILY:CALIBRI;FONT-SIZE:10PT>'+FILNAM+'</TD></TR>'
			FROM SUMMARY WHERE TADDTT LIKE @YYYYMMDD+'%'
SET @body=@body+'</TABLE><BR>'
SET @body=@body+'***<BR>' 
SET @body=@body+'***'

EXEC msdb.dbo.sp_send_dbmail @profile_name='***', @recipients=@recipients, @copy_recipients=@copy_recipients, @subject=@subject, @body=@body, @body_format='HTML'

END