if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_RECEIVE_MAIL]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_RECEIVE_MAIL]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE SP_RECEIVE_MAIL
AS
BEGIN
DECLARE @object INT,@hr INT
DECLARE @nCount INT ,@n INT ,@m INT,@k INT
DECLARE @Separator VARCHAR(1),@ReTo VARCHAR(200),@ReCC VARCHAR(200)
DECLARE @Msg INT ,@nRecipients INT ,@ReType INT,@nAttachments INT
DECLARE @PropertyName VARCHAR(50)
DECLARE @Recipients INT ,@Recipient INT
DECLARE @MailName VARCHAR(200),@MailEmail VARCHAR(200)
DECLARE @Attachments INT,@Attachment INT
DECLARE @AttachmentName VARCHAR(200)
DECLARE @MsgID INT
DECLARE @FSOobject INT
DECLARE @FileName VARCHAR(200)
DECLARE @isExists BIT
DECLARE @MsgDate NVARCHAR(100)
DECLARE @MsgSubject NVARCHAR(400)
DECLARE @MsgFromName NVARCHAR(200)
DECLARE @MsgBody NVARCHAR(4000)
DECLARE @MsgAttachment NVARCHAR(200)
SET @nCount=0
-------創建JMail.Message?象
EXEC @hr = SP_OACreate 'jmail.pop3', @object OUTPUT
EXEC @hr = SP_OAMethod @object, 'Connect',NULL, 'Jesse@cpjdi.com', '123456', '121.13.*.*'
--EXEC sp_OAGetErrorInfo @object --查看詳細錯誤信息
EXEC @hr = sp_OAGetProperty @object, 'Count',@nCount OUTPUT
SET @n=1
WHILE @n<=@nCount
BEGIN
SELECT @ReTo='',@ReCC=''
SET @PropertyName= 'Messages.Item('+ CAST(@n AS VARCHAR(10)) +')'
EXEC @hr = sp_OAGetProperty @object,@PropertyName,@Msg OUTPUT
EXEC @hr = SP_OASetProperty @Msg, 'EnableCharsetTranslation', 'False'
EXEC @hr = SP_OASetProperty @Msg, 'Logging', 'True'
EXEC @hr = sp_OAGetProperty @Msg,'Recipients',@Recipients OUTPUT
EXEC @hr = sp_OAGetProperty @Recipients, 'Count',@nRecipients OUTPUT
SET @m=0
WHILE @m<@nRecipients
BEGIN
SET @Separator=','
IF @m=@nRecipients-1
BEGIN
SET @Separator=''
END
SET @PropertyName= 'Item('+ CAST(@m AS VARCHAR(10)) +')'
EXEC @hr = sp_OAGetProperty @Recipients,@PropertyName,@Recipient OUTPUT
EXEC @hr = sp_OAGetProperty @Recipient, 'ReType',@ReType OUTPUT
EXEC @hr = sp_OAGetProperty @Recipient, 'Name',@MailName OUTPUT
EXEC @hr = sp_OAGetProperty @Recipient, 'EMail',@MailEmail OUTPUT
IF @ReType=0
--SET @ReTo=@ReTo+@MailName+@MailEmail + @Separator
SET @ReTo=@ReTo+@MailEmail + @Separator --去掉@MailName
ELSE
--SET @ReCC=@ReCC+@MailName+@MailEmail + @Separator
SET @ReCC=@ReCC+@MailEmail + @Separator --去掉@MailName
SET @m=@m+1
END
--求得最大的郵件ID
SELECT TOP 1 @MsgID=MsgID FROM MsgMailInfo ORDER BY MsgID DESC
IF @@ROWCOUNT>0
SET @MsgID=@MsgID+1
ELSE
SET @MsgID=1000000
EXEC @hr = sp_OAGetProperty @Msg,'Attachments',@Attachments OUTPUT
EXEC @hr = sp_OAGetProperty @Attachments, 'Count',@nAttachments OUTPUT
SET @k=0
WHILE @k<@nAttachments
BEGIN
SET @Separator=','
IF @k=@nAttachments-1
BEGIN
SET @Separator=''
END
SET @PropertyName= 'Item('+ CAST(@k AS VARCHAR(10)) +')'
EXEC @hr = sp_OAGetProperty @Attachments,@PropertyName,@Attachment OUTPUT
EXEC @hr = sp_OAGetProperty @Attachment,'Name',@AttachmentName OUTPUT
SET @FileName='C:/' + CAST(@MsgID AS VARCHAR(10)) + '_' + CAST(@k+1 AS VARCHAR(5))
+SUBSTRING(@AttachmentName, CHARINDEX('.',@AttachmentName ),LEN(@AttachmentName))
EXEC @hr = SP_OACreate 'Scripting.FileSystemObject', @FSOobject OUTPUT
EXEC @hr = SP_OAMethod @FSOobject, 'FileExists',@isExists OUTPUT,@FileName
--如果文件存在
IF ISNULL(@isExists,0)=1
BEGIN
--刪除文件
EXEC @hr = SP_OAMethod @FSOobject, 'DeleteFile',NULL,@FileName
END
----釋放FSO對象
EXEC @hr = SP_OADestroy @FSOobject
--保存文件
EXEC @hr = SP_OAMethod @Attachment, 'SaveToFile', NULL ,@FileName
SET @MsgAttachment=@MsgAttachment + CAST(@MsgID AS VARCHAR(10)) + '_' + CAST(@k+1 AS VARCHAR(5))
+SUBSTRING(@AttachmentName, CHARINDEX('.',@AttachmentName ),LEN(@AttachmentName))
+ '|' + @AttachmentName + @Separator
SET @k=@k+1
END
EXEC @hr = sp_OAGetProperty @Msg,'Date',@MsgDate OUTPUT
EXEC @hr = sp_OAGetProperty @Msg,'Subject',@MsgSubject OUTPUT
EXEC @hr = sp_OAGetProperty @Msg,'From',@MsgFromName OUTPUT
EXEC @hr = sp_OAGetProperty @Msg,'Body',@MsgBody OUTPUT
INSERT INTO MsgMailInfo (MsgID,Msg_Date,Msg_Subject,Msg_FormName,Msg_ReTo,Msg_ReCC,Msg_Body,Msg_Attachments)
SELECT @MsgID,@MsgDate,@MsgSubject,@MsgFromName,@ReTo,@ReCC,@MsgBody,@MsgAttachment
SET @n=@n+1
END
--從服務器中刪除郵件
--EXEC @hr = SP_OAMethod @object, 'DeleteMessages'
----釋放Jmail對象
EXEC @hr = SP_OADestroy @object
/*
--判斷文件是否存在 @isFileExist=1 表示存在
DECLARE @isFileExist INT
EXEC master..XP_FILEEXIST 'D:/TEST/ReceiveMail.dll' , @isFileExist output
PRINT @isFileExist
*/
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SQL2000調用JMail接收郵件
最新推荐文章于 2024-09-14 21:52:32 发布
这是一个创建SQL Server 2000存储过程的示例,该存储过程使用JMail组件来接收电子邮件。它遍历邮件收件人、抄送人,保存附件到本地,并将邮件信息插入到MsgMailInfo表中。
摘要由CSDN通过智能技术生成