SQL SERVER发送邮件

最新推荐文章于 2022-03-25 16:42:50 发布

转载最新推荐文章于 2022-03-25 16:42:50 发布 · 176 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/aBaoRong/archive/2011/10/18/2216041.html

本文介绍了一个使用SQL Server的存储过程实现的邮件通知系统，该系统用于提醒用户进行模具、检具和治具的例行保养维护。通过从MailRecord表中获取待保养设备的信息，构造详细的邮件内容，并利用CDO组件发送邮件给指定的收件人。

ALTER PROC [dbo].[sendMailToMyUSER]
(
@from VARCHAR(100),
@body VARCHAR(1000),
@subject VARCHAR(100)
)
AS
BEGIN
declare @bodynew varchar(1000)
declare @moldno varchar(20)
declare @comment varchar(200)
declare @store varchar(10)
declare @usetime int
declare @warntime int
declare @j int
declare @count int
declare @min int

    set @bodynew='您好！'+char(10)+char(13)+'當您收到這封信件時，表示有模具/檢具/治具需要進行例行保養維護。'+char(10)+char(13)+'請您（或安排人員）務必於三日內完成下列模具的保養維護動作！'+char(10)+char(13)+'謝謝！'+char(10)+char(13)
    select @count=count(*),@min=min(ID) from dbo.MailRecord
    set @j=0
    while @j<@count
      begin
  select @moldno=MoldNo,@comment=Comment,@store=Store,@usetime=UseTimes,@warntime=WarnTime from dbo.MailRecord where ID=@min
  set @bodynew=@bodynew + @moldno + @comment + @store + convert(varchar(10),@usetime) + convert(varchar(10),@warntime)
        set @j=@j+1
        set @min=@min+1
        set @bodynew= @bodynew+ char(10) + char(13)
      end

-- insert into test (body) values (@bodynew)

    SELECT IDENTITY(INT) id,EmailAddr INTO # FROM dbo.MailBox
    DECLARE @i INT,@b BIT
    SELECT @i = SCOPE_IDENTITY(),@b=1

    DECLARE @to VARCHAR(100)
    WHILE @i>0
        BEGIN
            SELECT @to=EmailAddr FROM # WHERE id=@i
            EXEC sendMailByCDO @to,@from,@subject,@bodynew,@b OUTPUT

            IF @b=0
              begin
                INSERT myMailSendLOG(toMail) SELECT @to
     end
            SET @i=@i-1
        END
drop table #
-- delete from dbo.MailRecord
END

ALTER PROC [dbo].[sendMailByCDO]
(
@to VARCHAR(100),
@from VARCHAR(100),
@subject VARCHAR(100),
@bodynew VARCHAR(1000),
@rt BIT OUTPUT
)
AS
BEGIN
    --insert into test values (@bodynew)
    DECLARE @cd INT,@error INT
    EXEC @error = sp_OACreate 'CDO.Message ',@cd OUTPUT

    EXEC @error = sp_OASetProperty @cd,'Subject',@bodynew
    EXEC @error = sp_OASetProperty @cd,'From',@from
    EXEC @error = sp_OASetProperty @cd,'To',@to
    EXEC @error = sp_OASetProperty @cd,'TEXTBody',@bodynew //若用HTMLBODY则无法按规定换行，用TEXTBODY可按规定换行

    EXEC @error = sp_OASetProperty @cd, 'HTMLBodyPart.Charset','gb2312'
    EXEC @error = sp_OAMethod @cd,'Send'
    EXEC @error = sp_OADestroy @cd
    SET @rt = 1
    IF @error != 0
      begin
        SET @rt = 0
   end
END

转载于:https://www.cnblogs.com/aBaoRong/archive/2011/10/18/2216041.html