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

Gmail IMAP: Archive a Message or Move It to Trash

See more GMail SMTP/IMAP/POP Examples

This example changes one message in INBOX. Choose archive to move it to the discovered All Mail folder, or trash to move it to the discovered Trash folder. It resolves a decimal Gmail message ID to a mailbox UID and uses MoveMessages with the server's MOVE capability.

Archiving removes a message from INBOX while retaining it in All Mail. Moving it to Trash is a different action; Gmail normally permanently deletes messages after 30 days there. This example does not empty Trash or permanently delete mail. A message can retain other custom labels when archived.

Folder names are discovered by special-use attributes. Avoid a generic delete/EXPUNGE sequence whose Gmail outcome can depend on account settings and which can affect unrelated messages. If a connection fails during a move, inspect the source and destination before retrying. A concurrent move or deletion can make the UID disappear.

Use the mailbox that authorized the full-mail scope https://mail.google.com/. Obtain the protocol OAuth token and refresh it before running this example. The token is loaded from gmail-mail-token.json in a private working directory; a narrow Gmail REST token is insufficient.

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

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

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

    EXEC sp_OAMethod @token, 'LoadFile', @success OUT, 'gmail-mail-token.json'
    IF @success <> 1
      BEGIN

        PRINT 'Could not load the protocol token from the private working directory.'
        EXEC @hr = sp_OADestroy @token
        RETURN
      END
    EXEC sp_OAMethod @token, 'JsonTypeOf', @iTmp0 OUT, 'access_token'
    EXEC sp_OAMethod @token, 'StringOfEquals', @iTmp1 OUT, 'access_token', '', 1
    IF (@iTmp0 <> 1) or (@iTmp1 = 1)
      BEGIN

        PRINT 'Authorize or refresh the mailbox first.'
        EXEC @hr = sp_OADestroy @token
        RETURN
      END
    DECLARE @mailbox nvarchar(4000)
    SELECT @mailbox = 'user@example.com'
    DECLARE @imap int
    EXEC @hr = sp_OACreate 'Chilkat.Imap', @imap OUT

    EXEC sp_OASetProperty @imap, 'Ssl', 1
    EXEC sp_OASetProperty @imap, 'Port', 993
    EXEC sp_OASetProperty @imap, 'RequireSslCertVerify', 1
    EXEC sp_OASetProperty @imap, 'AuthMethod', 'XOAUTH2'
    EXEC sp_OAMethod @imap, 'Connect', @success OUT, 'imap.gmail.com'
    IF @success <> 1
      BEGIN

        PRINT 'Could not establish the Gmail IMAP TLS connection.'
        EXEC @hr = sp_OADestroy @token
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END
    EXEC sp_OAMethod @token, 'StringOf', @sTmp0 OUT, 'access_token'
    EXEC sp_OAMethod @imap, 'Login', @success OUT, @mailbox, @sTmp0
    IF @success <> 1
      BEGIN
        EXEC sp_OAMethod @imap, 'Disconnect', @success OUT

        PRINT 'IMAP authentication failed. Check the mailbox, scope, expiry, and account policy.'
        EXEC @hr = sp_OADestroy @token
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END
    DECLARE @capabilities nvarchar(4000)
    EXEC sp_OAMethod @imap, 'Capability', @capabilities OUT
    EXEC sp_OAGetProperty @imap, 'LastMethodSuccess', @iTmp0 OUT
    EXEC sp_OAMethod @imap, 'HasCapability', @iTmp1 OUT, 'X-GM-EXT-1', @capabilities
    IF (@iTmp0 <> 1) or (@iTmp1 <> 1)
      BEGIN
        EXEC sp_OAMethod @imap, 'Logout', @success OUT
        EXEC sp_OAMethod @imap, 'Disconnect', @success OUT

        PRINT 'Gmail IMAP extensions are unavailable.'
        EXEC @hr = sp_OADestroy @token
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END
    --  Choose archive or trash. This example changes one message's location.
    DECLARE @action nvarchar(4000)
    SELECT @action = 'archive'
    IF (@action <> 'archive') and (@action <> 'trash')
      BEGIN
        EXEC sp_OAMethod @imap, 'Logout', @success OUT
        EXEC sp_OAMethod @imap, 'Disconnect', @success OUT

        PRINT 'Choose archive or trash.'
        EXEC @hr = sp_OADestroy @token
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END
    EXEC sp_OAMethod @imap, 'HasCapability', @iTmp0 OUT, 'MOVE', @capabilities
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAMethod @imap, 'Logout', @success OUT
        EXEC sp_OAMethod @imap, 'Disconnect', @success OUT

        PRINT 'The server must support IMAP MOVE.'
        EXEC @hr = sp_OADestroy @token
        EXEC @hr = sp_OADestroy @imap
        RETURN
      END
    DECLARE @folders int
    EXEC @hr = sp_OACreate 'Chilkat.Mailboxes', @folders OUT

    EXEC sp_OAMethod @imap, 'MbxList', @success OUT, 0, '', '*', @folders
    IF @success <> 1
      BEGIN
        EXEC sp_OAMethod @imap, 'Logout', @success OUT
        EXEC sp_OAMethod @imap, 'Disconnect', @success OUT

        PRINT 'Could not list Gmail folders.'
        EXEC @hr = sp_OADestroy @token
        EXEC @hr = sp_OADestroy @imap
        EXEC @hr = sp_OADestroy @folders
        RETURN
      END
    DECLARE @role nvarchar(4000)
    SELECT @role = '\All'
    IF @action = 'trash'
      BEGIN
        SELECT @role = '\Trash'
      END
    DECLARE @destination nvarchar(4000)
    SELECT @destination = ''
    DECLARE @count int
    SELECT @count = 0
    DECLARE @i int
    SELECT @i = 0
    EXEC sp_OAGetProperty @folders, 'Count', @iTmp0 OUT
    WHILE (@i < @iTmp0)
      BEGIN
        EXEC sp_OAMethod @folders, 'HasFlag', @iTmp0 OUT, @i, @role
        EXEC sp_OAMethod @folders, 'IsSelectable', @iTmp1 OUT, @i
        IF (@iTmp0 = 1) and (@iTmp1 = 1)
          BEGIN
            EXEC sp_OAMethod @folders, 'GetName', @destination OUT, @i
            SELECT @count = @count + 1
          END
        SELECT @i = @i + 1
      END
    IF @count <> 1
      BEGIN
        EXEC sp_OAMethod @imap, 'Logout', @success OUT
        EXEC sp_OAMethod @imap, 'Disconnect', @success OUT

        PRINT 'Could not identify the destination folder uniquely.'
        EXEC @hr = sp_OADestroy @token
        EXEC @hr = sp_OADestroy @imap
        EXEC @hr = sp_OADestroy @folders
        RETURN
      END
    EXEC sp_OAMethod @imap, 'SelectMailbox', @success OUT, 'INBOX'
    IF @success <> 1
      BEGIN
        EXEC sp_OAMethod @imap, 'Logout', @success OUT
        EXEC sp_OAMethod @imap, 'Disconnect', @success OUT

        PRINT 'Could not open INBOX for the move.'
        EXEC @hr = sp_OADestroy @token
        EXEC @hr = sp_OADestroy @imap
        EXEC @hr = sp_OADestroy @folders
        RETURN
      END
    --  Obtain this decimal string from the Gmail message/thread ID example.
    DECLARE @gmailMessageId nvarchar(4000)
    SELECT @gmailMessageId = '1278455344230334865'
    DECLARE @idText int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @idText OUT

    EXEC sp_OAMethod @idText, 'Append', @success OUT, @gmailMessageId
    DECLARE @validation int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @validation OUT

    DECLARE @matches int
    EXEC sp_OAMethod @idText, 'RegexMatch', @matches OUT, '\A[1-9][0-9]{0,19}\z', @validation, 1000
    IF @matches <> 1
      BEGIN
        EXEC sp_OAMethod @imap, 'Logout', @success OUT
        EXEC sp_OAMethod @imap, 'Disconnect', @success OUT

        PRINT 'Expected a decimal Gmail message ID string.'
        EXEC @hr = sp_OADestroy @token
        EXEC @hr = sp_OADestroy @imap
        EXEC @hr = sp_OADestroy @folders
        EXEC @hr = sp_OADestroy @idText
        EXEC @hr = sp_OADestroy @validation
        RETURN
      END
    DECLARE @target int
    EXEC @hr = sp_OACreate 'Chilkat.MessageSet', @target OUT

    DECLARE @joined1 int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @joined1 OUT

    EXEC sp_OAMethod @joined1, 'Append', @success OUT, 'X-GM-MSGID '
    EXEC sp_OAMethod @joined1, 'Append', @success OUT, @gmailMessageId
    EXEC sp_OAMethod @joined1, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @imap, 'QueryMbx', @success OUT, @sTmp0, 1, @target
    IF @success <> 1
      BEGIN
        EXEC sp_OAMethod @imap, 'Logout', @success OUT
        EXEC sp_OAMethod @imap, 'Disconnect', @success OUT

        PRINT 'Could not find the Gmail message in the selected mailbox.'
        EXEC @hr = sp_OADestroy @token
        EXEC @hr = sp_OADestroy @imap
        EXEC @hr = sp_OADestroy @folders
        EXEC @hr = sp_OADestroy @idText
        EXEC @hr = sp_OADestroy @validation
        EXEC @hr = sp_OADestroy @target
        EXEC @hr = sp_OADestroy @joined1
        RETURN
      END
    EXEC sp_OAGetProperty @target, 'Count', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAMethod @imap, 'Logout', @success OUT
        EXEC sp_OAMethod @imap, 'Disconnect', @success OUT

        EXEC sp_OAGetProperty @target, 'Count', @iTmp0 OUT
        PRINT 'Expected one message in the selected mailbox. Matches: ' + @iTmp0
        EXEC @hr = sp_OADestroy @token
        EXEC @hr = sp_OADestroy @imap
        EXEC @hr = sp_OADestroy @folders
        EXEC @hr = sp_OADestroy @idText
        EXEC @hr = sp_OADestroy @validation
        EXEC @hr = sp_OADestroy @target
        EXEC @hr = sp_OADestroy @joined1
        RETURN
      END
    DECLARE @uid nvarchar(4000)
    EXEC sp_OAMethod @target, 'ToCompactString', @uid OUT
    --  MoveMessages uses UID MOVE and maintains the Imap object's mailbox state.
    --  No broad EXPUNGE is issued, and unrelated deleted flags are not committed.
    EXEC sp_OAMethod @imap, 'MoveMessages', @success OUT, @target, @destination
    IF @success <> 1
      BEGIN
        EXEC sp_OAMethod @imap, 'Logout', @success OUT
        EXEC sp_OAMethod @imap, 'Disconnect', @success OUT

        PRINT 'Move failed; verify the mailbox state before retrying after a connection loss.'
        EXEC @hr = sp_OADestroy @token
        EXEC @hr = sp_OADestroy @imap
        EXEC @hr = sp_OADestroy @folders
        EXEC @hr = sp_OADestroy @idText
        EXEC @hr = sp_OADestroy @validation
        EXEC @hr = sp_OADestroy @target
        EXEC @hr = sp_OADestroy @joined1
        RETURN
      END


    PRINT 'Completed ' + @action + ' for Gmail message ' + @gmailMessageId
    EXEC sp_OAMethod @imap, 'Logout', @success OUT
    EXEC sp_OAMethod @imap, 'Disconnect', @success OUT

    EXEC @hr = sp_OADestroy @token
    EXEC @hr = sp_OADestroy @imap
    EXEC @hr = sp_OADestroy @folders
    EXEC @hr = sp_OADestroy @idText
    EXEC @hr = sp_OADestroy @validation
    EXEC @hr = sp_OADestroy @target
    EXEC @hr = sp_OADestroy @joined1


END
GO