Code to read emails

  • Thread starter Thread starter Mark Andrews
  • Start date Start date
M

Mark Andrews

I wrote some Microsoft Access 2007 VBA code (see below) to read email
messages from an Outlook folder
and it seems to work fine on my computer.

I have Outlook configured to read from 2 POP/SMTP accounts and the mail
folder name I am passing in is
"personal folders\Inbox\Inquiries"

My client is using Exchange and trying to specify a folder as
"Mailbox-Personsname\Inbox\CRM Updates" and the she can't get the code to
work.

Question has anyone done this for Outlook 2007 configured to read from
Exchange or would you know what I might need to change?
My client is on the other side of the world so I'm finding it difficult to
debug. I don't do this kind of email reading very often.

Note: The client also has two email accounts setup and wants to read from
the non-default one.

Thanks,
Mark

Public Sub ReadMessagesFromMailFolder(MailFolderName As String)
On Error GoTo Err_ReadMessagesFromMailFolder
Dim RS As DAO.Recordset
Dim OlApp As Outlook.Application
Dim Olmapi As Outlook.NameSpace
Dim OlFolderMain As Outlook.MAPIFolder
Dim OlFolder As Outlook.MAPIFolder
Dim olItems As Outlook.Items
Dim Mailobject As Object

'Clear temp table
CurrentDb.Execute ("Delete * from tblOutlookMail")

'Create a connection to outlook
Set OlApp = CreateObject("Outlook.Application")
Set Olmapi = OlApp.GetNamespace("MAPI")

'Open the folder
Set OlFolder = GetFolder(MailFolderName)

'Set up the folders the emails are going to be deposited in
Set olItems = OlFolder.Items

Set RS = CurrentDb.OpenRecordset("tblOutlookMail")

'loop through mail items and add them to table
For Each Mailobject In olItems
With RS
.AddNew
!Subject = Mailobject.Subject
!From = Mailobject.SenderEmailAddress
!To = Mailobject.To
!Body = Mailobject.Body
!DateSent = Mailobject.SentOn
.Update
End With
Next

Exit_ReadMessagesFromMailFolder:
Set OlApp = Nothing
Set Olmapi = Nothing
Set OlFolderMain = Nothing
Set OlFolder = Nothing
Set olItems = Nothing
Set Mailobject = Nothing
Set RS = Nothing
Exit Sub

Err_ReadMessagesFromMailFolder:
MsgBox Err.Description
Resume Exit_ReadMessagesFromMailFolder

End Sub
 
Instead of coding like that use NameSpace.GetDefaultFolder(olFolderInbox) to
get the Inbox folder. That will work universally. Once you have the Inbox as
a MAPIFolder (Folder in Outlook 2007) you can then use code like this,
assuming oInbox is your Inbox: oInbox.Folders("CRM Updates")
 
Ken,

I had code like that and it did work. However I couldn't figure out how to
make it work for the folder the client wanted to use.
The client is very particular that it has to be the folder she wants.

She has 2 exchange accounts setup and if you look at her folders she has
- MailBox - XXX
Inbox is under this

- Mailbox - YYY
Inbox is under this

Let's assume XXX is the default mailbox, the code you indicate would goto
the Inbox of XXX and then you can reference subfolders.

How would I reference a folder under Mailbox - YYY?

Thanks,
Mark
 
The root folder of the mailbox would be Inbox.Parent. Just get Inbox and the
Parent property will point to the root folder (Outlook Today).
 
I'm sorry I still don't understand. She provided a screenshot which shows
three trees of folders.
the top three folders show:
- MailBox - XXX
- Mailbox - YYY
- Archive Folders

so Inbox.Parent would be MailBox - XXX

How do I retrieve folder "Mailbox - YYY\Inbox\CRMUpdates"?

Do I have to traverse two parents and then "Mailbox - YYY" and then "inbox"
and then "CRM Updates"?

In my Outlook I have "personal Folders\Inbox" so your logic makes sense for
my situation.

Thanks,
Mark
 
NameSpace.Folders has all the folders and stores. Each mailbox is a store,
as is a PST file (Personal Folders). If 3 stores are loaded (not counting
any public folders) NameSpace.Folders(1) would be one of them, etc. The
first one may not be the default though.

For each folder under a store you'd use a construct like Folder.Folders() to
get at subfolders.

Inbox.Parent would only apply to the default store. GetDefaultFolder() only
works for getting a default folder from the default store (where email is
delivered for the logged in user).

In a case such as you have you'd need to know the exact name and location of
every store and folder of interest. That hard coding of names would make it
impossible to make your code universal, you'd need to customize it for each
person and every variation of loaded stores, folders and folder names.
That's why almost all developers will fix names and folder locations.
 
Back
Top