MAPI - retrieving sender property takes 10-15 seconds? What are the right Object types?

[TomPC]

Hi Forum

2007 / XP

I have a process which brings emails into a table in Excel (TimeReceived, Sender, Subject, Body, Mail Folder). I have to use MAPI because it is a group mailbox, which cannot be controlled with standard outlook vba like a primary outlook folder.

When the macro runs through a folder with about 700 mails in it it takes a good 10-15 minutes to complete.

With a little diagnosis I have worked out that this is because every time I pick up the sender property of my mapiitem it takes 10-15 seconds to do so.

I have (abridged) code as follows:

Dim MapiSess As Object
Dim MapiFolderInbox As Object
Dim MapiItem As Object
Dim arrMailboxes As Variant
 
Set MapiSess = CreateObject("MAPI.Session")
MapiSess.Logon "Outlook"
 
Set MapiFolderInbox = MapiSess.InfoStores(arrMailboxes(j)).RootFolder.Folders(strFolder(i))
 
For Each MapiItem In MapiFolderInbox.Messages
 
'THIS IS THE LINE THAT TAKES A LONG TIME
.Cells(NumMails + RowHeader, ColSender).Value = MapiItem.Sender
 
Next MapiItem

My theory is that by using variants and objects and writing MapiItem.Sender directly to a cell I may be making Excel do a lot of detective work to figure out what type (string/date/object etc) each of these objects might be.

(Q1) Do any of you know how I could more accurately declare:

Dim MapiSess As ?
Dim MapiFolderInbox As ?
Dim MapiItem As ?
Dim arrMailboxes As ?

ALSO if I amend the code to stop the direct write to cells of an unknown object....

Dim mapiSender
 
Set mapiSender=MapiItem.Sender
.Cells(NumMails + RowHeader, ColSender).Value = mapiSender

(Q2) What should mapiSender be declared as?
(Q3) .Cells(...).Value = mapiSender.Something <<<< What should the property in the last line of the above code be. Maybe .name?

(Q4) Am I barking up the wrong tree - is the slowdown caused by something else?

Thanks for looking!

Tom

 

[Norie]

Tom

I think it's a reference for MAPI you need more than the object type.

Do you have one on your machine?

Even without the reference you can find the object types by just adding a watch on the variables.

PS Are you sure MAPI's the only way to access the group box? Have you looked into using ADO?

 

[TomPC]

Ah, Norie! How nice to know you're on the case!

Yes I do have the references: MS CDO 1.21 Library and MS CDO for Windows 2000 Library (though I admit I don't know how to work out which one is the facilitator).

Adding watches was a new thing for me - fantastic! I have now declared such wonderful new types as:

MAPI.Session
MAPI.Folder
MAPI.Message
MAPI.AddressEntry
​

(It took me a while to work out I had to stick MAPI. in front of those - how would I normally figure that out?)

The code is still slow - but is definitely A LOT quicker. Now runs in 4 minutes.

Any idea at all why it sticks on Sender for so long? It definitely seems to get "tired" as the loop progresses though there's nothing inefficient in there.

I haven't looked into ADO yet... at the moment I have something that works pretty well.

Thanks for your help,

Tom

 

[Norie]

Tom

I don't know if ADO would be an option.

I've not used it much and when I have it's been pretty fiddly, and that just trying to get the data from my personal inbox.

I actually just tried it and got 1 result and I swear it's an email I deleted years ago.

Probably me doing something very wrong.