Results 1 to 10 of 10

Thread: xp_readmail

  1. #1
    Join Date
    Jan 2004
    Posts
    8

    Unanswered: xp_readmail

    How can i read all mails from outlook using xp_readmail and xp_findnextmsg
    i am able to read only the first mail from the inbox

    please help

    my code is given below


    DECLARE @status2 int
    declare @status int
    declare @message_id varchar(200)
    DECLARE @message varchar(8000), @dateRecive varchar(255),@subject varchar(255)
    DECLARE @subjectMessage varchar(255), @unread varchar(5) ,@unreadFl varchar(5)
    DECLARE @originator_address varchar(255), @sendBy varchar(255)DECLARE @i int
    DECLARE @attachments varchar(255), @attachmentsMessage varchar(255)
    EXEC @status2 = xp_findnextmsg @msg_id = @message_id OUTPUT

    EXEC @status = xp_readmail @msg_id =@message_id ,@originator_address = @sendBy OUTPUT ,

    @unread = @unreadFl OUTPUT , @message =@message OUTPUT , @date_received = @dateRecive OUTPUT

    , @subject = @subjectMessage OUTPUT , @attachments = @attachmentsMessage OUTPUT

    print @message
    print @dateRecive
    print @subjectMessage
    print @unreadFl
    print @sendBy
    print @attachmentsMessage
    Last edited by govindan; 01-21-04 at 02:35.

  2. #2
    Join Date
    Jan 2004
    Posts
    8
    IN the above code section, if i use a While Loop to get all messages based on the message id, the loop is not terminating......

    code is given below


    DECLARE @status2 int
    declare @status int
    declare @message_id varchar(200)
    DECLARE @message varchar(8000), @dateRecive varchar(255),@subject varchar(255)
    DECLARE @subjectMessage varchar(255), @unread varchar(5) ,@unreadFl varchar(5)
    DECLARE @originator_address varchar(255), @sendBy varchar(255)DECLARE @i int
    DECLARE @attachments varchar(255), @attachmentsMessage varchar(255)
    EXEC @status2 = xp_findnextmsg @msg_id = @message_id OUTPUT

    while @message_id is not null
    begin
    EXEC @status = xp_readmail @msg_id =@message_id ,@originator_address = @sendBy OUTPUT ,

    @unread = @unreadFl OUTPUT , @message =@message OUTPUT , @date_received = @dateRecive OUTPUT

    , @subject = @subjectMessage OUTPUT , @attachments = @attachmentsMessage OUTPUT
    print @message print @dateRecive print @subjectMessage print @unreadFl print
    @sendBy print @attachmentsMessage

    EXEC @status2 = xp_findnextmsg @msg_id = @message_id OUTPUT
    end

  3. #3
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    why dont you do a

    Code:
    sp_helptext sp_processmail
    and use the relevant part.
    Get yourself a copy of the The Holy Book

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

  4. #4
    Join Date
    Jan 2004
    Posts
    8
    But still it is not taking the second mail, it is taking the first mail only

    i have only two messages in my outlook inbox

    the code is given below



    ---------------------------------

    DECLARE @status2 int
    declare @status int
    declare @msg_id varchar(64)
    DECLARE @message varchar(8000), @dateRecive varchar(255),@subject varchar(255)
    DECLARE @subjectMessage varchar(255), @unread varchar(5) ,@unreadFl varchar(5)
    DECLARE @originator_address varchar(255), @sendBy varchar(255)DECLARE @i int
    DECLARE @attachments varchar(255), @attachmentsMessage varchar(255)
    declare @mapifailure int
    declare @current_msg varchar(64)

    exec @status = master.dbo.xp_findnextmsg
    @msg_id=@msg_id output,
    @unread_only='false'

    select @mapifailure=0

    if @status <> 0
    begin
    select @mapifailure=1
    print'mapi failure'
    end


    while (@mapifailure=0)
    begin

    if @msg_id is null break
    if @msg_id = '' break

    EXEC @status = xp_readmail
    @msg_id =@msg_id ,
    @originator_address = @sendBy OUTPUT ,
    @message =@message OUTPUT ,
    @date_received = @dateRecive OUTPUT ,
    @subject = @subjectMessage OUTPUT

    if @status <> 0
    begin
    select @mapifailure=1
    break
    end



    /* get new message id before processing & deleting current */

    select @current_msg=@msg_id
    exec @status = master.dbo.xp_findnextmsg
    @msg_id=@msg_id output,
    @unread_only='false'

    print 'findnextmsg status: '
    print @status

    if @status <> 0
    begin
    select @mapifailure=1
    end



    print @message
    print @dateRecive
    print @subjectMessage
    print @sendBy
    end

    --------------------
    after reading the first mail , while doing the next 'findenextmsg' , the value of @status become 1 and correspondingly @mapifailure become 1 and the loop is exiting
    Last edited by govindan; 01-22-04 at 00:30.

  5. #5
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    if you do a exec xp_readmail .... it returns a resultset of all the mails in your inbox ... what exactly are you looking for ???
    Get yourself a copy of the The Holy Book

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

  6. #6
    Join Date
    Jan 2004
    Posts
    8
    Dear Enigma,

    OK you are correct, XP_readmail will return a result set with the following fields
    Originator ,
    Date Received ,
    Recipients ,
    CC List ,
    BCC List ,
    Subject ,
    Message ,
    Unread ,
    Attachments ,
    Message ID , and
    Type

    It is working fine. But the 'Originator' field is returning only the Friendly Name of the Originator, not the actual email id of the originator and i want to capture the mail address of the originator with subject and message

    The code i specified in previous is able to return the 'Originator_address'


    Is there any other way to get all orginators actual mail id from mail inbox

    please suggest...

  7. #7
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Am not getting the time to research that .. but i believe this should work ...

    @msg_id should be having A SIZE OF VARCHAR(255).

    Am not near a sql server .. so you will have to test it and get back ...
    Code:
    DECLARE @status2 int
    declare @status int
    declare @msg_id varchar(255) 
    DECLARE @message varchar(8000), @dateRecive varchar(255),@subject varchar(255) 
    DECLARE @subjectMessage varchar(255), @unread varchar(5) ,@unreadFl varchar(5) 
    DECLARE @originator_address varchar(255), @sendBy varchar(255)DECLARE @i int 
    DECLARE @attachments varchar(255), @attachmentsMessage varchar(255)
    declare @mapifailure int 
    declare @current_msg varchar(64) 
    
    select @mapifailure=0
    
    if @status <> 0 
    begin
    select @mapifailure=1 
    print'mapi failure'
    end
    
    exec master..xp_findnextmsg @msg_id = @msg_id output
    
    
    while (@mapifailure=0) 
    begin 
    
    EXEC @status = master..xp_readmail 
    @msg_id =@msg_id ,
    @originator_address = @sendBy OUTPUT , 
    @message =@message OUTPUT ,
    @date_received = @dateRecive OUTPUT ,
    @subject = @subjectMessage OUTPUT 
    
    print @message 
    print @dateRecive
    print @subjectMessage 
    print @sendBy
    
    if @status <> 0 
    begin 
    select @mapifailure=1 
    break 
    end 
    
    
    
    /* get new message id before processing & deleting current */ 
    
    select @current_msg=@msg_id 
    exec @status = master.dbo.xp_findnextmsg 
    @msg_id=@msg_id output, 
    @unread_only='false' 
    
    print 'findnextmsg status: '
    print @status
    
    if @status <> 0 
    begin 
    select @mapifailure=1 
    end 
    
    
    
    select 
    @message 
    ,@dateRecive
    ,@subjectMessage 
    ,@sendBy
    end
    Get yourself a copy of the The Holy Book

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

  8. #8
    Join Date
    Jan 2004
    Posts
    8
    Enigma,

    Still it is not picking more than one mail from the inbox....

    but no error is displaying, i am surprising why it is not working !!!

  9. #9
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Have tested this out and is working fine on my machine except for the fact that each mail is shown as a seperate resultset.
    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
    Jan 2004
    Posts
    8
    is it problem with my SQL Server or Service Pack ?

Posting Permissions

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