X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Access pull data from Outlook via VBA    
post Nov 17 2010, 03:07 PM

Posts: 8
Joined: 17-November 10

developed code in Outlook VBA to write a list of my emails to a CSV file. Then it occured to me to have Access pull the data from Outlook since I'm importing the CSV into Access anyway.
The Outlook VBA code, obviously, does not run in Access as-is. It has to be flavored in Access VBA. I don't know the VBA code and protocol for Access to connect to Outlook.
My email store is PST based.
Does someone have some skeleton code I can use that I can figure out the procedure? Or can someone point me to the right place where I can learn by studying the sample code?
Go to the top of the page
post Nov 17 2010, 03:41 PM

Posts: 639
Joined: 2-September 03
From: Virginia

The easiest way is to create a linked table in Access to whichever or as many as you need folders in your Outlook mailbox. Then just write a query or VBA to filter and copy whichever emails you are talking about.
Go to the top of the page
post Nov 17 2010, 03:50 PM

Access Wiki and Forums Moderator
Posts: 72,162
Joined: 19-June 07
From: SunnySandyEggo

In addition to what DeDe said, check out this previous topic.
Hope that helps...
Go to the top of the page
post Nov 17 2010, 04:07 PM

Posts: 8
Joined: 17-November 10

That looks cool. I didn't know Access can do that. I'll definately save that for later.
But I'd really want a VBA to do as I described. I've done it before where I have Access driving to and from Excel. I like to do likewise with Outlook.
Go to the top of the page
post Nov 17 2010, 04:16 PM

Posts: 8
Joined: 17-November 10

That's the idea - Office Automation. I'll have to remember that term. <
Oget an error very early in my code. I error stop on this line ...
Set olAp = CreateObject("Outlook.Application")
The error is: ActiveX component can't create object
At Microsoft.com their explanations are cryptic. I did a search here in utteraccess.com and I can't seem to nail an explanation either.
Since I'm doing this for the first time in Access I immediately thought that I didn't have a library referenced in. I took a guess that I needed to check-on "Microsoft Outlook 14.0 Object Library".
That didn't work.
Am I missing something else?
Thanks in advance, guys. I feel I'm on the right track with all your help.
Go to the top of the page
post Nov 17 2010, 04:18 PM

Posts: 8
Joined: 17-November 10

I just noticed your sign, DB. LOL!!! Thanks for your welcome. This is a nice place you have here!
Go to the top of the page
post Nov 17 2010, 04:50 PM

Posts: 603
Joined: 21-March 09

Here's some code I use to read email messages from a specific folder and then I create new "activity" records in my database (an activity is a general type of record used for emails, to dos, appointments etc...). It uses three folders that exist off the Inbox folder in the default Outlook profile. This might not be what you are trying to do but it might help you a bit? Hope it helps. Note: I used to use early binding but changed everything to late binding after I installed Access 2010 and it wrecked my Outlook 2007 (also always seems better to use late binding).
ublic Sub ReadMessagesFromMailFolder(ShowStatus As Boolean)
On Error GoTo Err_ReadMessagesFromMailFolder
'Before doing anything, check that folders have been defined
Dim strFolderPending As String
Dim strFolderImported As String
Dim strFolderFailed As String
strFolderPending = Nz(DLookup("EMailPendingImport", "tblPreference", "PreferenceID=1"), "")
strFolderImported = Nz(DLookup("EMailImported", "tblPreference", "PreferenceID=1"), "")
strFolderFailed = Nz(DLookup("EMailFailedImport", "tblPreference", "PreferenceID=1"), "")
If strFolderPending = "" Or strFolderImported = "" Or strFolderFailed = "" Then
MsgBox "Folders need to be defined in Setup/Options before importing.", vbOKOnly, "Error"
Exit Sub
End If
Dim lCountPending As Long
Dim lCountImported As Long
Dim lCountFailed As Long
Dim i As Long
i = 0
lCountImported = 0
lCountFailed = 0
'early binding not used anymore
' Dim OlApp As Outlook.Application
' Dim Olmapi As Outlook.NameSpace
' Dim OlFolderMain As Outlook.MAPIFolder
' Dim OlFolderPending As Outlook.MAPIFolder
' Dim OlFolderImported As Outlook.MAPIFolder
' Dim OlFolderFailed As Outlook.MAPIFolder
' Dim olItems As Outlook.Items
Dim OlApp As Object
Dim Olmapi As Object
Dim OlFolderMain As Object
Dim OlFolderPending As Object
Dim OlFolderImported As Object
Dim OlFolderFailed As Object
Dim olItems As Object

Dim Mailobject As Object
'Create a connection to outlook (early binding)
' Set OlApp = CreateObject("Outlook.Application")

