How I built a Python script to read e-mails from an Exchange Server

About me

I have been coding since 1992, but I only recently got acquainted with Python. I find it a very straightforward and powerful language. As I work mainly in the integration world, especially with business tools like Microsoft Dynamics or Microsoft Exchange, I recently explored integration using Python and these tools. I found Python is very well suited for the task.

Update: I also had a go at this using Java more recently. If want to check it out, go to my Codementor article Using Java to integrate with Microsoft Exchange Server

The problem I wanted to solve

My goal was to access an Exchange server and read e-mails from it. I found some scripts for reading e-mails through Outlook. However, I believe using Outlook makes the code depend on an e-mail client, and I wanted to avoid that. If you are on a business setting, getting your info from the server, in this case, getting the e-mails from Microsoft Exchange is the best approach

What does this script do?

I created a simple Python script that could access a Microsoft Exchange Server (Exchange 2010, 2013, 2016, 2019, Online) and read e-mails from it. Using this script as a base, you can then elaborate and get only the specific e-mails you want.

Tech stack

The ODBC layer is where the data transfers happen, so from the scripting side, it is the same as if you were using python to access Microsoft SQL Server or to access any database via ODBC. I used the pyodbc module for this (python version 3.7 + pyodbc version 4.0.26). A tool called Connect Bridge handles the actual API integration. Then on the Python script, I read the data as if I were reading data from a database.
Connect Bridge is an integration platform developed by Connecting Software. It enables your script to connect any software through ODBC drivers, JDBC drivers, or Web Services. It is important to note that Connect Bridge is a commercial product. You can get a free trial for it so that you can try this approach for yourself at no cost.

Hands on scripting!

Let's assume the Exchange instance you want to connect to and read e-mails from already exists. These are simple steps you need to follow to get things started:

1. Make sure you have your Exchange login credentials at hand
2. Request a free trial and install Connect Bridge
3. Install Python for Windows ver. 3.7+. You can use the editor of your choice for writing the script.
4. Install pyodbc module 4.0.26+
5. Run Connect Bridge Management Studio and:
   5.1. Add an account for Exchange (Accounts – Add account). For adding the account, you should select the connector MGEXPlugin2010 and use the credentials mentioned on point 1.
   5.2. Open the New Query option and then the Connection Browser. Find the Exchange Connector and expand it until you see the DefaultConnection. Right-click the DefaultConnection and choose the Get Connection string option. Copy the ODBC connection string, as you will need it to pass it on to the script.
   5.3. Use the New Query option to test out a query that will access what you need in Exchange.
   I will do an example query here, but this is where you should put in what it is that you are looking for in Exchange. Once you have clicked New Query, open the Connection Browser, on the left. Find the Exchange Connector (MGEXPlugin2010) and open until the Tables option is visible. We can see that the schema contains a “table” called Message so we can construct our query as SELECT * FROM Message WHERE CreationDate >= ‘2019-01-01 00:00:00’ LIMIT 10; to select ten entries from the Exchange’s e-mails messages list created after Jan 1, 2019.
   Again, please note that although it looks as if we are using a database directly, that is not the case. Connect Bridge is accessing the API and then presenting it as if it were a database. Once you have your query, copy it, as you will need to pass it on to the script.
   The solution is all in one file CBExchange.py and the full source code is below. Please focus on lines 70-92, which depict the core solution. A complete description of how this script works is below.

#!/usr/local/bin/python3.7 
# encoding: utf-8 
''' 
CBExchange -- query data from, write data to Exchange 
CBExchange is a script that allows to read Exchange mail 
using SQL queries via Connect Bridge's ODBC driver 
@author:    Ana Neto  
@copyright:  2019 
@contact:    ana@connecting-software.com 
@deffield    updated: 26.09.2019 
''' 
import sys 
import os 
import pyodbc 
from argparse import ArgumentParser 
from argparse import RawDescriptionHelpFormatter 
__all__ = [] 
__version__ = 0.2 
__date__ = '2019-09-26' 
__updated__ = '2019-09-26' 
DEBUG = 1 
TESTRUN = 0 
PROFILE = 0 
class CLIError(Exception): 
    '''Generic exception to raise and log different fatal errors.''' 
    def __init__(self, msg): 
        super(CLIError).__init__(type(self)) 
        self.msg = "E: %s" % msg 
    def __str__(self): 
        return self.msg 
    def __unicode__(self): 
        return self.msg 
