Outlook Mail (Office 365) Connector for Python In this article you will learn how to quickly and efficiently integrate Outlook Mail (Office 365) data in Python. We will use high-performance Outlook Mail (Office 365) Connector to easily connect to Outlook Mail (Office 365) and then access the data inside Python. Outlook Mail Connector (Office 365) can be used to integrate Office 365 Outlook Mail API in your App / BI Tools. You can download attachment, read / search Emails, Users, MailFolders, Send email and more. Let's follow the steps below to see how we can accomplish that! Download Documentation Outlook Mail (Office 365) Connector for Python is based on ZappySys API Driver which is part of ODBC PowerPack. It is a collection of high-performance ODBC drivers that enable you to integrate data in SQL Server, SSIS, a programming language, or any other ODBC-compatible application. ODBC PowerPack supports various file formats, sources and destinations, including REST/SOAP API, SFTP/FTP, storage services, and plain files, to mention a few.

Connect to Outlook Mail (Office 365) in other apps Connect to Outlook Mail (Office 365) in SSIS Connect to Outlook Mail (Office 365) in SQL Server Connect to Outlook Mail (Office 365) in Power BI Connect to Outlook Mail (Office 365) in SSRS Connect to Outlook Mail (Office 365) in Informatica Connect to Outlook Mail (Office 365) in MS Access Connect to Outlook Mail (Office 365) in MS Excel Connect to Outlook Mail (Office 365) in SSAS Connect to Outlook Mail (Office 365) in C# Connect to Outlook Mail (Office 365) in JAVA Connect to Outlook Mail (Office 365) in Tableau Connect to Outlook Mail (Office 365) in SAP Crystal Reports Connect to Outlook Mail (Office 365) in Azure Data Factory (Pipeline) Connect to Outlook Mail (Office 365) in Azure Data Factory (SSIS) Connect to Outlook Mail (Office 365) in Talend Studio Connect to Outlook Mail (Office 365) in UiPath Connect to Outlook Mail (Office 365) in PowerShell Connect to Outlook Mail (Office 365) in ODBC

Create ODBC Data Source (DSN) based on ZappySys API Driver

Step-by-step instructions

To get data from Outlook Mail (Office 365) using Python we first need to create a DSN (Data Source) which will access data from Outlook Mail (Office 365). We will later be able to read data using Python. Perform these steps:

1. Install ZappySys ODBC PowerPack.

2. Open ODBC Data Sources (x64):

   

3. Create a User data source (User DSN) based on ZappySys API Driver

   ZappySys API Driver

   
   • Create and use User DSN if the client application is run under a User Account. This is an ideal option in design-time, when developing a solution, e.g. in Visual Studio 2019. Use it for both type of applications - 64-bit and 32-bit.
   • Create and use System DSN if the client application is launched under a System Account, e.g. as a Windows Service. Usually, this is an ideal option to use in a production environment. Use ODBC Data Source Administrator (32-bit), instead of 64-bit version, if Windows Service is a 32-bit application.

4. When the Configuration window appears give your data source a name if you haven't done that already, then select "Outlook Mail (Office 365)" from the list of Popular Connectors. If "Outlook Mail (Office 365)" is not present in the list, then click "Search Online" and download it. Then set the path to the location where you downloaded it. Finally, click Continue >> to proceed with configuring the DSN:

   OutlookMailOffice365DSN

   Outlook Mail (Office 365)

   

