(SQL Server) Outlook -- List Mail Folders

See more Outlook Examples

Uses the Microsoft Graph API to list Outlook mail folders.

Note: This example requires Chilkat v9.5.0.67 or greater.

This example applies to: Exchange Online | Office 365 | Hotmail.com | Live.com | MSN.com | Outlook.com | Passport.com

For more information, see https://docs.microsoft.com/en-us/graph/api/user-list-mailfolders?view=graph-rest-1.0&tabs=http

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)
    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @http int
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Use your previously obtained access token here:
    -- See the following examples for getting an access token:
    --    Get Microsoft Graph OAuth2 Access Token (Azure AD v2.0 Endpoint).
    --    Get Microsoft Graph OAuth2 Access Token (Azure AD Endpoint).
    --    Refresh Access Token (Azure AD v2.0 Endpoint).
    --    Refresh Access Token (Azure AD Endpoint).

    EXEC sp_OASetProperty @http, 'AuthToken', 'MICROSOFT_GRAPH_ACCESS_TOKEN'

    DECLARE @sbResponse int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbResponse OUT

    -- Sends:  GET /users/{id | userPrincipalName}/mailFolders
    -- Note: It is also possible to use the literal string "me" for the current logged-on user.
    -- For example: GET /me/mailFolders
    EXEC sp_OAMethod @http, 'ClearUrlVars', NULL
    DECLARE @success int
    EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'userPrincipalName', 'chilkatsoft@outlook.com'
    DECLARE @success int
    EXEC sp_OAMethod @http, 'QuickGetSb', @success OUT, 'https://graph.microsoft.com/v1.0/users/{$userPrincipalName}/mailFolders', @sbResponse
    EXEC sp_OAGetProperty @http, 'LastStatus', @iTmp0 OUT
    IF (@success <> 1) and (@iTmp0 = 0)
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @sbResponse
        RETURN
      END

    DECLARE @json int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    EXEC sp_OAMethod @json, 'LoadSb', @success OUT, @sbResponse
    EXEC sp_OASetProperty @json, 'EmitCompact', 0


    EXEC sp_OAGetProperty @http, 'LastStatus', @iTmp0 OUT
    PRINT 'Status code = ' + @iTmp0
    EXEC sp_OAGetProperty @http, 'LastStatus', @iTmp0 OUT
    IF @iTmp0 <> 200
      BEGIN
        EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
        PRINT @sTmp0

        PRINT 'Failed.'
      END

    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    PRINT '----'

    -- In my test case, this is the result (see below)
    -- Apparently, this lists folders without listing subfolders (aka child folders)
    -- There's another REST API call to list child folders.

    -- To iterate over the folders:
    DECLARE @i int
    SELECT @i = 0
    DECLARE @numFolders int
    EXEC sp_OAMethod @json, 'SizeOfArray', @numFolders OUT, 'value'
    WHILE @i < @numFolders
      BEGIN
        EXEC sp_OASetProperty @json, 'I', @i
        EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'value[i].displayName'
        PRINT @sTmp0
        SELECT @i = @i + 1
      END

    -- The output from the above loop:

    -- 	Archive
    -- 	Deleted Items
    -- 	Drafts
    -- 	Inbox
    -- 	Junk Email
    -- 	Outbox
    -- 	Sent Items

    -- ------------------------------------------------------------------------------------
    -- The raw JSON response: 

    -- {
    --   "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#users('chilkatsoft%40outlook.com')/mailFolders",
    --   "value": [
    --     {
    --       "id": "AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAG8XunwAAAA=",
    --       "displayName": "Archive",
    --       "parentFolderId": "AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAgEIAAAA",
    --       "childFolderCount": 0,
    --       "unreadItemCount": 0,
    --       "totalItemCount": 0
    --     },
    --     {
    --       "id": "AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAgEKAAAA",
    --       "displayName": "Deleted Items",
    --       "parentFolderId": "AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAgEIAAAA",
    --       "childFolderCount": 0,
    --       "unreadItemCount": 0,
    --       "totalItemCount": 0
    --     },
    --     {
    --       "id": "AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAgEPAAAA",
    --       "displayName": "Drafts",
    --       "parentFolderId": "AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAgEIAAAA",
    --       "childFolderCount": 0,
    --       "unreadItemCount": 0,
    --       "totalItemCount": 0
    --     },
    --     {
    --       "id": "AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAgEMAAAA",
    --       "displayName": "Inbox",
    --       "parentFolderId": "AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAgEIAAAA",
    --       "childFolderCount": 2,
    --       "unreadItemCount": 0,
    --       "totalItemCount": 4
    --     },
    --     {
    --       "id": "AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAgEiAAAA",
    --       "displayName": "Junk Email",
    --       "parentFolderId": "AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAgEIAAAA",
    --       "childFolderCount": 0,
    --       "unreadItemCount": 0,
    --       "totalItemCount": 0
    --     },
    --     {
    --       "id": "AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAgELAAAA",
    --       "displayName": "Outbox",
    --       "parentFolderId": "AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAgEIAAAA",
    --       "childFolderCount": 0,
    --       "unreadItemCount": 0,
    --       "totalItemCount": 0
    --     },
    --     {
    --       "id": "AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAgEJAAAA",
    --       "displayName": "Sent Items",
    --       "parentFolderId": "AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAgEIAAAA",
    --       "childFolderCount": 0,
    --       "unreadItemCount": 0,
    --       "totalItemCount": 14
    --     }
    --   ]
    -- }
    -- 

    -- ------------------------------------------------------------------------------------------------------
    -- This example applies to: Exchange Online | Office 365 | Hotmail.com | Live.com | MSN.com | Outlook.com | Passport.com
    -- 
    -- The Microsoft Graph Outlook Mail API lets you read, create, and send messages and attachments, 
    -- view and respond to event messages, and manage folders that are secured by Azure Active Directory 
    -- in Office 365. It also provides the same functionality in Microsoft accounts specifically 
    -- in these domains: Hotmail.com, Live.com, MSN.com, Outlook.com, and Passport.com.

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @sbResponse
    EXEC @hr = sp_OADestroy @json


END
GO