No, I am suggesting that the code could be adapted to import directly to Access tables. The example code is good for either Excel or Access because it stops short of giving the code that actually writes the data to Excel spreadsheet or Access table. The code shown is good for Excel and Access as it just shows how to open the Outlook folder, loop through the items in folder, grab email elements - which is applicable to VBA in both Excel and Access.
These might not be the best nor most comprehensive examples, but as I said, wasn't finding much. Maybe you will have better luck with a web search.
I did some experimenting with the example code and got this much to work:
Code:
Sub EmailImport()
'Dim olApp As Object, olNS As Object, olMail As Object, eFldr As Object
Dim olApp As Outlook.Application, olNS As Outlook.Namespace, olMail As Outlook.MailItem, eFldr As Outlook.Folder
On Error GoTo eHandler ' Set error handling
' Set the reference to outlook or create one
'Set olApp = GetObject(, "Outlook.Application")
Set olApp = CreateObject("Outlook.Application")
' Set the reference to the oulook MAPI namespace
Set olNS = olApp.GetNamespace("MAPI")
Set eFldr = olNS.Folders("Mailbox - whatever your Outlook shows").Folders("Inbox")
' Cycle through the emails in the specified mailbox folder
For Each olMail In eFldr.Items
If InStr(1, olMail.Subject, "some text here", vbTextCompare) > 0 And olMail.UnRead = True Then
Debug.Print olMail.Subject
Debug.Print olMail.SenderName
Debug.Print olMail.ReceivedTime
Debug.Print olMail.SenderEmailAddress
olMail.UnRead = False
' From this point additional scripting needed to save to table. Could be like:
CurrentDb.Execute "INSERT INTO tablename(field1, field2, field3, field4) " & _
VAlUES('" & olMail.Subject & "', '" & olMail.SenderName & "', #" & olMail.ReceivedTime & "#, '" & olMail.SenderEmailAddress & "')"
End If
NextMsg:
Next olMail
eHandler:
Set olApp = Nothing
Set olNS = Nothing
Set olMail = Nothing
Set eFldr = Nothing
End Sub
However, the SenderEmailAddress property doesn't retrieve what I expected.
The Dim line that declares object variables by reference to Outlook is early binding and requires setting a VBA reference to Microsoft Outlook 12.0 Object Library. This allows intellisense popup tips.