5. Now it's time to configure the Connection Manager. Select Authentication Type, e.g. Token Authentication. Then select API Base URL (in most cases, the default one is the right one). More info is available in the Authentication section.

    User Credentials [OAuth]

   Use delegated access (User Credentials) whenever you want to let a signed-in user work with their own resources or resources they can access. Whether it's an admin setting up policies for their entire organization or a user deleting an email in their inbox, all scenarios involving user actions should use delegated access. [API reference]

   Steps how to get and use Outlook Mail (Office 365) credentials
   

   Follow these simple steps below to create Microsoft Entra ID application with delegated access:

   WARNING: If you are planning to automate processes, we recommend that you use a Application Credentials authentication method. In case, you still need to use User Credentials, then make sure you use a system/generic account (e.g. automation@my-company.com). When you use a personal account which is tied to a specific employee profile and that employee leaves the company, the token may become invalid and any automated processes using that token will start to fail.

   1. Navigate to the Azure Portal and log in using your credentials.
   2. Access Microsoft Entra ID.
   3. Register a new application by going to App registrations and clicking on New registration button:
      INFO: Find more information on how to register an application in Graph API reference.
   4. When configuration window opens, configure these fields:
      • Supported account type
        • Use Accounts in this organizational directory only, if you need access to data in your organization only.
      • Redirect URI:
        • Set the type to Public client/native (mobile & desktop).
        • Use https://zappysys.com/oauth as the URL.
      
   5. After registering the app, copy the Application (client) ID for later:
   6. Then copy OAuth authorization endpoint (v2) & OAuth token endpoint (v2) URLs to use later in the configuration:
   7. Now go to SSIS package or ODBC data source and use the copied values in User Credentials authentication configuration:
      • In the Authorization URL field paste the OAuth authorization endpoint (v2) URL value you copied in the previous step.
      • In the Token URL field paste the OAuth token endpoint (v2) URL value you copied in the previous step.
      • In the Client ID field paste the Application (client) ID value you copied in the previous step.
      • In the Scope field use the default value or select individual scopes, e.g.:
        • email
        • offline_access
        • openid
        • profile
        • Mail.Read
        • Mail.Read.Shared
        • Mail.ReadBasic
        • Mail.ReadBasic.Shared
        • Mail.ReadWrite
        • Mail.ReadWrite.Shared
        • Mail.Send
        • Mail.Send.Shared
        • User.Read
        • User.ReadBasic.All
   8. Press Generate Token button to generate Access and Refresh Tokens.
   9. Optional step. Choose Default User Id from the drop down menu (if someone shared a mailbox with you).
   10. Click Test Connection to confirm the connection is working.
   11. Done! Now you are ready to use the API Connector!

   Fill in all required parameters and set optional parameters if needed:

   OutlookMailOffice365DSN

   Outlook Mail (Office 365)

   User Credentials [OAuth]

   https://graph.microsoft.com/v1.0

   Required Parameters
   Authorization URL	Fill-in the parameter...
   Token URL	Fill-in the parameter...
   Client ID	Fill-in the parameter...
   Scope	Fill-in the parameter...
   Return URL	Fill-in the parameter...
   Optional Parameters
   Client Secret
   Refresh Token File Path
   Login Prompt Option
   Default User Id (Select after clicking **Generate Token**)	me
   RetryMode	RetryWhenStatusCodeMatch
   RetryStatusCodeList	429|503
   RetryCountMax	5
   RetryMultiplyWaitTime	True

   

    Application Credentials [OAuth]

   Application-only access is broader and more powerful than delegated access (User Credentials), so you should only use app-only access where needed. Use it when: 1. The application needs to run in an automated way, without user input (for example, a daily script that checks emails from certain contacts and sends automated responses). 2. The application needs to access resources belonging to multiple different users (for example, a backup or data loss prevention app might need to retrieve messages from many different chat channels, each with different participants). 3. You find yourself tempted to store credentials locally and allow the app to sign in 'as' the user or admin. [API reference]

   Steps how to get and use Outlook Mail (Office 365) credentials
   

   Follow these simple steps below to create Microsoft Entra ID application with application access permissions.

   1. Navigate to the Azure Portal and log in using your credentials.
   2. Access Microsoft Entra ID.
   3. Register a new application by going to App registrations and clicking on New registration button:
      INFO: Find more information on how to register an application in Graph API reference.
   4. When configuration window opens, configure these fields:
      • Supported account type
        • e.g. select Accounts in this organizational directory only if you need access to data in your organization only.
      • Redirect URI:
        • Set the type to Public client/native (mobile & desktop).
        • Leave the URL field empty.
      
   5. After registering the app, copy the Application (client) ID for later:
   6. Then copy OAuth authorization endpoint (v2) & OAuth token endpoint (v2) URLs:
   7. Continue and create Client secret:
   8. Then copy the Client secret for later steps:
   9. Continue by adding permissions for the app by going to the API permissions section, and clicking on Add a permission:
   10. Select Microsoft Graph:
   11. Then choose Application permissions option:
   12. Continue by adding these Mail (Outlook) permissions:
   13. Finish by clicking Add permissions button:
   14. Now it's time to Grant admin consent for your application:
   15. Confirm all the permissions are granted:
   16. Now go to SSIS package or ODBC data source and use the copied values in Application Credentials authentication configuration:
       • In the Token URL field paste the OAuth token endpoint (v2) URL value you copied in the previous step.
       • In the Client ID field paste the Application (client) ID value you copied in the previous step.
       • In the Client Secret field paste the Client secret value you copied in the previous step.
       • Choose Default User Id from the drop down menu.
   17. Click Test Connection to confirm the connection is working.
   18. That's it! Now you can use the API Connector!

   Fill in all required parameters and set optional parameters if needed:

   OutlookMailOffice365DSN

   Outlook Mail (Office 365)

   Application Credentials [OAuth]

   https://graph.microsoft.com/v1.0

   Required Parameters
   Token URL	Fill-in the parameter...
   Client ID	Fill-in the parameter...
   Client Secret	Fill-in the parameter...
   Scope	Fill-in the parameter...
   Default User Id	Fill-in the parameter...
   Optional Parameters
   RetryMode	RetryWhenStatusCodeMatch
   RetryStatusCodeList	429|503
   RetryCountMax	5
   RetryMultiplyWaitTime	True

   

