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]GOSET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOCREATE PROCEDURE SP_RECEIVE_MAILASBEGIN 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 */ENDGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO
