SQL Server

IMAP Copy Email from Selected Folder to Another Folder

Copies an email from the selected mailbox (folder) to another mailbox (folder). 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 @iTmp1 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.

    -- This example copies an email from one mailbox to another.
    DECLARE @imap int
    EXEC @hr = sp_OACreate 'Chilkat.Imap', @imap OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Turn on session logging for debugging.
    EXEC sp_OASetProperty @imap, 'KeepSessionLog', 1

    -- Connect using TLS.
    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
    EXEC sp_OAMethod @imap, 'Login', @success OUT, 'email_account_login', 'email_account_password'
    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

    -- Search for messages having "Test email" in the subject.
    DECLARE @fetchUids int
    SELECT @fetchUids = 1
    DECLARE @criteria nvarchar(4000)
    SELECT @criteria = 'SUBJECT "Test email"'
    DECLARE @messageSet int
    EXEC @hr = sp_OACreate 'Chilkat.MessageSet', @messageSet OUT

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

    -- Assuming we have 1 or more messages in the set, copy the 1st one to the folder "Inbox.testFolder"
    EXEC sp_OAGetProperty @messageSet, 'Count', @iTmp0 OUT
    IF @iTmp0 > 0
      BEGIN
        EXEC sp_OAMethod @messageSet, 'GetId', @iTmp0 OUT, 0
        EXEC sp_OAGetProperty @messageSet, 'HasUids', @iTmp1 OUT
        EXEC sp_OAMethod @imap, 'Copy', @success OUT, @iTmp0, @iTmp1, 'Inbox/testFolder'
        IF @success <> 1
          BEGIN
            EXEC sp_OAGetProperty @imap, 'LastErrorText', @sTmp0 OUT
            PRINT @sTmp0
          END
      END

    EXEC sp_OAGetProperty @imap, 'SessionLog', @sTmp0 OUT
    PRINT @sTmp0


    PRINT 'OK'

    -- Sample sessiong log:

    -- ----INFO----
    -- Connecting to IMAP server at outlook.office365.com:993
    -- ----IMAP RESPONSE----
    -- * OK The Microsoft Exchange IMAP4 service is ready. [QgBMADAAUABSADAAMQAwADIAQwBBADAAMAA3ADAALgBwAHIAbwBkAC4AZQB4AGMAaABhAG4AZwBlAGwAYQBiAHMALgBjAG8AbQA=]
    -- ----IMAP REQUEST----
    -- aaab LOGIN "chilkat@live.com"
    -- ----IMAP RESPONSE----
    -- aaab OK LOGIN completed.
    -- ----IMAP REQUEST----
    -- aaac SELECT "Inbox"
    -- ----IMAP RESPONSE----
    -- * 28 EXISTS
    -- * 0 RECENT
    -- * FLAGS (\Seen \Answered \Flagged \Deleted \Draft $MDNSent)
    -- * OK [PERMANENTFLAGS (\Seen \Answered \Flagged \Deleted \Draft $MDNSent)] Permanent flags
    -- * OK [UNSEEN 23] Is the first unseen message
    -- * OK [UIDVALIDITY 14] UIDVALIDITY value
    -- * OK [UIDNEXT 6416] The next unique identifier value
    -- aaac OK [READ-WRITE] SELECT completed.
    -- ----IMAP REQUEST----
    -- aaad UID SEARCH SUBJECT "Test email"
    -- ----IMAP RESPONSE----
    -- * SEARCH 6414
    -- aaad OK SEARCH completed.
    -- ----IMAP REQUEST----
    -- aaae UID COPY 6414 "Inbox/testFolder"
    -- ----IMAP RESPONSE----
    -- aaae OK [COPYUID 180 6414 1] COPY completed.
    -- 
    -- OK

    EXEC @hr = sp_OADestroy @imap
    EXEC @hr = sp_OADestroy @messageSet


END
GO