SQL2000調用JMail接收郵件

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