Code to read emails

Status
Not open for further replies.
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

Mark Andrews

RPT Software

http://www.rptsoftware.com

http://www.donationmanagementsoftware.com
 
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")

"Mark Andrews" <mandrews___NOSPAM___@rptsoftware.com> wrote in message

news:OKp9XKE8KHA.5412@TK2MSFTNGP06.phx.gbl...
> 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

> > Mark Andrews
> RPT Software
> http://www.rptsoftware.com
> http://www.donationmanagementsoftware.com
 
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
<kenslovak@mvps.org> wrote in message

news:O1tVljE8KHA.3964@TK2MSFTNGP05.phx.gbl...
> 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")

> >

>

> "Mark Andrews" <mandrews___NOSPAM___@rptsoftware.com> wrote in message
> news:OKp9XKE8KHA.5412@TK2MSFTNGP06.phx.gbl...
> >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
>

>
>
>
>
>> > > Mark Andrews
> > RPT Software
> > http://www.rptsoftware.com
> > http://www.donationmanagementsoftware.com

>
 
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).

"Mark Andrews" <mandrews___NOSPAM___@rptsoftware.com> wrote in message

news:eCxFyyE8KHA.4604@TK2MSFTNGP04.phx.gbl...
> 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
 
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
<kenslovak@mvps.org> wrote in message

news:#8vERQF8KHA.4604@TK2MSFTNGP04.phx.gbl...
> 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).

> >

>

> "Mark Andrews" <mandrews___NOSPAM___@rptsoftware.com> wrote in message
> news:eCxFyyE8KHA.4604@TK2MSFTNGP04.phx.gbl...
> > 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

>
 
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.

"Mark Andrews" <mandrews___NOSPAM___@rptsoftware.com> wrote in message

news:eD9QtRG8KHA.1748@TK2MSFTNGP06.phx.gbl...
> 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
 
Thanks Ken that makes sense! Now I just need to decide what I should do.

Mark
<kenslovak@mvps.org> wrote in message

news:uC1LlOH8KHA.420@TK2MSFTNGP02.phx.gbl...
> 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.

> >

>

> "Mark Andrews" <mandrews___NOSPAM___@rptsoftware.com> wrote in message
> news:eD9QtRG8KHA.1748@TK2MSFTNGP06.phx.gbl...
> > 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

>
 
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
C In need of VBA code to read / parse HTML - Outlook emails Using Outlook 0
R Need code snippet to read offline PST file Outlook VBA and Custom Forms 1
P How to get a QR code for automatic signin with Outlook for iOS Using Outlook 5
D Outlook 2021 Using vba code to delete all my spamfolders not only the default one. Outlook VBA and Custom Forms 0
F Color code certain INBOX emails Using Outlook 2
K vba code to auto download email into a specific folder in local hard disk as and when any new email arrives in Inbox/subfolder Outlook VBA and Custom Forms 0
J Want to create a button on the nav bar (module add-in) to run code Outlook VBA and Custom Forms 2
H Preventing the 'email address fetch from Exchange' crashing email reading code Exchange Server Administration 0
C Code to move mail with certain attachment name? Does Not work Outlook VBA and Custom Forms 3
Aussie Outlook 365 Rule runs manually but returns the error code "an unexpected error has occurred" when incoming mail arrives Using Outlook 1
S Need code to allow defined starting folder and selection from there to drill down Outlook VBA and Custom Forms 10
D VBA code to select a signature from the signatures list Outlook VBA and Custom Forms 3
S HTML Code Embedded in String Within Open Outlook Email Preventing Replace(Application.ActiveInspector.CurrentItem.HTMLBody From Working Outlook VBA and Custom Forms 4
P Color Code or highlight folders in Outlook 2016 Using Outlook 2
N Please advise code received new mail Using Outlook 0
B Outlook 2016 Unable to view images or logos on the outlook 2016 emails the same html code works well when i use outlook 2010 Using Outlook 0
S Excel vba code to manage outlook web app Using Outlook 10
S Outlook VBA How to adapt this code for using in a different Mail Inbox Outlook VBA and Custom Forms 0
S Add VBA save code Using Outlook 0
C Auto Run VBA Code on new email Outlook VBA and Custom Forms 1
Witzker Pls help to change the code for inserting date in Ol contact body Outlook VBA and Custom Forms 5
I Outlook 2003 shows html code when To: field is empty Using Outlook 7
F VBA code to dock Styles whenever I write or edit an email Outlook VBA and Custom Forms 0
S Skype for business meeting vba code Outlook VBA and Custom Forms 1
R Expand VBA Permanent Delete Code Outlook VBA and Custom Forms 6
B Outlook Business Contact Manager with SQL to Excel, User Defined Fields in BCM don't sync in SQL. Can I use VBA code to copy 1 field to another? BCM (Business Contact Manager) 0
A VBA Code in Outlook disappears after first use Outlook VBA and Custom Forms 1
Alex Cotton "invalid or unqualified reference" on code that should work Outlook VBA and Custom Forms 5
F VBA to ensure a code is entered in Subject title Outlook VBA and Custom Forms 1
Z Outlook Custom Form: Adding Dropdown(Project Code) at the end of subject Outlook VBA and Custom Forms 0
D Any updates or fixes that would make this code stop working just moving emails to another folder Outlook VBA and Custom Forms 1
N Open & Save VBAProject.Otm using VBA Code Outlook VBA and Custom Forms 1
M error code 0x8DE00006 Using Outlook 1
R VBA Code to permanently delete selected email Outlook VBA and Custom Forms 10
dyny723 Outlook 2016: Code to link a contact to emails received from that contact Outlook VBA and Custom Forms 1
D Command Button code will not execute. Any suggestions Please. Outlook VBA and Custom Forms 2
V VB script code to save a specific email attachment from a given email Outlook VBA and Custom Forms 14
J Custom form code doesn't run Outlook VBA and Custom Forms 2
M code to move selected Outlook contacts to another folder Using Outlook 3
E Outlook Form - Voting Responses Not Auto Processing If Form Contains Any Code Outlook VBA and Custom Forms 0
C Need VBA code to automatically save message outside outlook and add date Outlook VBA and Custom Forms 1
S Problem running Command button code Outlook VBA and Custom Forms 2
D Help with code to move mail on receipt to another folder based on time received Outlook VBA and Custom Forms 2
C Color Code New Calendar Items Using Outlook 2
S VBA Code to move mail items from search folder to inbox subfolder Outlook VBA and Custom Forms 4
B Automation error running VB macro code Outlook VBA and Custom Forms 8
S my vbscript button1_click code works on appointment created but not on opening an existing apntmn Outlook VBA and Custom Forms 16
M regarding the Create Contacts From Messages code Using Outlook 8
J Outlook 2010 VBScript editor does not run code at all Outlook VBA and Custom Forms 0
B VBA Code to create appointment from email Outlook VBA and Custom Forms 1

Similar threads

Back
Top