Sample code for 30+ languages & platforms
SQL Server Requires Chilkat v11.1.0+

Gmail POP3: Recent Mode with Persistent UIDL Tracking

See more GMail SMTP/IMAP/POP Examples

Use recent:user@example.com to access Gmail's recent POP delivery window, then download each UIDL only once. This example uses an app password, requiring 2-Step Verification and an account policy that allows app passwords. Enable POP and select Keep Gmail's copy in the Inbox. An ordinary account password will not work. If app passwords are unavailable, use the OAuth IMAP examples.

Recent mode exposes approximately the last 30 days of mail and is useful when multiple POP clients need the same messages. Keep the recent prefix out of SMTP settings. This is a rolling window, not a complete-mailbox backup; polling after a long gap can miss older mail.

UIDLs stay as opaque strings. A SHA-256 filename derived from mailbox plus UIDL identifies each saved MIME file. The final file doubles as the persistent completion receipt: a second run skips it. A partial download or failed write is never marked complete, and a leftover .part file is retried. The code sends no DELE commands.

Keep this private directory dedicated to the downloader, use a stable mailbox spelling, and run only one writer at a time. Moving or deleting final files discards the tracking information and permits a new download while the message is still available. For business processing beyond saving MIME, use an application transaction and idempotent handling.

Google documentation