6. Once the data source has been configured, you can preview data. Select the Preview tab and use settings similar to the following to preview data:
   

7. Click OK to finish creating the data source.

Video instructions

Read data in Python

Using ODBC DSN

1. Python code to get the data:
   

   OutlookMailOffice365DSN')

   

2. When you run the code it will make the API call and read the data:
   

3. Here is Python program's code in text format:

   
       import pyodbc
       conn = pyodbc.connect('DSN=OutlookMailOffice365DSN')
   
       cursor = conn.cursor()
   
       #execute query to fetch data from API service
       cursor.execute("SELECT id,title FROM products")
   
       row = cursor.fetchone()
       while row:
           print(row)
           row = cursor.fetchone()
   
       ##For loop example
       #for row in cursor:
       #    print(row)
   

Using a full ODBC connection string

If you want to avoid being dependent on a DSN and creating multiple DSNs for each platform (x86, x64), then you can use a fully qualified connection string. Simply go to your DSN and copy the Connection String:

1. Open ODBC data source configuration and click Copy settings:
   

   ZappySys API Driver - Outlook Mail (Office 365)

   Outlook Mail Connector (Office 365) can be used to integrate Office 365 Outlook Mail API in your App / BI Tools. You can download attachment, read / search Emails, Users, MailFolders, Send email and more.

   OutlookMailOffice365DSN

   
2. The window opens, telling us the connection string was successfully copied to the clipboard:

3. Then in your Python code use Connection String when initializing OdbcConnection object, for example:

   conn = pyodbc.connect('DRIVER={ZappySys API Driver};ServiceUrl=https://yourservices.provider.com/api/xxxx....;AuthName=Http;')

How to install `pyodbc` in the Python?

Advanced topics

Create Custom Stored Procedure in ZappySys Driver

You can create procedures to encapsulate custom logic and then only pass handful parameters rather than long SQL to execute your API call.

Steps to create Custom Stored Procedure in ZappySys Driver. You can insert Placeholders anywhere inside Procedure Body. Read more about placeholders here

1. Go to Custom Objects Tab and Click on Add button and Select Add Procedure:
   

2. Enter the desired Procedure name and click on OK:
   

3. Select the created Stored Procedure and write the your desired stored procedure and Save it and it will create the custom stored procedure in the ZappySys Driver:
   Here is an example stored procedure for ZappySys Driver. You can insert Placeholders anywhere inside Procedure Body. Read more about placeholders here
   

   CREATE PROCEDURE [usp_get_orders]
       @fromdate = '<<yyyy-MM-dd,FUN_TODAY>>'
    AS
       SELECT * FROM Orders where OrderDate >= '<@fromdate>';
   

   
   

4. That's it now go to Preview Tab and Execute your Stored Procedure using Exec Command. In this example it will extract the orders from the date 1996-01-01:
   

   Exec usp_get_orders '1996-01-01';

   
   

