SQL Server

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
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    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