Creating table of Outlook Inbox emails in Access

Question

UPDATE:

Current code below in accordance with recommended SQL construct: error in SqlString =

Run-time error '3011': The Microsoft Access database engine could not find the object ". Make sure the object exists and that you spell its name and the path name correctly. If " is not a local object, check your network connection or contact the server administrator.

Of note, I am working on a USAF unclassified network system, and log in via CAC.

Sub InboxImport
    Dim SqlString As String
    Dim ConnectionString As String
    Dim EmailTableName As String
    Dim UserIdNum As String
    Dim EmailAddr As String
    Dim olNS As Outlook.NameSpace
    Dim olFol As Outlook.Folder`

    Set ol = CreateObject("Outlook.Application")
    Set olNS = ol.GetNamespace("MAPI")
    Set olFol = olNS.GetDefaultFolder(olFolderInbox)

    EmailTableName = "MyInbox" 'My table name
    UserIdNum = Environ("USERNAME")  '1277523A... acct #
    EmailAddr = olFol.Parent.name 'user's email address
    ConnectionString = "Outlook 9.0;MAPILEVEL=" & EmailAddr & "|;PROFILE=Default Outlook Profile;TABLETYPE=0;TABLENAME=MyInbox;COLSETVERSION=12.0;DATABASE=C:\Users\" & UserIdNum & "\AppData\Local\Temp\"

    SqlString = "SELECT [From] As [Sender], [Sender Name] As SenderName, [Subject Prefix] & [Normalized Subject] As Subject, [Contents] As [Body], [Received] As [ReceivedTime]" & _
                " INTO [Email]" & _
                " From [" & ConnectionString & "].[MyInbox]"
    DoCmd.RunSQL SqlString
end sub

Original text:

I am attempting to pull default Outlook inbox emails into a table within Access. I am able to use the wizard to successfully retrieve emails and populate the various columns and view my current inbox via Access table named "Inbox".

My Access database will be used by several employees at the same time, and I can't ask them to run the wizard for every different computer they log into.

I am using code copied from middle of the page..."Export Outlook Emails to Access table - VBA".

I'm attempting to use

DoCmd.RunSQL "INSERT INTO [Email] " & _
     "([Sender], [SenderName], [Subject], [Body], [ReceivedTime])" & _
     "VALUES " & _
     "'" & objProp(i).Sender & "', '" & _     'ERROR!
     objProp(i).SenderName & "', " & _  'ERROR!
     objProp(i).Subject & "', '" & _
     objProp(i).Body & "', '" & _       'ERROR!
     objProp(i).ReceivedTime & "';"

The code stumbles looking at any MailItem property other than .ReceivedTime or .Subject, and those properties throw an error of...

Run-time error '287': Application-defined or object-defined error

For my References - Database:

Visual Basic For Applications
Microsoft Access 15.0 Object Library
OLE Automation
Microsoft Office 15.0 Access database engine Object Library
Microsoft Internet Controls
Microsoft Outlook 15.0 Object Library

I'd suggest stepping through the code (with F8) and checking out the objProp(i) object with the Watch Window and/or Object Browser, to get some insight as to what's going on. Here's more info from Chip Pearson on Debugging VBA. — ashleedawg
Are you sure MyInbox is the foreign table name, so that's how the folder is called in Outlook, no spaces? Email is the table name in your database here. — Erik A
@ErikvonAsmuth, is there a way to get the sender's email address? (or any other contact properties of the sender? i.e. phone number, alias) — Jazzmasterflex

Erik A Erik A · Accepted Answer · 2018-08-03T08:05:47

I strongly recommend you don't take the tried approach when importing mail from Outlook. Access can natively work with Outlook Data Files in SQL queries. You can, of course, execute these queries using VBA. But it will be way more optimized.

The trick is getting the proper connection string. You can easily obtain the connection string by using the following process:

Create a linked table to the desired outlook folder under External Data -> More -> Outlook folder, choose linked table, select the folder
Use Debug.Print CurrentDb.TableDefs!MyLinkedOutlookFolder.Connect to obtain the connection string, and Debug.Print CurrentDb.TableDefs!MyLinkedOutlookFolder.SourceTableName to obtain the external table name

Execute the following query, using your obtained variables:

SELECT [From] As [Sender], [Sender Name] As SenderName, [Subject Prefix] & [Normalized Subject] As Subject, [Contents] As [Body], [Received] As [ReceivedTime]
INTO [Email]
FROM [ThatConnectionString].[ThatSourceTableName]

Sample connection string:

Outlook 9.0;MAPILEVEL=me@example.com|;PROFILE=Default Outlook Profile;TABLETYPE=0;TABLENAME=Inbox;COLSETVERSION=12.0;DATABASE=C:\Users\Me\AppData\Local\Temp\

Sample source table name:

Inbox

That's all you need, no complex VBA needed.

Creating table of Outlook Inbox emails in Access

1 Answers