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