'Code added for late binding
On Error Resume Next
Set OlApp = GetObject(, "Outlook.Application")
If Err.Number = 429 Then
Set OlApp = CreateObject("Outlook.application")
End If
On Error GoTo Err_ReadMessagesFromMailFolder

Set Olmapi = OlApp.GetNamespace("MAPI")
'Open the folder
Set OlFolderMain = Olmapi.GetDefaultFolder(olFolderInbox)
'Set up the folders the emails are going to be deposited in
Set OlFolderPending = OlFolderMain.Folders(strFolderPending)
Set olItems = OlFolderPending.Items
lCountPending = OlFolderPending.Items.Count
'If no messages in folder, exit here
If lCountPending = 0 Then
If ShowStatus = True Then MsgBox "No messages to import.", , "Outlook E-Mail Import"
GoTo Exit_ReadMessagesFromMailFolder
End If
Set OlFolderImported = OlFolderMain.Folders(strFolderImported)
Set OlFolderFailed = OlFolderMain.Folders(strFolderFailed)
'loop through mail items and add them to table
'Loop backwards because messages are moved
'Returns true if added, false if not added and then moves to appropriate folder
SysCmd acSysCmdInitMeter, "Importing E-Mails...", lCountPending
For i = olItems.Count To 1 Step -1
SysCmd acSysCmdUpdateMeter, i
If AddActivityFromEMail(Nz(olItems(i).Subject, ""), Nz(Replace(olItems(i).SenderEmailAddress, "'", ""), ""), Nz(Replace(olItems(i).To, "'", ""), ""), Nz(olItems(i).Body, ""), Nz(olItems(i).SentOn, #1/1/1901#)) = True Then
olItems(i).Move OlFolderImported
lCountImported = lCountImported + 1
olItems(i).Move OlFolderFailed
lCountFailed = lCountFailed + 1
End If
'Show result if launched from setup/options screen
If ShowStatus = True Then
MsgBox lCountPending & " messages in Pending folder." & vbCrLf _
& lCountImported & " messages imported." & vbCrLf _
& lCountFailed & " messages not imported.", , "Outlook E-Mail Import"
End If

'Reload activity forms if open
If IsLoaded("frmActivityList") Then Forms("Frmactivitylist").Requery
If IsLoaded("frmActivityCalendar") Then Call Forms("frmactivityCalendar").SetDates
SysCmd acSysCmdRemoveMeter
Set OlApp = Nothing
Set Olmapi = Nothing
Set OlFolderMain = Nothing
Set OlFolderPending = Nothing
Set OlFolderImported = Nothing
Set OlFolderFailed = Nothing
Set olItems = Nothing
Set Mailobject = Nothing
Exit Sub
If Err.Number = -2147221233 Then 'Can't find folder
MsgBox "Unable to find folder in Outlook", , "Error"
MsgBox Err.Description
End If
Resume Exit_ReadMessagesFromMailFolder
End Sub
Go to the top of the page
post Nov 17 2010, 09:13 PM

Posts: 8
Joined: 17-November 10

Thanks Mandrews.
I'm still having a problem with the error stop of "ActiveX component can't create object" very early in the code when it gets to ...
Set olAp = CreateObject("Outlook.Application")
Ogot to figure what this is about.
Curiously, and on a hunch, this works in Access 2003 on an older Windows XP machine with Office 2003. I wonder if there is a change - Microsoft seems to like to change the game of things on each upgrade.
I think this is why my boss has kept all our work PCs on Windows XP and Office 2003. He won't upgrade unless he is forced to because he is afraid of what won't work later on. Only my home system is up on the lastest Microsoft Windows and Office release. And at strange times like this I'm almost sorry I "upgraded". LOL
Go to the top of the page
post Nov 18 2010, 07:14 PM

Posts: 603
Joined: 21-March 09

try late binding so it works with whatever version of Outlook is installed.
therwise not sure about error.
Go to the top of the page
post Nov 18 2010, 09:28 PM

Posts: 8
Joined: 17-November 10

Good idea but I started on another track which solved the issue.
Oput a stop on the running module. While in that state I explored the exposed and active Outlook library objects and noticed that the Outlook objects I wanted to make and reference were already instanced. I figured I must have been creating an object which already existed by default. So VBA gave that nonsensical error instead of one which you could figure out - no news to most of us, right? LOL
So I changed my statement to reference it directly. IT WORKED! It was there all the time! LOL
To put it simply. I changed this ...
Set olAp = CreateObject("Outlook.Application")
to this ...
Set olAp = Outlook.Application
And it worked just fine after that.
Thanks guys!!
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    26th May 2018 - 05:09 AM