Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2004
    Posts
    12

    Post Unanswered: reading attachements from mail whit T/SQL

    Hello, i would like to find a way to read email attachements, and to save it into a folder of my choice . I am able to read mail whith the xp_readmail but not to save the attachements.

    thanks in advance

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Code:
    SET QUOTED_IDENTIFIER ON 
    GO
    SET ANSI_NULLS ON 
    GO
    
    ALTER  procedure usp_ProcessMailAttatchments @copypath varchar(256) = 'c:\Attatchments\'
    as
    begin
    	set nocount on
    	declare @status int  
    	declare @msg_id varchar(94)  
    	declare @originator varchar(255)  
    	declare @cc_list varchar(255)  
    	declare @msgsubject varchar(255)  
    	declare @query varchar(8000)  
    	declare @messages int  
    	declare @mapifailure int  
    	declare @resultmsg varchar(80)  
    	declare @current_msg varchar(94)  
    	declare @attachedfiles varchar(800)
    	declare @folder varchar(200)
    	declare @filename varchar(256) 
    	declare @filepath varchar(256)
     
    	select @messages=0  
    	select @mapifailure=0  
    	create table #temp 
    	(
    		results varchar(300)
    	)
    	exec @status = master.dbo.xp_findnextmsg  
      		@msg_id=@msg_id output,  
      		@unread_only='true'  
      
    	if @status <> 0  
    	 	select @mapifailure=1  
      
    	while (@mapifailure=0)  
      	begin  
      
        		if @msg_id is null break  
        		if @msg_id = '' break  
      
        		exec @status = master.dbo.xp_readmail  
    			  @msg_id=@msg_id,  
    			  @originator=@originator output,  
    			  @cc_list=@cc_list output,  
    			  @subject=@msgsubject output,
    			  @suppress_attach = 'false',
    			  @attachments = @attachedfiles output,    
    			  @message=@query output,  
    			  @peek='true'  
      
      		if (@attachedfiles is not null) 
    		begin
    			Select
    				reverse(substring(reverse(Path),1,charindex('\',reverse(Path),1)-1)) "Filename",
    				reverse(substring(reverse(Path),charindex('\',reverse(Path),1),len(path))) "Path"
    			into 
    				#Filepath
    			from
    			(
    				SELECT 
    					NullIf(SubString(';' 
    					+ @attachedfiles 
    					+ ';' , ID , CharIndex(';' , ';' 
    					+ @attachedfiles 
    					+ ';' , ID) - ID) , '') AS Path
    				FROM 
    					Tally
    				WHERE 
    					ID <= Len(';' + @attachedfiles + ';') 
    					AND SubString(';' + @attachedfiles + ';' , ID - 1, 1) = ';' 
    					AND CharIndex(';' , ';' + @attachedfiles + ';' , ID) - ID > 0
    			) a
    			declare c1 cursor for select Filename,Path from #Filepath
    			open c1
    			fetch next from c1 into @filename, @filepath
    			while (@@fetch_status = 0)
    			begin
    				select @query = 'copy '+@filepath + @filename + '  ' + @copypath+@filename
    				insert into #temp exec master..xp_cmdshell @query
    				fetch next from c1 into @filename, @filepath
    			end
    			close c1
    			deallocate c1
    			drop table #Filepath
    		end
        		if @status <> 0  
     		begin  
      			select @mapifailure=1  
      		break  
     	 	END 
    
     		select @current_msg=@msg_id  
     		exec @status = master.dbo.xp_findnextmsg  
      			@msg_id=@msg_id output,  
      			@unread_only='true'  
      
         		if @status <> 0  
     		begin  
      			select @mapifailure=1  
    		end
    	END  
    	set nocount off
     end   
    
    
    
    GO
    SET QUOTED_IDENTIFIER OFF 
    GO
    SET ANSI_NULLS ON 
    GO
    Here you go .. this script will do the work
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Jul 2002
    Posts
    63

    Re: reading attachements from mail whit T/SQL

    Why to do it? What is easier than simple VB script with scripting object and Pop3 component?
    Why to make with SQL server actions that easier and more effect by other application ?

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    OK ... can you show me the VB code for opening a mail ... showing the message body and saving the attatchments onto a folder.

    If it looks better than my code ... I MIGHT EVEN USE IT.



    Why to make with SQL server actions that easier and more effect by other application ?
    Well ..writing SQL seems easy to me than writing code in .Net . Easy coding is a matter of what you know .... I would have taken at least 22 days to make the same thing .(One day for writing the application and the rest for reading How to learn VB in 21 days )
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  5. #5
    Join Date
    Jul 2002
    Posts
    63
    Here You are :

    http://www.edesigntech.com/support/s_asppop.htm


    or :

    Private Function OpenMailAndSavedTheAttachFile()


    On Error GoTo err
    'create the pop3 object
    Dim nNumberOfMessages As Integer
    Dim nNumberOfAttachment As Integer
    nNumberOfMessages = 0
    nNumberOfAttachment = 0

    Set objPop3 = New POP3svg.Mailer
    'set the mail drop folder
    objPop3.MailDirectory = strMailDirectory & "\Temp"
    'set the remote host info
    objPop3.RemoteHost = strRemoteHost
    objPop3.UserName = strUser
    objPop3.Password = strPassword

    'open the pop3 session
    objPop3.OpenPop3
    'Check for Errors in the Open Mail component
    If objPop3.Error <> "" Then
    OpenMailAndSavedTheAttachFile = False
    Exit Function
    End If



    nNumberOfMessages = objPop3.MessageCount


    Dim nCurrentMessage As Integer
    Dim nCurrentAttachment As Integer

    For nCurrentMessage = 1 To nNumberOfMessages
    objPop3.Retrieve nCurrentMessage

    nNumberOfAttachment = objPop3.AttachmentCount

    'Check the Mail subject - if it is the correct mail

    For nCurrentAttachment = 1 To nNumberOfAttachment

    'Save the file

    objPop3.SaveAttachment (nCurrentAttachment)

    Next nCurrentAttachment

    objPop3.Delete (nCurrentMessage)
    Next nCurrentMessage

    'close the session
    objPop3.ClosePop3
    Set objPop3 = Nothing
    OpenMailAndSavedTheAttachFile = True

    Exit Function

    End Function

  6. #6
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Do I need a pop3 server for this .. or can I use it with MS exchange server.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  7. #7
    Join Date
    Jul 2002
    Posts
    63
    Gust get the component ( google on POP3svg / the link I sent ) and regsvr32 it

  8. #8
    Join Date
    Feb 2004
    Posts
    12
    Originally posted by eschapir
    Gust get the component ( google on POP3svg / the link I sent ) and regsvr32 it
    Thanks for your advices, but do you kbow why i have a courrier error when using the Enigma code?

  9. #9
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Change the alter procedure statement to Create procedure
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  10. #10
    Join Date
    Feb 2004
    Posts
    12
    Originally posted by Enigma
    Change the alter procedure statement to Create procedure
    ok
    what is TallY in your proc?
    I am sorry but I am a novice in sql server ;-)

  11. #11
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Oops ... I forgot the tally table

    The tally table is nothing but a table of sequential value we use for some wierd queries like the one in the sproc

    Code:
    CREATE TABLE [TALLY] (
    	[ID] [int] NULL 
    ) ON [PRIMARY]
    GO
    
    declare @i int , @loop int
    select @i = 0, @loop = 10000
    while (@i <@loop)
    begin
            insert into tally select @i
            select @i = @i+1
    end
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  12. #12
    Join Date
    Feb 2004
    Posts
    12
    always my error : xp_readmailfailed with mailing error 0x80004005
    !
    rrrrrrrr ;-)
    if I disable the @msg_id=@msg_id line in the exec xp_readmail command I don't have it...

  13. #13
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Works fine for me ....

    Hmm.. Need to check again ....
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •