Determine the Number of Unseen Email Messages

Demonstrates how to determine how many unseen messages exist in an email account on an IMAP server.

Download Chilkat IMAP ActiveX

CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @imap int
    EXEC @hr = sp_OACreate 'Chilkat.Imap', @imap OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int

    --  Anything unlocks the component and begins a fully-functional 30-day trial.
    EXEC sp_OAMethod @imap, 'UnlockComponent', @success OUT, 'Anything for 30-day trial'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        RETURN
      END

    --  Connect to an IMAP server.
    EXEC sp_OAMethod @imap, 'Connect', @success OUT, 'mail.chilkatsoft.com'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        RETURN
      END

    --  Login
    EXEC sp_OAMethod @imap, 'Login', @success OUT, '***', '***'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        RETURN
      END

    --  Select an IMAP mailbox
    EXEC sp_OAMethod @imap, 'SelectMailbox', @success OUT, 'Inbox'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        RETURN
      END

    --  After selecting the mailbox. the total number of emails
    --  is immediately available:
    DECLARE @totalNum int

    EXEC sp_OAGetProperty @imap, 'NumMessages', @totalNum OUT


    PRINT STR(@totalNum)

    --  To determine the number of unseen messages, a call
    --  to Search is required, which returns the set of UIDs
    --  of all unseen messages.
    DECLARE @messageSet int

    --  We can choose to fetch UIDs or sequence numbers.
    DECLARE @fetchUids int

    SELECT @fetchUids = 1

    EXEC sp_OAMethod @imap, 'Search', @messageSet OUT, 'UNSEEN', @fetchUids
    IF @messageSet Is NULL 
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        RETURN
      END

    DECLARE @numUnseen int

    EXEC sp_OAGetProperty @messageSet, 'Count', @numUnseen OUT


    PRINT STR(@numUnseen)


    PRINT 'UIDs ----'

    --  Display the UIDs
    DECLARE @i int

    DECLARE @msgId int

    EXEC sp_OAGetProperty @messageSet, 'Count', @iTmp0 OUT
    SELECT @i = 0
    WHILE @i <= @iTmp0 - 1
      BEGIN
        EXEC sp_OAMethod @messageSet, 'GetId', @msgId OUT, @i

        PRINT STR(@msgId)
        SELECT @i = @i + 1
      END

    --  Disconnect from the IMAP server.
    EXEC sp_OAMethod @imap, 'Disconnect', NULL


END
GO