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

SQL Server

--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    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.

    DECLARE @http int
    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
    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
    EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'userPrincipalName', 'chilkatsoft@outlook.com'
    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
    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