CREATE TABLE TestX 
(
Dep varchar(30), -- 所屬部門
Name Nvarchar(30), -- 業務人員
Email Nvarchar(30), -- Email
CustNo Nvarchar(30), -- 客編
CustName Nvarchar(30), -- 客戶名稱
Qty int, -- 數量
Remark Nvarchar(30) -- 備註
);

insert into TestX values 
('A',N'小明','A@abc.com.tw','A01','A01',5,'U'),
('B',N'小美','B@abc.com.tw','A02','A02',1,'V'),
('C',N'小白','C@abc.com.tw','A03','A03',3,'W'),
('A',N'小明','A@abc.com.tw','A01','A01',10,'X'),
('B',N'小美','B@abc.com.tw','A02','A02',12,'Y'),
('C',N'小白','C@abc.com.tw','A03','A03',16,'Z');

GetEmailToSend : 取出收件者資料,執行 AutoMail_002

CREATE PROCEDURE [dbo].[GetEmailToSend]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @TransactionName NVARCHAR(MAX) = 'GetEmailToSend';
--
DECLARE @Dep NVARCHAR(MAX)
DECLARE @Name NVARCHAR(MAX)
DECLARE @Email NVARCHAR(MAX)
--
DECLARE Table_Cursor_A CURSOR FOR
SELECT DISTINCT Dep,Name,Email
FROM TestX
WHERE ISNULL(Email,'')<>''
ORDER BY Dep,Name,Email;
--
OPEN Table_Cursor_A;
FETCH NEXT FROM Table_Cursor_A
INTO @Dep,@Name,@Email;
WHILE @@FETCH_STATUS = 0
BEGIN
  EXEC [dbo].[AutoMail_002] @Dep,@Name,@Email;
  FETCH NEXT FROM Table_Cursor_A
  INTO @Dep,@Name,@Email;
END;
--
CLOSE Table_Cursor_A;
DEALLOCATE Table_Cursor_A;
--
END
GO

AutoMail_002 : 發送 Email

CREATE PROCEDURE [dbo].[AutoMail_002]
@Dep NVARCHAR(MAX) = NULL,
@Name NVARCHAR(MAX) = NULL,
@Email NVARCHAR(MAX) = NULL
AS
BEGIN
SET NOCOUNT ON;
-- Insert statements for procedure here
DECLARE @TransactionName NVARCHAR(MAX) = 'AutoMail_002';
BEGIN try
BEGIN TRANSACTION @TransactionName DECLARE @Subject NVARCHAR(MAX)
--
DECLARE @tableHTML NVARCHAR(MAX)
DECLARE @Table NVARCHAR(MAX) = N''
DECLARE @FieldCombStr NVARCHAR(MAX) = N''
--
SET @Subject = CONVERT(varchar(8),getdate(),112) + N' 客戶訂單'
--
DECLARE Table_Cursor_B CURSOR FOR
SELECT
'<tr style="background-color:'+CASE WHEN (ROW_NUMBER() OVER (ORDER BY Dep,Name,Email))%2 =1 THEN '#FFFFFF' ELSE '#00FFFF' END +';">' +
'<td align="right">' + CONVERT(Varchar,ROW_NUMBER() OVER (ORDER BY Dep,Name,Email)) + '</td>' +
-- 
-- '<td>' + RTRIM(IsNull(Dep,''))+ '</td>' +
-- '<td>' + RTRIM(IsNull(Name,''))+ '</td>' +
-- '<td>' + RTRIM(IsNull(Email,''))+ '</td>' +
'<td>' + RTRIM(IsNull(CustNo,''))+ '</td>' +
'<td>' + RTRIM(IsNull(CustName,''))+ '</td>' +
'<td align="right">' + RTRIM(Qty)+ '</td>' +
'<td>' + RTRIM(IsNull(Remark,''))+ '</td>' +
'</tr>'
FROM [dbo].[TestX]
WHERE 1=1
AND Dep=@Dep
AND Name=@Name
AND Email=@Email
ORDER BY Dep,Name,Email,CustNo,CustName;

OPEN Table_Cursor_B;
FETCH NEXT FROM Table_Cursor_B
INTO @FieldCombStr;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Table=@Table+@FieldCombStr;
FETCH NEXT FROM Table_Cursor_B
INTO @FieldCombStr;
END;
CLOSE Table_Cursor_B;
DEALLOCATE Table_Cursor_B;

SET @tableHTML =
N'<H4>'+@Subject+'</H4>' +
N'<table border="1" cellpadding="2" cellspacing="0" style="font-size:13px;font-family:arial,helvetica,sans-serif;" >' +
N'<tr bgcolor="#FF8604">'+
N'<font color="#FFFFFF">'+
N'<td>序號</td>'+
-- N'<td>所屬部門</td>'+
-- N'<td>業務人員</td>'+
-- N'<td>Email</td>'+
N'<td>客編</td>'+
N'<td>客戶名稱</td>'+
N'<td>數量</td>'+
N'<td>備註</td>'+
N'</font>'+
N'</tr>' +
@Table +
N'</table>'+
N'<br>'+
N'<br>'+
N'<H4 ><font color="#FF0000">'+'******'+ N'</font>'+N'</H4>'+
N'<H4>'+'*-*-*'+ N'</H4>';

EXEC msdb.dbo.sp_sEND_dbmail
@profile_name='DBMAIL', --填你的 DataBase Mail 設定的 profile_name
@recipients=@Email, 
@subject= @Subject,
@body = @tableHTML,
@body_format = 'HTML' ;

COMMIT TRANSACTION @TransactionName
END try
BEGIN catch

ROLLBACK TRANSACTION @TransactionName

DECLARE @ErrMsg NVARCHAR(MAX);
SET @ErrMsg = @TransactionName + ' : ' + ERROR_MESSAGE() + ' Line : ' + CONVERT(NVARCHAR(MAX),Error_number())

INSERT INTO [dbo].[AutoMailError]
(Message)
VALUES
(@ErrMsg)

END catch

END
GO

AutoMailError : 錯誤記錄檔

CREATE TABLE [dbo].[AutoMailError] (
[Id] uniqueidentifier NOT NULL DEFAULT (newid()) ,
[Message] nvarchar(MAX) NULL ,
[OccurrenceDateTime] datetime NULL DEFAULT (getdate())
)
GO

ALTER TABLE [dbo].[AutoMailError] ADD PRIMARY KEY ([Id])
GO

執行 GetEmailToSend : 寄出 Email

EXEC [dbo].[GetEmailToSend];

拿這篇來改

EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'Administrator',  
    @recipients = 'A@abc.com.tw',  
    @query = 'SELECT * FROM YourTable WHERE 業務人員 = ''小明'',  
    @subject = 'Work Order',  
    @attach_query_result_as_file = 1 ;

另外，點這裡是我這次鐵人賽唯一的一篇文章，喜歡的話左上角點 Like