5. Let's generate the SQL Server Query Code to make the API call using stored procedure. Go to Code Generator Tab, select language as SQL Server and click on Generate button the generate the code.
   As we already created the linked server for this Data Source, in that you just need to copy the Select Query and need to use the linked server name which we have apply on the place of [MY_API_SERVICE] placeholder.
   

   SELECT * FROM OPENQUERY([LINKED_SERVER_TO_OUTLOOK_MAIL_OFFICE_365_IN_DATA_GATEWAY], 'EXEC usp_get_orders @fromdate=''1996-07-30''')

   
   

6. Now go to SQL served and execute that query and it will make the API call using stored procedure and provide you the response.
   

Create Custom Virtual Table in ZappySys Driver

ZappySys API Drivers support flexible Query language so you can override Default Properties you configured on Data Source such as URL, Body. This way you don't have to create multiple Data Sources if you like to read data from multiple EndPoints. However not every application support supplying custom SQL to driver so you can only select Table from list returned from driver.

If you're dealing with Microsoft Access and need to import data from an SQL query, it's important to note that Access doesn't allow direct import of SQL queries. Instead, you can create custom objects (Virtual Tables) to handle the import process.

Many applications like MS Access, Informatica Designer wont give you option to specify custom SQL when you import Objects. In such case Virtual Table is very useful. You can create many Virtual Tables on the same Data Source (e.g. If you have 50 URLs with slight variations you can create virtual tables with just URL as Parameter setting.

1. Go to Custom Objects Tab and Click on Add button and Select Add Table:
   

2. Enter the desired Table name and click on OK:
   

3. And it will open the New Query Window Click on Cancel to close that window and go to Custom Objects Tab.

4. Select the created table, Select Text Type AS SQL and write the your desired SQL Query and Save it and it will create the custom table in the ZappySys Driver:
   Here is an example SQL query for ZappySys Driver. You can insert Placeholders also. Read more about placeholders here
   

   SELECT
     "ShipCountry",
     "OrderID",
     "CustomerID",
     "EmployeeID",
     "OrderDate",
     "RequiredDate",
     "ShippedDate",
     "ShipVia",
     "Freight",
     "ShipName",
     "ShipAddress",
     "ShipCity",
     "ShipRegion",
     "ShipPostalCode"
   FROM "Orders"
   Where "ShipCountry"='USA'

   
   

5. That's it now go to Preview Tab and Execute your custom virtual table query. In this example it will extract the orders for the USA Shipping Country only:
   

   SELECT * FROM "vt__usa_orders_only"

   
   

6. Let's generate the SQL Server Query Code to make the API call using stored procedure. Go to Code Generator Tab, select language as SQL Server and click on Generate button the generate the code.
   As we already created the linked server for this Data Source, in that you just need to copy the Select Query and need to use the linked server name which we have apply on the place of [MY_API_SERVICE] placeholder.
   

   SELECT * FROM OPENQUERY([LINKED_SERVER_TO_OUTLOOK_MAIL_OFFICE_365_IN_DATA_GATEWAY], 'EXEC [usp_get_orders] ''1996-01-01''')

   
   

7. Now go to SQL served and execute that query and it will make the API call using stored procedure and provide you the response.
   

Actions supported by Outlook Mail (Office 365) Connector

Outlook Mail (Office 365) Connector Examples for Python Connection

This page offers a collection of SQL examples designed for seamless integration with the ZappySys API ODBC Driver under ODBC Data Source (36/64) or ZappySys Data Gateway, enhancing your ability to connect and interact with Prebuilt Connectors effectively.

SELECT * FROM MyMessages

select * from Users 
			--Where Id='049beb7f-03e0-4b0d-825d-73567b6786e9'

SELECT * FROM MyMessages
WHERE Id='AAXkADIwNzNhODMyLTZiMTQtNDhiMC02OWQzLTc5YTY5M2IyMjk0NABGAyAAAACbj2hVuNphT74wylrfU4ixBwAbUV6IxRnpQrqzrb2WfacdAAAAAAEMAAAbUV6IxRnpQrqzrb2WfacdAAAysBnxAAA='

SELECT * FROM get_messages
WITH (UserID='firstname.lastname@domainname.com')

SELECT * FROM get_messages
WITH (UserID='firstname.lastname@domainname.com', PageSize='5')

SELECT * FROM get_messages
WITH (
MailFolderID='INBOX' --or use mai folder id
)

SELECT * FROM get_messages
WITH (UserID='firstname.lastname@domainname.com', SearchCriteria='startsWith(Subject,''Announcements for'')')

SELECT * FROM get_messages
WITH (UserID='firstname.lastname@domainname.com', SearchCriteria='hasAttachments eq true')

SELECT * FROM get_messages
WITH (UserID='firstname.lastname@domainname.com', SearchCriteria='IsRead eq false')

SELECT * FROM MyMessages WITH (OrderBy='sentDateTime')

SELECT * FROM MyMessages WITH (OrderBy='sentDateTime desc')

SELECT * FROM MyMessages WITH (OrderBy='receivedDateTime')

SELECT * FROM MyMessages WITH (OrderBy='receivedDateTime desc')

SELECT * FROM MyMessages WITH (OrderBy='sender/emailAddress/name')

SELECT * FROM MyMessages WITH (OrderBy='sender/emailAddress/name desc')

SELECT * FROM MyMailFolders

SELECT * FROM MyMailFolders
WHERE (Id='AAMkADIwNzNhODMyLTZiMTQtNDhiMCz4OWQzLTc5YTY5M2IyMjk0NAYuAAAAAACbj2hVuNphT74wyYrfU4ixAQAbUV6IxRnpQrWzrb2WfacdAAAAAAEKAcA=')

SELECT * FROM get_message_attachments
WITH (MessageId='AAXkADIwNzNhODMyLTZiMTQtNDhiMC02OWQzLTc5YTY5M2IyMjk0NABGAyAAAACbj2hVuNphT74wylrfU4ixBwAbUV6IxRnpQrqzrb2WfacdAAAAAAEMAAAbUV6IxRnpQrqzrb2WfacdAAAysBnxAAA=')

SELECT * FROM get_message_attachment
WITH (MessageId='AAXkADIwNzNhODMyLTZiMTQtNDhiMC02OWQzLTc5YTY5M2IyMjk0NABGAyAAAACbj2hVuNphT74wylrfU4ixBwAbUV6IxRnpQrqzrb2WfacdAAAAAAEMAAAbUV6IxRnpQrqzrb2WfacdAAAysBnxAAA=',
AttachmentId='AAMkADIwNzNhODMyLTZiMTQtNDhiM704OWQzLTc5YTY5M2IyMjk0NABGAAqAAACbj2hVuNphT74wylrfU4ixBwAbUV6IxRnpQrqzrb2WfacdAABBAAEMAAAbUV6IxRnpQrqzrb2WfacdAAAysB9xAAABEgAQAK6dg4NuEa5Fmn_5Tp_D_XM=')

SELECT * FROM download_message_attachment
WITH (MessageId='AAMkADIwNz2hODMyLTZiMTQtNDhiMC04OWQzLTc5YTY5M2IyMjk0NABGZAAAAxCbj2hVuNphT74wylrfU4ixBwAbUV6IxRnpQ6qzrb2WfacdAAAAAAEMAAAbUb6IxRnpQrqzrb2WfacdAAAysBnxAAA=',
AttachmentId='AAMkADIwNzNhODMyLTZiMTQtNDhiMC04cWQzLTc5YTY5M2IyMjk0NABGAAAAAACbj2hVuNphTZ4wylrfU4ixBwAbUV6IxRnpQrqzrb2WfacdAAAAAAEMAA8bUV6IxRnpQrqzrb2WfacdAAAysBnxAAABEgAQAK6dg4NuEa5Fmn_5Tp_D_XM=',
TargetFilePath='C:\temp\photo.png'
)

SELECT *  FROM download_message_attachments
WITH(
	  SaveFolder='c:\download',	
	  OverwriteFile='True',
 	  MailFolderId='INBOX', --or use mailbox ID
	  UserId='me', --or use "user-id" or use "email"
	  --search mail with attachments, sent after 2023-03-01, subject contains string "invoice" and  from email is microsoft-noreply@microsoft.com
	  SearchCriteria='hasAttachments eq true and sentDateTime gt 2023-03-01 and contains(subject, ''invoice'') and from/emailAddress/address eq ''microsoft-noreply@microsoft.com''  ',
	  EmailBodyFormat='HTML' --or text
)

INSERT INTO MyMessages
(Subject, BodyContentType, BodyContent
, ToRecipients, CcRecipients, BccRecipients
, InternetMessageHeaders
, Attachments, Importance, IsDeliveryReceiptRequested, IsReadReceiptRequested
, SaveToSentItems)
VALUES
('Employee Reviews Scheduled', 'HTML', '<b>Hi All,</b> employee reviews have been scheduled. <span style="text-decoration: underline;">Please reflect this in your notes.</span>',
 '[{ "emailAddress": { "address": "john.doe@domain.com" }}, { "emailAddress": { "address": "jane.doe@domain.com" }}]',
 '[{ "emailAddress": { "address": "mary.dawson@domain.com" }}]',
 '[{ "emailAddress": { "address": "ryan.connor@domain.com" }}]',
 '[{ "name": "x-custom-header-group-name", "value": "Managers" }, { "name": "x-custom-header-group-id", "value":"MGR001" }]',
 '[
      {
        "@odata.type": "#microsoft.graph.fileAttachment",
        "name": "file1.txt",
        "contentType": "text/plain",
        "contentBytes": "<<c:\file1.txt,FUN_FILE_BASE64ENC>>"
      },
      {
        "@odata.type": "#microsoft.graph.fileAttachment",
        "name": "profile-picture.png",
        "contentType": "image/png",
        "contentBytes": "<<c:\profile-picture.png,FUN_FILE_BASE64ENC>>"
      }	  
  ]', 
 'normal', 'false', 'false', 'true')

