在 SQL Server 預存程序中呼叫 CDO.Message 透過 SMTP 發送郵件

建立預存程序 sys_sendmail

CREATE PROCEDURE sys_sendmail
    @From VARCHAR(100), @To VARCHAR(100), @Bcc VARCHAR(500),
    @Subject VARCHAR(400) = ' ', @Body NTEXT = ' '
AS
BEGIN
    DECLARE @Obj INT, @HR INT;

    EXEC @HR = sp_OACreate 'CDO.Message', @Obj OUT;
    IF @HR <> 0 GOTO ErrorHandler;

    -- 使用 SMTP 發送 (2 = cdoSendUsingPort)
    EXEC @HR = sp_OASetProperty @Obj, 'Configuration.Fields("https://schemas.microsoft.com/cdo/configuration/sendusing").Value', '2';
    EXEC @HR = sp_OASetProperty @Obj, 'Configuration.Fields("https://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'smtp.163.com';

    -- SMTP 驗證 (Basic Auth)
    EXEC @HR = sp_OASetProperty @Obj, 'Configuration.Fields("https://schemas.microsoft.com/cdo/configuration/smtpauthenticate").Value', '1';
    EXEC @HR = sp_OASetProperty @Obj, 'Configuration.Fields("https://schemas.microsoft.com/cdo/configuration/sendusername").Value', 'your_user';
    EXEC @HR = sp_OASetProperty @Obj, 'Configuration.Fields("https://schemas.microsoft.com/cdo/configuration/sendpassword").Value', 'your_pass';
    EXEC @HR = sp_OAMethod @Obj, 'Configuration.Fields.Update', NULL;

    -- 郵件內容
    EXEC @HR = sp_OASetProperty @Obj, 'To', @To;
    EXEC @HR = sp_OASetProperty @Obj, 'Bcc', @Bcc;
    EXEC @HR = sp_OASetProperty @Obj, 'From', @From;
    EXEC @HR = sp_OASetProperty @Obj, 'Subject', @Subject;
    EXEC @HR = sp_OASetProperty @Obj, 'TextBody', @Body;

    -- 發送
    EXEC @HR = sp_OAMethod @Obj, 'Send', NULL;
    IF @HR <> 0 GOTO ErrorHandler;

    PRINT 'success';
    EXEC @HR = sp_OADestroy @Obj;
    RETURN 0;

ErrorHandler:
    EXEC sp_OAGetErrorInfo @Obj;
    RETURN @Obj;
END

呼叫範例

EXEC sys_sendmail
    'sender@domain.com',
    'recipient@domain.com',
    '',
    '測試主旨',
    '這是測試郵件內文。';

前置條件

• 伺服器安裝 SMTP 服務（IIS 內建或獨立 SMTP 伺服器）。
• SQL Server 啟用 Ole Automation Procedures 選項：
  EXEC sp_configure 'Ole Automation Procedures', 1; RECONFIGURE;
• 防火牆開放 TCP 25 (SMTP) 輸出。