從資料庫查詢資料,並格式成HTML本文寄出
/* DBMailProfile 是指預先設定好的Database Mail, 在 資料庫主機→「管理」→「Database Mail」→右鍵「設定Database Mail」裡*/
Declare @TitleText nvarchar(200)='表格抬頭', @tableHTML nvarchar(max)
Declare @tempDB Table (DBField1 nvarchar(20),DBField2 nvarchar(20),DBField3 nvarchar(20),DBField4 nvarchar(20))
--建立資料表內容(範例資料)
Insert @tempDB
Select 'TestData11','TestData12','TestData13','TestData14'
Union
Select 'TestData21','TestData22','TestData23','TestData24'
--製作HTML文件
SET @tableHTML =
N'<!DOCTYPE html><html><head>'+
N'</head>'+
N'<body>'+
N'<H2 align="center" >' + @TitleText + N'</H2>' +
N'<table style="border:1px solid black; border-collapse: collapse;" border="1" cellpadding="5" width="60%" align="center" >'+
N'<tr>'+
N'<th align="center">表頭一</th><th align="center">表頭二</th><th align="center">表頭三</th><th align="center">表頭四</th>' +
N'</tr>' +
cast(
(
select
"td/@align" = 'center',td = DBField1,'',
"td/@align" = 'center',td = DBField2,'',
"td/@align" = 'center',td = DBField3,'',
"td/@align" = 'center',td = DBField4,''
from @tempDB
order by DBField1
FOR XML PATH('tr'),type
) AS NVARCHAR(MAX) )
+ N'</table></body></html>' ;
--執行 sp_send_dbmail,就會寄出去(@profile_Name 及 @recipients要改成自已的才能試)
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBMailProfile',
@recipients = 'user@domain.com.tw',
@subject = 'My DB Mail Test',
@body = @tableHTML,
@body_format = 'HTML' ;