SELECT * FROM generic_request
  WITH (
  URL='/users/049beb7f-03e0-4b0d-825d-73567b6786e9'
  , RequestMethod='GET'
  , Filter='$' --optional if you like to read from array inside document
  --Try commenting below line or define static metadata (speed up API calls by not requesting columns)
  , Meta='businessPhones:String(220); displayName:String(230); givenName:String(100); jobTitle:String(255); mail:String(310); mobilePhone:String(255); officeLocation:String(255); preferredLanguage:String(255); surname:String(140); userPrincipalName:String(500); id:String(360); '
  )

Conclusion

In this article we showed you how to connect to Outlook Mail (Office 365) in Python and integrate data without any coding, saving you time and effort. We encourage you to download Outlook Mail (Office 365) Connector for Python and see how easy it is to use it for yourself or your team.

If you have any questions, feel free to contact ZappySys support team. You can also open a live chat immediately by clicking on the chat icon below.

Download Outlook Mail (Office 365) Connector for Python Documentation

More integrations

Other connectors for Python

Amazon Ads

Amazon Selling Partner (SP-API)

Asana

Azure DevOps

Cosmos DB

DropBox

ElasticSearch

FastSpring

Google Ads

Google BigQuery

Google Calendar

Google Drive

Google Sheets

Hubspot

Jira

Mailchimp

ManagedEngine ServiceDesk Plus (Zoho)

OData

OneDrive

Power BI

ServiceNow

SharePoint Online

Shopify

Smartsheet

Stripe

YouTube

Zendesk

Zoho CRM

Zoho SalesIQ

Zoom

REST API

JSON

JSON File

XML

SOAP

XML File

JDBC-ODBC Bridge

Salesforce

CSV

Amazon S3 JSON File

Amazon S3 XML File

Amazon S3 CSV File

Azure Blob JSON File

Azure Blob XML File

Azure Blob CSV File

FTP/SFTP JSON File

FTP/SFTP XML File

FTP/SFTP CSV File

Build your own Custom API Connector

Other application integration scenarios for Outlook Mail (Office 365)

SSIS

SQL Server

Power BI

SSRS

Informatica

MS Access

MS Excel

SSAS

C#

JAVA

Tableau

SAP Crystal Reports

Azure Data Factory (Pipeline)

Azure Data Factory (SSIS)

Talend Studio

UiPath

PowerShell

ODBC

Common Searches: