Outlook Mail (Office 365) Connector for Python

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. Download and install 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

   Outlook Mail (Office 365) authentication

   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]

   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
        • Group.Read.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!

   API Connection Manager configuration

   Just perform these simple steps to finish authentication configuration:

   1. Set Authentication Type to User Credentials [OAuth]
   2. Optional step. Modify API Base URL if needed (in most cases default will work).
   3. Fill in all the required parameters and set optional parameters if needed.
   4. Press Generate Token button to generate the tokens.
   5. Finally, hit OK button:

   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

   Outlook Mail (Office 365) authentication

   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]

   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!

   API Connection Manager configuration

   Just perform these simple steps to finish authentication configuration:

   1. Set Authentication Type to Application Credentials [OAuth]
   2. Optional step. Modify API Base URL if needed (in most cases default will work).
   3. Fill in all the required parameters and set optional parameters if needed.
   4. Finally, hit OK button:

   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 connection has been configured, it's time to configure the SQL query. Select the Preview tab and then click Query Builder button to configure the SQL query:

   

   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

   

7. Start by selecting the Table or Endpoint you are interested in and then configure the parameters. This will generate a query that we will use in Python to retrieve data from Outlook Mail (Office 365). Hit OK button to use this query in the next step.

   SELECT * FROM MyMessages

   
   Some parameters configured in this window will be passed to the Outlook Mail (Office 365) API, e.g. filtering parameters. It means that filtering will be done on the server side (instead of the client side), enabling you to get only the meaningful data much faster.

8. Now hit Preview Data button to preview the data using the generated SQL query. If you are satisfied with the result, use this query in Python:

   

   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

   SELECT * FROM MyMessages

   
   You can also access data quickly from the tables dropdown by selecting <Select table>.
   A WHERE clause, LIMIT keyword will be performed on the client side, meaning that the whole result set will be retrieved from the Outlook Mail (Office 365) API first, and only then the filtering will be applied to the data. If possible, it is recommended to use parameters in Query Builder to filter the data on the server side (in Outlook Mail (Office 365) servers).

9. Click OK to finish creating the data source.

Video Tutorial

Read data in Python

Using ODBC DSN

1. Use this code snippet to read the data using OutlookMailOffice365DSN data source:

   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 - Configuration [Version: 2.0.1.10418]

   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?

Centralized data access via Data Gateway

In some situations, you may need to provide Outlook Mail (Office 365) data access to multiple users or services. Configuring the data source on a Data Gateway creates a single, centralized connection point for this purpose.

This configuration provides two primary advantages:

• Centralized data access
  The data source is configured once on the gateway, eliminating the need to set it up individually on each user's machine or application. This significantly simplifies the management process.
• Centralized access control
  Since all connections route through the gateway, access can be governed or revoked from a single location for all users.

If you need any of these requirements, you will have to create a data source in Data Gateway to connect to Outlook Mail (Office 365), and to create an ODBC data source to connect to Data Gateway in Python.

Let's not wait and get going!

Creating Outlook Mail (Office 365) data source in Gateway

In this section we will create a data source for Outlook Mail (Office 365) in Data Gateway. Let's follow these steps to accomplish that:

1. Search for gateway in Windows Start Menu and open ZappySys Data Gateway Configuration:

   

2. Go to Users tab and follow these steps to add a Data Gateway user:

   • Click Add button
   • In Login field enter username, e.g., john
   • Then enter a Password
   • Check Is Administrator checkbox
   • Click OK to save
   

3. Now we are ready to add a data source:

   • Click Add button
   • Give Datasource a name (have it handy for later)
   • Then select Native - ZappySys API Driver
   • Finally, click OK
   OutlookMailOffice365DSN

   ZappySys API Driver

   

4. When the ZappySys API Driver configuration window opens, configure the Data Source the same way you configured it in ODBC Data Sources (64-bit), in the beginning of this article.

5. Very important step. Now, after creating or modifying the data source make sure you:

   • Click the Save button to persist your changes.
   • Hit Yes, once asked if you want to restart the Data Gateway service.

   This will ensure all changes are properly applied:

   
   Skipping this step may result in the new settings not taking effect and, therefore you will not be able to connect to the data source.

Creating ODBC data source for Data Gateway

In this part we will create ODBC data source to connect to Data Gateway from Python. To achieve that, let's perform these steps:

1. Open ODBC Data Sources (x64):

   

2. Create a User data source (User DSN) based on ODBC Driver 17 for SQL Server:

   ODBC Driver 17 for SQL Server

   
   If you don't see ODBC Driver 17 for SQL Server driver in the list, choose a similar version driver.

3. Then set a Name of the data source (e.g. Gateway) and the address of the Data Gateway:

   GatewayDSN

   localhost,5000

   
   Make sure you separate the hostname and port with a comma, e.g. localhost,5000.

4. Proceed with authentication part:

   • Select SQL Server authentication
   • In Login ID field enter the user name you used in Data Gateway, e.g., john
   • Set Password to the one you configured in Data Gateway
   

5. Then set the default database property to OutlookMailOffice365DSN (the one we used in Data Gateway):

   OutlookMailOffice365DSN

   

6. Continue by checking Trust server certificate option:

   

7. Once you do that, test the connection:

   

8. If connection is successful, everything is good:

   

9. Done!

We are ready to move to the final step. Let's do it!

Accessing data in Python via Data Gateway

Finally, we are ready to read data from Outlook Mail (Office 365) in Python via Data Gateway. Follow these final steps:

1. Go back to Python.

2. Use this code snippet to read the data using GatewayDSN data source:

   GatewayDSN')

   

3. Read the data the same way we discussed at the beginning of this article.

4. That's it!

Now you can connect to Outlook Mail (Office 365) data in Python via the Data Gateway.

Actions supported by Outlook Mail (Office 365) Connector

Learn how to perform common Outlook Mail (Office 365) actions directly in Python with these how-to guides:

• Delete Message by Id (Single)
• Delete Messages - Using Search Condition (Bulk Delete)
• Download Message Attachment (Single File)
• Download Message Attachments (Multiple Files) - Using Search Condition
• Download Message by Id (Single *.EML File RFC 822 / MIME)
• Download Messages by Search Condition (Multiple *.EML Files RFC 822 / MIME)
• Get Group by Id
• Get Groups
• Get Mail Folder by Id [only works with User Credentials]
• Get Mail Folders
• Get Message Attachment
• Get Message Attachments
• Get Message by Id (Single) - Output binary data (EML RFC 822 / MIME)
• Get Messages
• Get Messages by Search Filters - Output binary data (EML RFC 822 / MIME)
• Get My Information
• Get My Mail Folders [only works with User Credentials]
• Get My Message by Id [only works with User Credentials]
• Get My Messages [only works with User Credentials]
• Get Table Columns
• Get Tables
• Get User by Id
• Get Users
• Send Mail
• Make Generic API Request
• Make Generic API Request (Bulk Write)

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 Athena

Amazon Selling Partner (SP-API)

Apache Derby

Apache Hive

Apache Spark

Asana

Azure DevOps

Cosmos DB

Dropbox

ElasticSearch

FastSpring

Gmail

Google Ads

Google BigQuery

Google Calendar

Google Drive

Google Search Console

Google Sheets

Hubspot

Infor Data Lake

Jira

Mailchimp

ManagedEngine ServiceDesk Plus (Zoho)

MariaDB

OData

OneDrive

Parquet (DuckDB)

Power BI

ServiceNow

SharePoint Online

Shopify

Smartsheet

Starburst

Stripe

Trino

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