SQL Server Requires Chilkat v11.0.0+

IMAP Find Emails by Date

See more IMAP Examples

Explains how to find emails by date using the IMAP protocol. SQL Server

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    --  This example requires the Chilkat API to have been previously unlocked.
    --  See Global Unlock Sample for sample code.

    DECLARE @imap int
    EXEC @hr = sp_OACreate 'Chilkat.Imap', @imap OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    --  Connect to an IMAP server.
    EXEC sp_OASetProperty @imap, 'Ssl', 1
    EXEC sp_OASetProperty @imap, 'Port', 993
    EXEC sp_OAMethod @imap, 'Connect', @success OUT, 'imap.example.com'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END

    --  Login
    EXEC sp_OAMethod @imap, 'Login', @success OUT, 'myLogin', 'myPassword'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END

    --  Select an IMAP mailbox
    EXEC sp_OAMethod @imap, 'SelectMailbox', @success OUT, 'Inbox'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END

    --  In the IMAP (Internet Message Access Protocol) protocol, criteria strings are used to search for emails matching specific criteria on the mail server. 
    --  When searching for emails by date, you can use criteria strings to specify the date range you're interested in.
    --  
    --  IMAP provides several options for searching emails by date using criteria strings. Here are the commonly used ones:
    --  
    --  1. BEFORE: Searches for emails sent before a specific date.
    --     Example: BEFORE 01-Apr-2022
    --  
    --  2. ON: Searches for emails sent on a specific date.
    --     Example: ON 01-Apr-2022
    --  
    --  3. SINCE: Searches for emails sent after a specific date.
    --     Example: SINCE 01-Apr-2022
    --  
    --  4. SENTBEFORE: Searches for emails whose date header (sent date) is before a specific date.
    --     Example: SENTBEFORE 01-Apr-2022
    --  
    --  5. SENTON: Searches for emails whose date header (sent date) is on a specific date.
    --     Example: SENTON 01-Apr-2022
    --  
    --  6. SENTSINCE: Searches for emails whose date header (sent date) is after a specific date.
    --     Example: SENTSINCE 01-Apr-2022
    --  

    --  The date format used in IMAP criteria strings is typically a specific format like "DD-Mon-YYYY" (e.g., "01-Apr-2022"), but some servers may also accept other formats 
    --  such as "YYYY-MM-DD". It's essential to check the documentation or specifications of the IMAP server you're working with to ensure compatibility with the 
    --  date format you're using.
    --  
    --  By combining these date criteria strings with other search criteria (such as sender, recipient, subject, etc.), 
    --  you can effectively filter and retrieve emails based on various conditions.
    --  

    --  Search for messages on a specific date.
    --  The date string is DD-Month-YYYY where Month is
    --  Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, or Dec.
    DECLARE @onDate nvarchar(4000)
    SELECT @onDate = 'SENTON 15-Apr-2024'

    --  Search for messages between two dates.  SENTBEFORE
    --  finds emails sent before a date, and SENTSINCE finds
    --  email sent on or after a date.  The "AND" operation
    --  is implied by joining criteria, separated by spaces.
    DECLARE @betweenDates nvarchar(4000)
    SELECT @betweenDates = 'SENTSINCE 12-Apr-2024 SENTBEFORE 15-Apr-2024'

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

    DECLARE @messageSet int
    EXEC @hr = sp_OACreate 'Chilkat.MessageSet', @messageSet OUT

    EXEC sp_OAMethod @imap, 'QueryMbx', @success OUT, @betweenDates, @fetchUids, @messageSet
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        EXEC @hr = sp_OADestroy @messageSet
        RETURN
      END

    EXEC sp_OAMethod @messageSet, 'ToCompactString', @sTmp0 OUT
    PRINT @sTmp0

    --  Download each email in the message set.
    DECLARE @email int
    EXEC @hr = sp_OACreate 'Chilkat.Email', @email OUT

    DECLARE @messageCount int
    EXEC sp_OAGetProperty @messageSet, 'Count', @messageCount OUT
    DECLARE @i int
    SELECT @i = 0
    WHILE @i < @messageCount
      BEGIN
        EXEC sp_OAMethod @messageSet, 'GetId', @iTmp0 OUT, @i
        EXEC sp_OAMethod @imap, 'FetchEmail', @success OUT, 0, @iTmp0, @fetchUids, @email
        IF @success = 0
          BEGIN
            EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
            PRINT @sTmp0
            EXEC @hr = sp_OADestroy @imap
            EXEC @hr = sp_OADestroy @messageSet
            EXEC @hr = sp_OADestroy @email
            RETURN
          END

        EXEC sp_OAGetProperty @email, 'Subject', @sTmp0 OUT
        PRINT @sTmp0
        --  ...

        SELECT @i = @i + 1
      END

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

    EXEC @hr = sp_OADestroy @imap
    EXEC @hr = sp_OADestroy @messageSet
    EXEC @hr = sp_OADestroy @email


END
GO