I love Microsoft Office and how we can integrate all of the apps into a cohesive solution. This article will show you how to read emails into Access and save any attachments.
Why Store Emails? Because the’re difficult to share
I built a custom order tracking tool for my client used by 30 employees at the company. Orders can range from $100,000 to several million dollars and can take years to build and close. During the order’s life, emails will travel back and forth between customers and employees at the company, providing a wealth of knowledge relative to the order. It was difficult to share emails with other employees on a order using Exchange, that’s when the firm asked me to come up with a way to import them into Access/SQL Server, allowing all employees to see email info in Access regardless of who received them at the company. I created a new tab on the order screen called Order Notes and added a new button called Import Emails. Users can add notes or emails to the system using the same form. If they land on a email the «View Attachments» button becomes available if there were attachments stored with the email.
My client wanted to save the body of the email into a SQL Server table and any attachments to a network directory set aside for document storage for the database, then display the emails on a continuous form inside Access. This strategy allows the client to search for text in the email and even perform queries against the data.
This is not the only way to store archived emails, you could for example save individual emails by selecting File Save As in Outlook and placing them on a networked drive then referencing them as a link in Access. Users can then click to open the email using Outlook. Using this approach will preserve the formatting of the message and the attachments.
Select the method that bests suits you, display email in Access and do queries or display it in Outlook and preserve formatting.
Outlook Folder Maze
One thing you will quickly realize when you try to import emails into Access is that most people’s inbox are quite large, hence, it can take a very long time to scan all emails. Instead create two folders under the inbox called Import and Imported, than have the user move any emails they wish to import into the former. The code will then move the emails to Imported once the process has been completed.
The second thing about mail folders in Outlook is they don’t follow a specific organization from user to user, since they may place folders in different places thus you’re asking for trouble if you hard code folder locations. Better to cycle through all folders until you find the ones you’re looking for and that’s what the code does.
I took the extra step of showing which emails a user can import into the system by using a temp table and a form, allowing them to cherry pick which ones they want imported into which order in Access. It reduces the need to shuffle back and forth between the two programs when you need to import into different records.
Import and Move or Import all then Move? I’m moved you asked…
I decided to move the email while I had a pointer on the message instead of importing all emails and then moving them. When you move the email it reset’s the collection, hence the code starts again to search for emails after each move.
EntryID is your ticket to ride
Each message in Outlook gets a unique number called EntryID, I use it in the code to identify which emails to import after the user has selected them on the import form. A caveat should be noted though – EntityID will be changed whenever you move a message, so don’t rely on it to reference messages as you would a primary key of a table but more like a pointer.
Beware what you wish for
Have you seen emails lately? They contain graphics, html code and who knows what. In order to save the email body as text in SQL Server, I needed to take care of apostrophes and other vermin that can interfere with my import, all of which you will see in the code.
You can find the code here.
Need to read emails into Access but find it daunting? Then hire us to do it for you! Click here to contact us.