(SQL Server) Read IMAP Email Headers

This example demonstrates how to connect to an IMAP server and download only the email headers for all messages in a mailbox. Downloading headers only is much faster and more efficient than downloading complete emails because the message bodies and attachment contents are not retrieved.

The example shows how to:

• Connect to an IMAP server using SSL/TLS
• Authenticate and select a mailbox
• Query for all messages in the mailbox
• Fetch header-only email information
• Display sender, subject, recipients, and total message size
• Retrieve attachment metadata such as filenames and sizes without downloading the actual attachments

This technique is useful for quickly scanning large mailboxes, building message summaries, or inspecting attachment information while minimizing bandwidth and memory usage.

Note: This example requires Chilkat v11.0.0 or greater.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- 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 @sTmp1 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    SELECT @success = 0

    -- This example assumes the Chilkat API has already been unlocked.
    -- See Global Unlock Sample for example 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 the IMAP server using SSL/TLS on the standard secure IMAP port.
    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

    -- Authenticate with the IMAP server.
    EXEC sp_OAMethod @imap, 'Login', @success OUT, '****', '****'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END

    -- Select the mailbox (folder) to access.
    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

    -- Get the identifiers for all messages in the selected mailbox.
    -- Setting fetchUids = cktrue causes IMAP UIDs to be returned.
    -- If ckfalse, IMAP sequence numbers are returned instead.
    DECLARE @fetchUids int
    SELECT @fetchUids = 1
    DECLARE @messageSet int
    EXEC @hr = sp_OACreate 'Chilkat.MessageSet', @messageSet OUT

    EXEC sp_OAMethod @imap, 'QueryMbx', @success OUT, 'ALL', @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

    -- Download only the email headers for the messages in the set.
    -- This is much faster than downloading full emails because the
    -- message bodies and attachment contents are not retrieved.
    -- 
    -- Even though the attachments themselves are not downloaded,
    -- Chilkat can still provide information such as attachment
    -- filenames, attachment sizes, and the total message size.
    DECLARE @bundle int
    EXEC @hr = sp_OACreate 'Chilkat.EmailBundle', @bundle OUT

    DECLARE @headersOnly int
    SELECT @headersOnly = 1
    EXEC sp_OAMethod @imap, 'FetchMsgSet', @success OUT, @headersOnly, @messageSet, @bundle
    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 @bundle
        RETURN
      END

    -- Iterate over each email in the bundle and display summary information.
    DECLARE @email int
    EXEC @hr = sp_OACreate 'Chilkat.Email', @email OUT

    DECLARE @name nvarchar(4000)

    DECLARE @addr nvarchar(4000)

    DECLARE @i int
    SELECT @i = 0
    DECLARE @j int
    SELECT @j = 0
    EXEC sp_OAGetProperty @bundle, 'MessageCount', @iTmp0 OUT
    WHILE @i < @iTmp0
      BEGIN
        EXEC sp_OAMethod @bundle, 'EmailAt', @success OUT, @i, @email

        -- Display the sender and subject line.
        EXEC sp_OAGetProperty @email, 'From', @sTmp0 OUT
        PRINT @sTmp0
        EXEC sp_OAGetProperty @email, 'Subject', @sTmp0 OUT
        PRINT @sTmp0

        -- Display all "To" recipients.
        SELECT @j = 0
        EXEC sp_OAGetProperty @email, 'NumTo', @iTmp0 OUT
        WHILE @j < @iTmp0
          BEGIN

            EXEC sp_OAMethod @email, 'GetToName', @sTmp0 OUT, @j

            EXEC sp_OAMethod @email, 'GetToAddr', @sTmp1 OUT, @j
            PRINT 'TO: ' + @sTmp0 + ', ' + @sTmp1
            SELECT @j = @j + 1
          END

        -- Display all "CC" recipients.
        SELECT @j = 0
        EXEC sp_OAGetProperty @email, 'NumCC', @iTmp0 OUT
        WHILE @j < @iTmp0
          BEGIN

            EXEC sp_OAMethod @email, 'GetCcName', @sTmp0 OUT, @j

            EXEC sp_OAMethod @email, 'GetCcAddr', @sTmp1 OUT, @j
            PRINT 'CC: ' + @sTmp0 + ', ' + @sTmp1
            SELECT @j = @j + 1
          END

        -- Display the total size of the email message, including
        -- the body and all attachments, even though only headers
        -- were downloaded.
        EXEC sp_OAGetProperty @email, 'Size', @iTmp0 OUT
        PRINT @iTmp0

        -- When full emails are downloaded, attachment information
        -- is accessed using the email.NumAttachments property and
        -- the email.GetMailAttach* methods.
        -- 
        -- However, because this example downloaded headers only,
        -- attachment metadata must be obtained using the IMAP object.
        DECLARE @numAttach int
        EXEC sp_OAMethod @imap, 'GetMailNumAttach', @numAttach OUT, @email

        SELECT @j = 0
        WHILE @j < @numAttach
          BEGIN

            -- Display the attachment filename.
            EXEC sp_OAMethod @imap, 'GetMailAttachFilename', @sTmp0 OUT, @email, @j
            PRINT @sTmp0

            -- Display the attachment size in bytes.
            DECLARE @attachSize int
            EXEC sp_OAMethod @imap, 'GetMailAttachSize', @attachSize OUT, @email, @j


            PRINT '    size = ' + @attachSize + ' bytes'

            SELECT @j = @j + 1
          END


        PRINT '--'

        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 @bundle
    EXEC @hr = sp_OADestroy @email


END
GO