Chilkat SQL Server Downloads

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

    --  Assumes Chilkat is unlocked. See Global Unlock Sample.

    --  Use a private directory dedicated to this application's downloaded mail.
    DECLARE @mailbox nvarchar(4000)
    SELECT @mailbox = 'user@example.com'
    DECLARE @appPassword nvarchar(4000)
    SELECT @appPassword = 'YOUR_16_CHARACTER_APP_PASSWORD'
    DECLARE @files int
    EXEC @hr = sp_OACreate 'Chilkat.FileAccess', @files OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OAMethod @files, 'DirEnsureExists', @success OUT, 'gmail-pop-mail'
    IF @success <> 1
      BEGIN

        PRINT 'Could not create the private download directory.'
        EXEC @hr = sp_OADestroy @files
        RETURN
      END
    DECLARE @mailman int
    EXEC @hr = sp_OACreate 'Chilkat.MailMan', @mailman OUT

    EXEC sp_OASetProperty @mailman, 'MailHost', 'pop.gmail.com'
    EXEC sp_OASetProperty @mailman, 'MailPort', 995
    EXEC sp_OASetProperty @mailman, 'PopSsl', 1
    EXEC sp_OASetProperty @mailman, 'RequireSslCertVerify', 1
    DECLARE @joined1 int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @joined1 OUT

    EXEC sp_OAMethod @joined1, 'Append', @success OUT, 'recent:'
    EXEC sp_OAMethod @joined1, 'Append', @success OUT, @mailbox
    EXEC sp_OAMethod @joined1, 'GetAsString', @sTmp0 OUT
    EXEC sp_OASetProperty @mailman, 'PopUsername', @sTmp0
    EXEC sp_OASetProperty @mailman, 'PopPassword', @appPassword
    --  Use an eligible account's app password loaded from protected configuration.
    --  The recent: prefix is only for POP; never add it to an SMTP username.
    EXEC sp_OAMethod @mailman, 'Pop3BeginSession', @success OUT
    IF @success <> 1
      BEGIN
        EXEC sp_OAMethod @mailman, 'Pop3EndSessionNoQuit', @success OUT

        PRINT 'POP login failed. Check the app password, POP setting, and account policy.'
        EXEC @hr = sp_OADestroy @files
        EXEC @hr = sp_OADestroy @mailman
        EXEC @hr = sp_OADestroy @joined1
        RETURN
      END
    DECLARE @uidls int
    EXEC @hr = sp_OACreate 'Chilkat.StringTable', @uidls OUT

    EXEC sp_OAMethod @mailman, 'FetchUidls', @success OUT, @uidls
    IF @success <> 1
      BEGIN
        EXEC sp_OAMethod @mailman, 'Pop3EndSessionNoQuit', @success OUT

        PRINT 'Could not list POP UIDLs.'
        EXEC @hr = sp_OADestroy @files
        EXEC @hr = sp_OADestroy @mailman
        EXEC @hr = sp_OADestroy @joined1
        EXEC @hr = sp_OADestroy @uidls
        RETURN
      END
    DECLARE @hash int
    EXEC @hr = sp_OACreate 'Chilkat.Crypt2', @hash OUT

    EXEC sp_OASetProperty @hash, 'HashAlgorithm', 'sha256'
    EXEC sp_OASetProperty @hash, 'EncodingMode', 'hex'
    EXEC sp_OASetProperty @hash, 'Charset', 'utf-8'
    DECLARE @mime int
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @mime OUT

    DECLARE @saved int
    SELECT @saved = 0
    DECLARE @skipped int
    SELECT @skipped = 0
    DECLARE @i int
    SELECT @i = 0
    EXEC sp_OAGetProperty @uidls, 'Count', @iTmp0 OUT
    WHILE (@i < @iTmp0)
      BEGIN
        DECLARE @uidl nvarchar(4000)
        EXEC sp_OAMethod @uidls, 'StringAt', @uidl OUT, @i
        --  Never use the opaque server UIDL as a filesystem path.
        DECLARE @joined2 int
        EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @joined2 OUT

        EXEC sp_OAMethod @joined2, 'Append', @success OUT, @mailbox
        EXEC sp_OAMethod @joined2, 'Append', @success OUT, CHAR(10)
        EXEC sp_OAMethod @joined2, 'Append', @success OUT, @uidl
        DECLARE @fileKey nvarchar(4000)
        EXEC sp_OAMethod @joined2, 'GetAsString', @sTmp0 OUT
        EXEC sp_OAMethod @hash, 'HashStringENC', @fileKey OUT, @sTmp0
        EXEC sp_OAGetProperty @hash, 'LastMethodSuccess', @iTmp0 OUT
        IF @iTmp0 <> 1
          BEGIN
            EXEC sp_OAMethod @mailman, 'Pop3EndSessionNoQuit', @success OUT

            PRINT 'Could not construct the local UIDL key.'
            EXEC @hr = sp_OADestroy @files
            EXEC @hr = sp_OADestroy @mailman
            EXEC @hr = sp_OADestroy @joined1
            EXEC @hr = sp_OADestroy @uidls
            EXEC @hr = sp_OADestroy @hash
            EXEC @hr = sp_OADestroy @mime
            EXEC @hr = sp_OADestroy @joined2
            RETURN
          END
        DECLARE @joined3 int
        EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @joined3 OUT

        EXEC sp_OAMethod @joined3, 'Append', @success OUT, 'gmail-pop-mail/'
        EXEC sp_OAMethod @joined3, 'Append', @success OUT, @fileKey
        EXEC sp_OAMethod @joined3, 'Append', @success OUT, '.eml'
        DECLARE @path nvarchar(4000)
        EXEC sp_OAMethod @joined3, 'GetAsString', @path OUT
        DECLARE @exists int
        EXEC sp_OAMethod @files, 'FileExists3', @exists OUT, @path
        IF @exists < 0
          BEGIN
            EXEC sp_OAMethod @mailman, 'Pop3EndSessionNoQuit', @success OUT

            PRINT 'Could not check the local download receipt.'
            EXEC @hr = sp_OADestroy @files
            EXEC @hr = sp_OADestroy @mailman
            EXEC @hr = sp_OADestroy @joined1
            EXEC @hr = sp_OADestroy @uidls
            EXEC @hr = sp_OADestroy @hash
            EXEC @hr = sp_OADestroy @mime
            EXEC @hr = sp_OADestroy @joined2
            EXEC @hr = sp_OADestroy @joined3
            RETURN
          END
        IF @exists = 1
          BEGIN
            SELECT @skipped = @skipped + 1
          END
        ELSE
          BEGIN
            EXEC sp_OAMethod @mailman, 'FetchMimeBd', @success OUT, @uidl, @mime
            IF @success <> 1
              BEGIN
                EXEC sp_OAMethod @mailman, 'Pop3EndSessionNoQuit', @success OUT

                PRINT 'Download failed; the UIDL was not marked complete.'
                EXEC @hr = sp_OADestroy @files
                EXEC @hr = sp_OADestroy @mailman
                EXEC @hr = sp_OADestroy @joined1
                EXEC @hr = sp_OADestroy @uidls
                EXEC @hr = sp_OADestroy @hash
                EXEC @hr = sp_OADestroy @mime
                EXEC @hr = sp_OADestroy @joined2
                EXEC @hr = sp_OADestroy @joined3
                RETURN
              END
            --  A final .eml file is the persistent completion receipt for this UIDL.
            --  Save to a sibling temporary file first. A leftover .part file is not a receipt.
            DECLARE @joined4 int
            EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @joined4 OUT

            EXEC sp_OAMethod @joined4, 'Append', @success OUT, @path
            EXEC sp_OAMethod @joined4, 'Append', @success OUT, '.part'
            EXEC sp_OAMethod @joined4, 'GetAsString', @sTmp0 OUT
            EXEC sp_OAMethod @mime, 'WriteFile', @success OUT, @sTmp0
            IF @success <> 1
              BEGIN
                EXEC sp_OAMethod @mailman, 'Pop3EndSessionNoQuit', @success OUT

                PRINT 'Could not save the complete MIME file.'
                EXEC @hr = sp_OADestroy @files
                EXEC @hr = sp_OADestroy @mailman
                EXEC @hr = sp_OADestroy @joined1
                EXEC @hr = sp_OADestroy @uidls
                EXEC @hr = sp_OADestroy @hash
                EXEC @hr = sp_OADestroy @mime
                EXEC @hr = sp_OADestroy @joined2
                EXEC @hr = sp_OADestroy @joined3
                EXEC @hr = sp_OADestroy @joined4
                RETURN
              END
            DECLARE @joined5 int
            EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @joined5 OUT

            EXEC sp_OAMethod @joined5, 'Append', @success OUT, @path
            EXEC sp_OAMethod @joined5, 'Append', @success OUT, '.part'
            EXEC sp_OAMethod @joined5, 'GetAsString', @sTmp0 OUT
            EXEC sp_OAMethod @files, 'FileRename', @success OUT, @sTmp0, @path
            IF @success <> 1
              BEGIN
                EXEC sp_OAMethod @mailman, 'Pop3EndSessionNoQuit', @success OUT

                PRINT 'Could not commit the downloaded message. Keep the temporary file for diagnosis.'
                EXEC @hr = sp_OADestroy @files
                EXEC @hr = sp_OADestroy @mailman
                EXEC @hr = sp_OADestroy @joined1
                EXEC @hr = sp_OADestroy @uidls
                EXEC @hr = sp_OADestroy @hash
                EXEC @hr = sp_OADestroy @mime
                EXEC @hr = sp_OADestroy @joined2
                EXEC @hr = sp_OADestroy @joined3
                EXEC @hr = sp_OADestroy @joined4
                EXEC @hr = sp_OADestroy @joined5
                RETURN
              END
            SELECT @saved = @saved + 1
          END
        SELECT @i = @i + 1
      END
    --  No DELE commands: leave the messages on Gmail for other clients/recent-mode polls.
    EXEC sp_OAMethod @mailman, 'Pop3EndSession', @success OUT


    PRINT 'Saved: ' + @saved + '  Already saved: ' + @skipped
    IF @success <> 1
      BEGIN

        PRINT 'The local files are committed, but closing the POP session failed.'
      END

    EXEC @hr = sp_OADestroy @files
    EXEC @hr = sp_OADestroy @mailman
    EXEC @hr = sp_OADestroy @joined1
    EXEC @hr = sp_OADestroy @uidls
    EXEC @hr = sp_OADestroy @hash
    EXEC @hr = sp_OADestroy @mime
    EXEC @hr = sp_OADestroy @joined2
    EXEC @hr = sp_OADestroy @joined3
    EXEC @hr = sp_OADestroy @joined4
    EXEC @hr = sp_OADestroy @joined5


END
GO