【Vegas原创】SQL Server调用CDO发送邮件

未分类 2年前 (2022) 程序员胖胖胖虎阿

203 0 0

•用于某事件过期mail提醒。

操作步骤：

1.新建DailyJob,事件中填写：

exec dbo.EIP_DeadLine_Notice

/*********************************************************************

* Vegas Lee 制作 2006.01.17 *

**********************************************************************/
CREATE PROCEDURE dbo.EIP_DeadLine_Notice AS
DECLARE @mailTitle nvarchar(100), @mailBody nvarchar(4000), @theMailList nvarchar(500),@C_mailBody nvarchar(4000)

-- 项目到期时，系统自动发mail提醒==========================================
DECLARE Cur_EIP_DeadLine CURSOR FOR
select t.Unique_ID,m1.name as From_Name,t.Category,t.Name,t.DeadLine,t.Description,t.people,t.finish,m.mail,m1.mail as from_mail from todo t,members m,members m1 where datediff(dd,t.DeadLine,getdate())<1 and t.finish=0 and t.people=m.Account_ID and m1.Account_ID=t.User_Account

Set @C_mailBody=N'<HTML>...'
Set @C_mailBody= @C_mailBody +'<tr>...'
DECLARE @v_Unique_ID INT,@v_From_Name VARCHAR(10),@v_Category VARCHAR(10)
DECLARE @v_Name VARCHAR(50),@v_DeadLine nvarchar(20),@v_Description VARCHAR(1000),@v_people nvarchar(10),@v_finish INT,@v_email VARCHAR(100),@v_from_mail VARCHAR(100)

OPEN Cur_EIP_DeadLine
FETCH Cur_EIP_DeadLine
INTO @v_Unique_ID,@v_From_Name,@v_Category,@v_Name,@v_DeadLine,@v_Description,@v_people,@v_finish,@v_email,@v_from_mail

WHILE (@@FETCH_STATUS = 0)
       BEGIN
    Set @mailTitle='系统通知：'
    Set @mailBody ='<HTML>..'

Set @C_mailBody= @C_mailBody + '...'
    SET @theMailList = rtrim(@v_email)


    EXEC sp_send_cdosysmail @v_from_mail,@theMailList,@mailTitle,@mailBody

FETCH Cur_EIP_DeadLine INTO @v_Unique_ID,@v_From_Name,@v_Category,@v_Name,@v_DeadLine,@v_Description,@v_people,@v_finish,@v_email,@v_from_mail
END

CLOSE Cur_EIP_DeadLine
DEALLOCATE Cur_EIP_DeadLine

-----=====================通知
Set @C_mailBody=@C_mailBody + '</table></body></html>'

SET @theMailList = @v_from_Mail
Exec sp_send_cdosysmail_gb @v_from_Mail,@theMailList,@mailTitle,@C_mailBody

3. sp_send_cdosysmail_gb:

CREATE PROCEDURE [dbo].[sp_send_cdosysmail_gb]
   @From varchar(100) ,
   @To varchar(300) ,
   @Subject varchar(100)=" ",
   @Body nvarchar(4000) =" "
   --@Body ntext
/*********************************************************************

This stored procedure takes the above parameters and sends an e-mail.
All of the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
Reference to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp

***********************************************************************/
   AS
   Declare @iMsg int
   Declare @hr int
   Declare @source varchar(255)
   Declare @description varchar(500)
   Declare @output varchar(1000)

--************* 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', '[Mailserver]'

-- 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
   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
   EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBodyPart.Charset','gb2312'
   EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

-- 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

sp_send_cdosysmail(big5)

CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
   @From varchar(100) ,
   @To varchar(300) ,
   @Subject varchar(100)=" ",
   @Body nvarchar(4000) =" "
   --@Body ntext
/*********************************************************************

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

-- 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
   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
   --EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBodyPart.Charset','utf8'
   EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL