Fetch Single Email by UID or Sequence Number

Assuming the UID is known, download a single email by UID from an IMAP mail server.

Download Chilkat IMAP ActiveX

CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @sTmp1 nvarchar(4000)
    DECLARE @imap int
    EXEC @hr = sp_OACreate 'Chilkat.Imap', @imap OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int

    --  Anything unlocks the component and begins a fully-functional 30-day trial.
    EXEC sp_OAMethod @imap, 'UnlockComponent', @success OUT, 'Anything for 30-day trial'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        RETURN
      END

    --  Connect to an IMAP server.
    EXEC sp_OAMethod @imap, 'Connect', @success OUT, 'mail.chilkatsoft.com'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        RETURN
      END

    --  Login
    EXEC sp_OAMethod @imap, 'Login', @success OUT, '***', '***'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        RETURN
      END

    --  Select an IMAP mailbox
    EXEC sp_OAMethod @imap, 'SelectMailbox', @success OUT, 'Inbox'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        RETURN
      END

    DECLARE @email int

    DECLARE @uid int

    SELECT @uid = 2014
    DECLARE @isUid int

    SELECT @isUid = 1

    EXEC sp_OAMethod @imap, 'FetchSingle', @email OUT, @uid, @isUid
    IF Not (@email Is NULL )
      BEGIN

        --  Display the From and Subject
        EXEC sp_OAGetProperty @email, 'FromAddress', @sTmp0 OUT

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

        PRINT @sTmp0

        --  Display the Body property, which is the default body.
        --  If an email has an HTML body, the Body property contains
        --  the HTML source.  Otherwise it contains the plain-text
        --  body.

        PRINT '---- EMAIL BODY ----'
        EXEC sp_OAGetProperty @email, 'Body', @sTmp0 OUT

        PRINT @sTmp0

        PRINT '--------------------'

        --  Display the recipients:
        DECLARE @j int

        EXEC sp_OAGetProperty @email, 'NumTo', @iTmp0 OUT
        SELECT @j = 0
        WHILE @j <= @iTmp0 - 1
          BEGIN
            EXEC sp_OAMethod @email, 'GetToName', @sTmp0 OUT, @j

            EXEC sp_OAMethod @email, 'GetToAddr', @sTmp1 OUT, @j
            PRINT @sTmp0 + ', ' + @sTmp1
            SELECT @j = @j + 1
          END
        EXEC sp_OAGetProperty @email, 'NumCC', @iTmp0 OUT
        SELECT @j = 0
        WHILE @j <= @iTmp0 - 1
          BEGIN
            EXEC sp_OAMethod @email, 'GetCcName', @sTmp0 OUT, @j

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

        --  Show the total size of the email, including body and attachments:
        EXEC sp_OAGetProperty @email, 'Size', @iTmp0 OUT

        PRINT @iTmp0

        --  When a full email is downloaded, we would use the
        --  email.NumAttachments property in conjunction with the
        --  email.GetMailAttach* methods.
        --  However, when an email object contains only the header,
        --  we need to access the attachment info differently:
        DECLARE @numAttach int

        EXEC sp_OAMethod @imap, 'GetMailNumAttach', @numAttach OUT, @email

        PRINT STR(@numAttach)

        SELECT @j = 0
        WHILE @j <= @numAttach - 1
          BEGIN
            EXEC sp_OAMethod @imap, 'GetMailAttachFilename', @sTmp0 OUT, @email, @j
            PRINT @sTmp0
            DECLARE @attachSize int

            EXEC sp_OAMethod @imap, 'GetMailAttachSize', @attachSize OUT, @email, @j



            PRINT '    size = ' + STR(@attachSize) + ' bytes'
            SELECT @j = @j + 1
          END


        PRINT '--'

      END

    --  Disconnect from the IMAP server.
    EXEC sp_OAMethod @imap, 'Disconnect', NULL
END
GO