​Case Study: Integrating Microsoft Outlook and Access

Introduction: IHS Alexander, Inc. in Simsbury, CT, represents international manufacturers of equipment and systems used to build or maintain power plants. Alex Bosso, the President of IHS Alexander, contacted me after reading one of my LinkedIn articles, Integrating Microsoft Outlook and Access. Alex had been searching for a way to beneficially integrate MS Outlook email content with Access memo fields.

Problem Outline: Specifically, IHS Alexander developed an MIS system based on MS Access 97 and planned to upgrade to the current version of MS Office. At the time Alex reached out to me, they had upgraded their hardware and migrated to Office 365. Although they had an existing Access database, they did not use Outlook for email.

Before the upgrade, IHS had used Outlook Express (Microsoft Mail) for emails because it allowed them to directly drag and drop email messages into a memo field in their Access database. The entirety of the email body, and heading information would paste to the memo field proving a chronological history of communication with clients or prospective clients.

However, after upgrading to Outlook, they lost drag/drop capabilities between their email handler and Access. Alex emailed me wondering if I could solve this problem. In addition there were other issues which needed to be addressed. IHS Alexander’s existing Access database has been in use for over eighteen years. It is their mission critical database. But, like most mature databases, it needed some revisions to accommodate moving to Microsoft’s newer .accdb file format.

Alex and I began working together in February of 2016. Over the months we’ve tackled many different aspects of his database upgrade. But, the one issue that brought us together was a tight integration between Microsoft Outlook and Access was the most fascinating part of our work together..

Solution Summary: This was an interesting project to work on. Alex outlined the following requirements for drag/drop capabilities from Outlook to MS Access memo fields:

1.  When the user drags/drops an Outlook email to an Access memo field, the routine needs to:
   1. Isolate the sender email address.
   2. Search all contacts in the Access database and find the email sender.
   3. Populate a corresponding ContactID field with the senders correct ID number
   4. Populate the appropriate memo field with both email header information and email body text.
2. The routine must be able to work in four different forms/data entry screens.

The VBA routine to manage Outlook drag/drop capabilities processes the following tasks:

1. If the sender is not an IHS employee, then the routine looks up the sender email address in the Access database contact table. If there is a match, the appropriate contact ID number is assigned to the memo records corresponding ContactID field.
2. The Outlook recipient array designates recipient by either a “TO” or “CC” recipient. My VBA routine uses the recipient type to push recipients into either a “TO” or “CC” line for email header information output.
3. The email subject and time/date received is also extracted from the Outlook email, for use in the drag/drop routine.
4. Finally the body of the outlook email is also extracted, so that it can be included in final output.
5. Once all of the above information is obtained from the Outlook email, then memo output is assembled. In short, formatting of the memo output is as follows:

• Subject line
• Received date and time line
• From line
• To Recipients line
• CC Recipients line
• Email Body

Testing and Revisions: Initial testing of the VBA routine went well. But once Alex rolled it out to production and started testing in the real world, we had to make some revisions. The largest issue we ran into was spacing between paragraphs.

In short, once the VBA routine was rolled out to a production environment, many of the emails were pasting, to memo fields, with too many spaces between lines in the email body. This doesn’t seem like it should be a major concern. However, in reality, if an email is several paragraphs long the subsequent memo output would have 4 to 6 line breaks between each paragraph. This can be a very frustrating situation for end-users just trying to read the resulting memo content.

From my perspective, this was a real puzzle to solve. Because some Outlook emails would paste fine, with just the right spacing between paragraphs. Other emails would paste to the memo field with too many lines. And yet still other emails would paste to the Access memo field with no spacing between paragraphs.

Technically the worst problems to trouble-shoot are inconsistent results. And that is exactly what we ran into with paragraph spacing. After much research and experimentation the VBA script was revised to process the email body a bit more extensively.

Outlook email items have different body formats. Specifically an incoming email may have an HTML body format, a plain body format, or a rich text body format. Part of the paragraph spacing problem was related to different body formats. So, my VBA script was revised to accommodate different types of body formats. Without going into a lot of minutiae, depending on the body format, some of the emails were arriving with extra carriage return characters that needed to be stripped.  

For the most part stripping out extra carriage returns seemed to work. But there is always that one little piece of the puzzle that can throw a monkey wrench into the works. Alex receives email from one particular client that doesn’t process appropriately. Even though this client’s email arrives with an HTML body format, its paragraph spacing wasn’t processing like other HTML body formatted emails. For some reason, with this one particular client, the HTML tags were also generating extra line breaks.

Once I figured out HTML tags were also impacting paragraph spacing, I was able to adjust the VBA script appropriately. The impact of HTML tags is inconsistent, and so difficult to accommodate in the VBA script. At this point the drag/drop routine is working well the greatest majority of the time. There are instances where the incoming emails don’t paste perfectly. But, these instances are rare. So, Alex has asked that we move onto other priorities. 
​
Other Outlook/Access Integration Priorities: Integrating Outlook emails by creating a drag/drop routine for Outlook email pasting to an Access memo field was a new experience for me. This made it a new puzzle for me to figure out. But, Alex also wants me to create integration between is Access contacts and his Outlook contacts. So… this is our next step.

The integration routine I build, for integration of Outlook and Access contacts will have to accommodate multiple employee Outlook contact lists. But, once I am finished building the routines, the following capabilities will be in place:

1. Alex will have the ability to export a .csv file prepped for import into Outlook. This file will be a seed file, used for purposes of starting with a clean and updated Outlook contact list.
2. The .csv file includes the Microsoft Access contact ID for each individual. Once this information is imported to Outlook, there will be a way to update outlook contacts directly from MS Access.
3. Alex and his staff only make changes to contact information from one form. So, all update, create and delete routines will be managed from this one, main Access contact form.
4. Since each employee has their own contact list, I will build routines that update Outlook contacts when an employee opens the database. Specifically my routines will only update Outlook contacts by an edit date on the Access contact record. This should limit the number of Outlook contacts that need updating or deleting when an employee opens the database.

Summary: As Outlook integration projects go, this particular project has been exciting because of the drag/drop capabilities. This was my first attempt at building drag/drop capabilities between Outlook and MS Access. Alex has been a wonderful collaborator. He does a great job itemizing priorities, testing work that I’ve completed, and providing needed feedback. He also has a realistic expectation of the process and so, we can discuss the drawbacks and benefits of multiple options. Alex engages well with these conversations. It is because of his willingness to participate in the give and take of a collaborative working relationship that things have gone so well.

More Data Management Articles​​

• Yes – Microsoft Access works in a Multi-User Environment
• YES – Microsoft Access Can be Used Securely
• Deploying Microsoft Access in a Remote Environment
• Why You Should Care about RAD and How It Impacts Your Bottom Line ....
• Do you have questions about your own data management project? Contact Michelle.