SQL Server
SQL Server
Outlook -- Search Multiple Folders
See more Outlook Examples
I don't yet see how it's possible to do a recursive search of Outlook folders using the Microsoft Graph API. My best guess is to somehow use OData v4.0's $expand query option, but the hierarchical structure of the "mailFolder" and "messages" Microsoft Graph resources don't quite fit. Suggestions are welcome and can be sent to support@chilkatsoft.com.This example will iterate over a list of folder previously obtained by a recursive traversal of the Outlook mail folders. (See the link in the example code below.)
A separate search is performed on each desired folder, and the results are combined into a single result set.
Note: This example requires Chilkat v9.5.0.68 or greater.
This example applies to: Exchange Online | Office 365 | Hotmail.com | Live.com | MSN.com | Outlook.com | Passport.com
Chilkat SQL Server Downloads
-- 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)
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'
-- This example will iterate over the folders previously discovered by recursively traversing the Outlook folders
-- as shown in this example: Outlook Recursive Folder Traversal)
-- The XML map produced by the recursive traversal looks like this:
-- <?xml version="1.0" encoding="utf-8"?>
-- <hashtable>
-- <e><k>/Sent Items</k><v>AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAgEJAAAA</v></e>
-- <e><k>/Inbox</k><v>AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAgEMAAAA</v></e>
-- <e><k>/Junk Email</k><v>AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAgEiAAAA</v></e>
-- <e><k>/Inbox/xyz</k><v>AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAL8huwEAAAA=</v></e>
-- <e><k>/Inbox/abc/subFolderA/a</k><v>AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAL8huwIAAAA=</v></e>
-- <e><k>/Inbox/abc</k><v>AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAL8huv8AAAA=</v></e>
-- <e><k>/Outbox</k><v>AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAgELAAAA</v></e>
-- <e><k>/Inbox/abc/subFolderA</k><v>AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAL8huwAAAQ==</v></e>
-- <e><k>/Inbox/abc/subFolderB</k><v>AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAL8huwMAAAA=</v></e>
-- <e><k>/Archive</k><v>AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAG8XunwAAAA=</v></e>
-- <e><k>/Deleted Items</k><v>AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAgEKAAAA</v></e>
-- <e><k>/Drafts</k><v>AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAgEPAAAA</v></e>
-- </hashtable>
-- We'll iterate over the folders, and search all folders beginning with "/Inbox" (effectively, we're recursively searching
-- everything under Inbox)
DECLARE @xmlMap int
EXEC @hr = sp_OACreate 'Chilkat.Xml', @xmlMap OUT
EXEC sp_OAMethod @xmlMap, 'LoadXmlFile', @success OUT, 'qa_data/outlook/folderMap.xml'
IF @success <> 1
BEGIN
PRINT 'Failed to load XML folder map.'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @xmlMap
RETURN
END
-- We're going to return just the message id, subject, and FROM name/address.
EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'select', 'id,subject,from'
-- Our search will be for emails with the word "sample" in the subject.
EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'filter', 'contains(subject,''sample'')'
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
EXEC sp_OASetProperty @json, 'EmitCompact', 0
DECLARE @jsonCombined int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonCombined OUT
EXEC sp_OASetProperty @jsonCombined, 'EmitCompact', 0
DECLARE @sbResponse int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbResponse OUT
DECLARE @sbPath int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbPath OUT
DECLARE @numFolders int
EXEC sp_OAGetProperty @xmlMap, 'NumChildren', @numFolders OUT
DECLARE @i int
SELECT @i = 0
DECLARE @j int
SELECT @j = 0
DECLARE @k int
SELECT @k = 0
WHILE @i < @numFolders
BEGIN
EXEC sp_OASetProperty @xmlMap, 'I', @i
EXEC sp_OAMethod @xmlMap, 'GetChildContent', @sTmp0 OUT, 'e[i]|k'
EXEC sp_OAMethod @sbPath, 'SetString', @success OUT, @sTmp0
EXEC sp_OAMethod @sbPath, 'StartsWith', @iTmp0 OUT, '/Inbox', 1
IF @iTmp0 = 1
BEGIN
PRINT '------------------------------------------------------------------'
EXEC sp_OAMethod @sbPath, 'GetAsString', @sTmp0 OUT
PRINT 'Searching ' + @sTmp0
-- Search this mail folder..
EXEC sp_OAMethod @xmlMap, 'GetChildContent', @sTmp0 OUT, 'e[i]|v'
EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'folder_id', @sTmp0
EXEC sp_OAMethod @sbResponse, 'Clear', NULL
EXEC sp_OAMethod @http, 'QuickGetSb', @success OUT, 'https://graph.microsoft.com/v1.0/me/mailFolders/{$folder_id}/messages?$filter={$filter}&$select={$select}', @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 @xmlMap
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @jsonCombined
EXEC @hr = sp_OADestroy @sbResponse
EXEC @hr = sp_OADestroy @sbPath
RETURN
END
EXEC sp_OAMethod @json, 'LoadSb', @success OUT, @sbResponse
EXEC sp_OAGetProperty @http, 'LastStatus', @iTmp0 OUT
IF @iTmp0 <> 200
BEGIN
EXEC sp_OAGetProperty @http, 'LastStatus', @iTmp0 OUT
PRINT 'HTTP response status = ' + @iTmp0
EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
PRINT @sTmp0
PRINT 'Failed.'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @xmlMap
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @jsonCombined
EXEC @hr = sp_OADestroy @sbResponse
EXEC @hr = sp_OADestroy @sbPath
RETURN
END
EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
PRINT @sTmp0
-- Each mail folder search will return JSON with a value array, which is non-empty if any matching messages were found:
-- {
-- "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#users('me')/mailFolders('AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAgEMAAAA')/messages(id,subject,from)",
-- "value": [
-- Individual messages, if any, are listed here.
-- ]
-- }
DECLARE @numMessages int
EXEC sp_OAMethod @json, 'SizeOfArray', @numMessages OUT, 'value'
SELECT @j = 0
WHILE @j < @numMessages
BEGIN
EXEC sp_OASetProperty @json, 'J', @j
-- Add this message to the final result set.
EXEC sp_OASetProperty @jsonCombined, 'K', @k
EXEC sp_OAMethod @sbPath, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @jsonCombined, 'UpdateString', @success OUT, 'value[k].folderPath', @sTmp0
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'value[j].id'
EXEC sp_OAMethod @jsonCombined, 'UpdateString', @success OUT, 'value[k].id', @sTmp0
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'value[j].subject'
EXEC sp_OAMethod @jsonCombined, 'UpdateString', @success OUT, 'value[k].subject', @sTmp0
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'value[j].from.emailAddress.name'
EXEC sp_OAMethod @jsonCombined, 'UpdateString', @success OUT, 'value[k].from.emailAddress.name', @sTmp0
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'value[j].from.emailAddress.address'
EXEC sp_OAMethod @jsonCombined, 'UpdateString', @success OUT, 'value[k].from.emailAddress.address', @sTmp0
SELECT @k = @k + 1
SELECT @j = @j + 1
END
END
SELECT @i = @i + 1
END
-- Show the final combined JSON search result.
PRINT '------------------------------------------------------------------'
PRINT 'Combined Search Results:'
EXEC sp_OAMethod @jsonCombined, 'Emit', @sTmp0 OUT
PRINT @sTmp0
-- Sample output for the above program:
-- ------------------------------------------------------------------
-- Searching /Inbox
-- {
-- "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#users('me')/mailFolders('AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAgEMAAAA')/messages(id,subject,from)",
-- "value": [
-- {
-- "@odata.etag": "W/\"CQAAABYAAADn68XtMop0TpsYJGpfKXY9AADOpwfr\"",
-- "id": "AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgBGAAADsVyfxjDU406Ic4X7ill8xAcA5_vF7TKKdE6bGCRqXyl2PQAAAgEMAAAA5_vF7TKKdE6bGCRqXyl2PQAAAM6Jj10AAAA=",
-- "subject": "A sample email with Amazon in the body",
-- "from": {
-- "emailAddress": {
-- "name": "Chilkat Software",
-- "address": "support@chilkatsoft.com"
-- }
-- }
-- }
-- ]
-- }
--
-- ------------------------------------------------------------------
-- Searching /Inbox/xyz
-- {
-- "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#users('me')/mailFolders('AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAL8huwEAAAA%3D')/messages(id,subject,from)",
-- "value": [
-- ]
-- }
--
-- ------------------------------------------------------------------
-- Searching /Inbox/abc/subFolderA/a
-- {
-- "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#users('me')/mailFolders('AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAL8huwIAAAA%3D')/messages(id,subject,from)",
-- "value": [
-- {
-- "@odata.etag": "W/\"CQAAABYAAADn68XtMop0TpsYJGpfKXY9AADOpzfb\"",
-- "id": "AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgBGAAADsVyfxjDU406Ic4X7ill8xAcA5_vF7TKKdE6bGCRqXyl2PQAAAL8huwIAAADn68XtMop0TpsYJGpfKXY9AAAAzombFQAAAA==",
-- "subject": "Sample email from admin@chilkat.io",
-- "from": {
-- "emailAddress": {
-- "name": "Chilkat Software",
-- "address": "admin@chilkat.io"
-- }
-- }
-- }
-- ]
-- }
--
-- ------------------------------------------------------------------
-- Searching /Inbox/abc
-- {
-- "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#users('me')/mailFolders('AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAL8huv8AAAA%3D')/messages(id,subject,from)",
-- "value": [
-- ]
-- }
--
-- ------------------------------------------------------------------
-- Searching /Inbox/abc/subFolderA
-- {
-- "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#users('me')/mailFolders('AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAL8huwAAAQ%3D%3D')/messages(id,subject,from)",
-- "value": [
-- ]
-- }
--
-- ------------------------------------------------------------------
-- Searching /Inbox/abc/subFolderB
-- {
-- "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#users('me')/mailFolders('AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgAuAAADsVyfxjDU406Ic4X7ill8xAEA5_vF7TKKdE6bGCRqXyl2PQAAAL8huwMAAAA%3D')/messages(id,subject,from)",
-- "value": [
-- ]
-- }
--
-- ------------------------------------------------------------------
-- Combined Search Results:
-- {
-- "value": [
-- {
-- "folderPath": "/Inbox",
-- "id": "AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgBGAAADsVyfxjDU406Ic4X7ill8xAcA5_vF7TKKdE6bGCRqXyl2PQAAAgEMAAAA5_vF7TKKdE6bGCRqXyl2PQAAAM6Jj10AAAA=",
-- "subject": "A sample email with Amazon in the body",
-- "from": {
-- "emailAddress": {
-- "name": "Chilkat Software",
-- "address": "support@chilkatsoft.com"
-- }
-- }
-- },
-- {
-- "folderPath": "/Inbox/abc/subFolderA/a",
-- "id": "AQMkADAwATM0MDAAMS1iNTcwLWI2NTEtMDACLTAwCgBGAAADsVyfxjDU406Ic4X7ill8xAcA5_vF7TKKdE6bGCRqXyl2PQAAAL8huwIAAADn68XtMop0TpsYJGpfKXY9AAAAzombFQAAAA==",
-- "subject": "Sample email from admin@chilkat.io",
-- "from": {
-- "emailAddress": {
-- "name": "Chilkat Software",
-- "address": "admin@chilkat.io"
-- }
-- }
-- }
-- ]
-- }
--
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @xmlMap
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @jsonCombined
EXEC @hr = sp_OADestroy @sbResponse
EXEC @hr = sp_OADestroy @sbPath
END
GO