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

Find an email by subject and fetch it by IMAP UID

See more Email Object Examples

Search the selected INBOX by subject and fetch the first matching UID into an Email. The example requests complete attachment bodies and uses peek mode to preserve the read flag. Configure your own IMAP TLS endpoint and credentials.

Chilkat SQL Server Downloads

SQL Server
--
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.

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

    EXEC sp_OASetProperty @imap, 'Ssl', 1
    EXEC sp_OASetProperty @imap, 'Port', 993
    EXEC sp_OASetProperty @imap, 'RequireSslCertVerify', 1
    EXEC sp_OASetProperty @imap, 'PeekMode', 1
    EXEC sp_OASetProperty @imap, 'AutoDownloadAttachments', 1
    EXEC sp_OAMethod @imap, 'Connect', @success OUT, 'imap.example.com'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END
    EXEC sp_OAMethod @imap, 'Login', @success OUT, 'MAIL_USER', 'MAIL_PASSWORD'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END
    EXEC sp_OAMethod @imap, 'SelectMailbox', @success OUT, 'INBOX'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END
    DECLARE @matches int
    EXEC @hr = sp_OACreate 'Chilkat.MessageSet', @matches OUT

    EXEC sp_OAMethod @imap, 'QueryMbx', @success OUT, 'SUBJECT "Sample message"', 1, @matches
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        EXEC @hr = sp_OADestroy @matches
        RETURN
      END
    EXEC sp_OAGetProperty @matches, 'Count', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN

        PRINT 'No matching message in INBOX.'
        EXEC sp_OAMethod @imap, 'Disconnect', @success OUT
        IF @success <> 1
          BEGIN
            EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
            PRINT @sTmp0
            EXEC @hr = sp_OADestroy @imap
            EXEC @hr = sp_OADestroy @matches
            RETURN
          END
        EXEC @hr = sp_OADestroy @imap
        EXEC @hr = sp_OADestroy @matches
        RETURN
      END
    --  The search returned UIDs. Use the same identifier type for the fetch.
    DECLARE @uid int
    EXEC sp_OAMethod @matches, 'GetId', @uid OUT, 0
    DECLARE @email int
    EXEC @hr = sp_OACreate 'Chilkat.Email', @email OUT

    EXEC sp_OAMethod @imap, 'FetchEmail', @success OUT, 0, @uid, 1, @email
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        EXEC @hr = sp_OADestroy @matches
        EXEC @hr = sp_OADestroy @email
        RETURN
      END

    EXEC sp_OAGetProperty @imap, 'UidValidity', @iTmp0 OUT

    PRINT 'Mailbox INBOX, UIDVALIDITY ' + @iTmp0 + ', UID ' + @uid
    EXEC sp_OAMethod @imap, 'Disconnect', @success OUT
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        EXEC @hr = sp_OADestroy @matches
        EXEC @hr = sp_OADestroy @email
        RETURN
      END
    --  The downloaded Email remains usable after disconnecting.

    EXEC sp_OAGetProperty @email, 'Subject', @sTmp0 OUT

    EXEC sp_OAGetProperty @email, 'NumAttachments', @iTmp0 OUT
    PRINT 'Subject: ' + @sTmp0 + '  Files: ' + @iTmp0

    EXEC sp_OAMethod @email, 'SaveEml', @success OUT, 'imap-received.eml'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @email, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        EXEC @hr = sp_OADestroy @matches
        EXEC @hr = sp_OADestroy @email
        RETURN
      END

    PRINT 'Saved imap-received.eml.'

    EXEC @hr = sp_OADestroy @imap
    EXEC @hr = sp_OADestroy @matches
    EXEC @hr = sp_OADestroy @email


END
GO