def main(argv=None): # IGNORE:C0111 
    '''Command line options.''' 
    if argv is None: 
        argv = sys.argv 
    else: 
        sys.argv.extend(argv) 
    program_name = os.path.basename(sys.argv[0]) 
    program_version = "v%s" % __version__ 
    program_build_date = str(__updated__) 
    program_version_message = '%%(prog)s %s (%s)' % (program_version, program_build_date) 
    program_shortdesc = __import__('__main__').__doc__.split("n")[1] 
    program_license = '''%s 
  Created by Ana Neto on %s. 
  Licensed under the Apache License 2.0 
  http://www.apache.org/licenses/LICENSE-2.0 
  Distributed on an "AS IS" basis without warranties
  or conditions of any kind, either express or implied. 
USAGE 
''' % (program_shortdesc, str(__date__)) 
    try: 
        # Setup argument parser 
        parser = ArgumentParser(description=program_license, 
        formatter_class=RawDescriptionHelpFormatter) 
        parser.add_argument('connstr')         
        parser.add_argument('query') 
         
        # Process arguments 
        args = parser.parse_args() 
        query = args.query 
        connstr = args.connstr 
        conn = pyodbc.connect(connstr) 
        cursor = conn.cursor() 
        cursor.execute(query) 
        while 1: 
            row = None 
            try: 
                row = cursor.fetchone() 
            except:  
                print(sys.exc_info()[1]) 
                break 
            if not row: 
                break                     
            print(row)              
    except KeyboardInterrupt: 
        ### handle keyboard interrupt ### 
        return 0 
    except: 
        print(sys.exc_info()[1]) 
        #indent = len(program_name) * " "         
        #sys.stderr.write(program_name + ": " + repr(e) + "n") 
        #sys.stderr.write(indent + "  for help use --help") 
        return 2 
if __name__ == "__main__": 
    if TESTRUN: 
        import doctest 
        doctest.testmod() 
    if PROFILE: 
        import cProfile 
        import pstats 
        profile_filename = 'CBExchange_profile.txt' 
        cProfile.run('main()', profile_filename) 
        statsfile = open("profile_stats.txt", "wb") 
        p = pstats.Stats(profile_filename, stream=statsfile) 
        stats = p.strip_dirs().sort_stats('cumulative') 
        stats.print_stats() 
        statsfile.close() 
        sys.exit(0) 
    sys.exit(main()) 

For variables I used
• parser to parse the arguments we get from the command line
• args holds those arguments
• query contains the query we want to run and that comes in as an argument
• connstr holds the ODBC connection string that comes in as an argument and that we pass into the pyodbc module to create an ODBC connection
• conn is the ODBC connection
And here is what the script is doing
• On lines 70 to 78, the goal is to get the variables connstr and query from the command line arguments input. I used pydev's argparse script template for simplicity sake.
• On line 80, we open a database cursor using conn
• On line 82, we execute the SQL query we got from the command line
• On lines 83 to 92, we loop through the results and read them from the cursor row by row.
• When cursor.fetchone returns None, we break the loop
• If an exception occurs during the ODBC fetch, we also break the loop and print the problem to output
• If the fetchone method succeeds and returns a data row, we print the raw data row as it is to the output. I chose to do it like this as it is enough for demo purposes, but naturally in a real-world situation you could format as xml, json, csv, or any other kind of data-interchange format. Or you could also just use the raw data row object to perform custom tasks in subsequent code.
When you are ready to run the script, please note it accepts two positional command-line arguments: connstr and query.
You'll need to copy these from Connect Bridge Management Studio, as explained above (point 5.2). When you have them at hand, open a command line, cd to the folder you saved the script to and run your Python script passing the two parameters to it.

Challenges I faced

When I first approached this problem, I was considering using Exchange's API. I am glad I found an alternative solution, and I never had to study and learn that API. I had never used Exchange's API. By avoiding the learning curve, I got to the end faster.
Still, when I decided to use the Connect Bridge tool, I found it is only available for Windows operating systems (no ODBC Linux client library is available). So, for this to work, I had to stick to a Windows machine.

Key learnings

Using an integration platform made my code much shorter, and I am convinced it will be easy to maintain. If no new feature is needed, I should not have to touch the code again (as compatibility with future Exchange versions is guaranteed). If a new feature is required, then I think the abstraction of reaching to Exchange as if it were a database will give me a big head start as it did for this project.

Tips and advice

When considering using Python to connect to an API that you have never used, consider using a third-party tool that can make your life easier. This is especially true if the API is from Microsoft or another software giant, as those seem to be changed more often. But make sure you are careful when choosing the third-party, so that it is not slowing your code down.
I am happy with the choice I made. In the end, I have a got script that works for several Exchange versions. The third-party tool used ensures forward and backward compatibility.

Final thoughts and next steps

We have now seen how accessing Exchange data in Python can be quickly done. Using the same Connect Bridge integration platform, you can also get data from other business software like Microsoft Dynamics or Salesforce. You just need to pick the connector for the specific software you want and go with it.

Connect Bridge can really save you a lot of headaches when doing an integration project. Your code will have fewer errors, you'll need to write less code, and once you are done with the project, you will not need to go back and do maintenance, as Connect Bridge comes with a forward compatibility warranty (that's a great time saver!).

If you want to know how to use Connect Bridge to get some specific information, you can explore on your own by trying it on a free trial or you can get in touch with Connecting Software's experts. They will be more than happy to give you a free demo or answer any questions you might have.

You can also Leave a Reply below, and I will do my best to help you out.