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.
The Strategy
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.
Great article and code.
Interesting thing I found was, if the sub-folder has a space in the name (i.e. “Ven’s Emails to Import” the folder will be found, but the code to loop through the mail items “For Each objMailItem In objFolder.Items” will not find any items.
Remove the spaces, then all runs as expected.
Is there a way to actually have the code to run?
What is weird, if I use the following:-
Set inbox = ns.GetDefaultFolder(olFolderInbox)
Set sub_folder = inbox.Folders(“Ven’s Emails to Import”)
the For Each loop runs.
Hi Juan,
I get Error 0 () in procedure Form_Load of VBA Document Form_FrmImportEmails “
Juan
Admire your spirit of sharing knowledge and expertise with the rest of the world. Just goes to prove it belongs to the Creator and is for all of mankind!
With the best in life.
Kasam
[…] Access VBA import Outlook email https://accessexperts.com/blog/2011/0…s-into-access/ (adsbygoogle = window.adsbygoogle || []).push({}); To provide db: Make copy, remove […]
This code is perfect for the project I am working on. Please guide me, where this code has to be placed. I created a command button on the access form named ‘import email’ and added the code on ‘command button click’. How should I provide reference to code. Please help.
Jubilee –
In your VBA editor, use Tools -> References, then select “Microsoft Outlook XX.X Object Library”. That should enable you to reference the object model for Outlook. That said, read up on early/late binding (which you can easily google for numerous articles and examples) so you’re aware of development and distribution issues with additional references.
[…] You have two options: 1) Read the mail directly from the mail server. I prefer this method since it does not depend on the email client. 2) Read the emails inside Outlook. See: Importing Outlook emails into Access […]
Juan, like the breakdown of how you went about this. I have incorporated the code in a new form (the global code has gone into a separate global module) but frustratingly the OnLoad event triggers an error (one of those oh so helpful MS messages!) stating “the expression On Load you entered as the event property setting produced the following error:User defined type not defined”. I have called the form frmMailImportTest and have the OnLoad property set to [Event Procedure] and placed your On Load event code in the forms code module. Any suggestions?
I am also getting the same error (user defined type not defined) when compiling on the Dim Job As clsJob line. Did you resolve this?
Did you experience, or have you heard of any issues with compatibility between 32 bit and 64 bit. I’ve developed similar code, but am experiencing minor issues with portability. I ran into your post here researching possible causes and alternatives.
If you develop in 64 the file will not work in a 32 bit environment.
I am also trying to process your code. In the function OutlookFolderNames, the dim oFolder as Outlook.Folder failed because the type Outlook.Folder is not defined. Have I missed something?
Gib, you’d need to add VBA Reference to Microsoft Office XX.X Outlook object library to compile with Outlook’s objects.
Alternatively, you can convert the code to use late-binding instead.
This looks perfect for something I need to do (especially automating importing the email attachments), but I’m running into problems with clsJob – I don’t see anywhere in your code where the “Job” class is defined. Is it in a library I need to reference?
Hubert,
You can ignore the clsJob portion of it, it was only used on a project I had and is specialized. Glad to hear it worked for you!
Hi Juan,
Thanks for the site, really useful. I was wondering whether you had a step by step guide on importing the emails, as I am a newbie to this – and am finding it difficult to follow.
Kindest Regards,
Eileen
Eileen,
Glad to hear you like the blog! No, I don’t have a step by step, it’s assumed you are already proficient with VBA when you read our posts. May I suggest a step by step guide to using Access books on Amazon?
Kind Regards,
Juan
I’m trying to use something similar but your example doesn’t seem to work.
My architecture is Access ADP (which I think is the problem) front end on a SQL 2005 DB.
What are you using in this example?
Ed,
It should work fine, what issue are you having?
It would be great to post a sample .mdb
Hi DJ,
We have a policy of not posting MDBs and instead encourage the reader to explore, learn and if they have issues, come back here and post. Hope you can use this code in your database!
Juan
Juan, thanks for posting this, after coping and pasting this as a txt file, where should it be placed?
Hi Phil
You would paste it into a generic module