I am accessing Outlook from Access VBA and whilst the following code works
just fine it seems really slow to just show a list of the mail items that are
in the Inbox are there any suggestions on how this could be speeded up.

Set objFolder = objOutlook.GetNamespace("MAPI").GetDefaultFolder(M ailBox)

With objFolder

lngCount = .Items.Count

If lngCount 0 Then

For Each objOutlookMail In .Items

aryEmails(1, lngCount2) = Nz(objOutlookMail.To, "")
aryEmails(2, lngCount2) = Nz(objOutlookMail.Subject, "")
aryEmails(3, lngCount2) = objOutlookMail.Senton

lngCount2 = lngCount2 + 1

Next

End If

End With

Many thanks

Try using the SetColumns method to retrieve only those properties that you're interested in, e.g.:

.Item.SetColumns ("To, Subject, SentOn")

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003
http://www.turtleflock.com/olconfig/index.htm
and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/jumpstart.aspx

"Simon Maystre" wrote in message ...

I am accessing Outlook from Access VBA and whilst the following code works
just fine it seems really slow to just show a list of the mail items that are
in the Inbox are there any suggestions on how this could be speeded up.

Set objFolder = objOutlook.GetNamespace("MAPI").GetDefaultFolder(M ailBox)

With objFolder

lngCount = .Items.Count

If lngCount 0 Then

For Each objOutlookMail In .Items

aryEmails(1, lngCount2) = Nz(objOutlookMail.To, "")
aryEmails(2, lngCount2) = Nz(objOutlookMail.Subject, "")
aryEmails(3, lngCount2) = objOutlookMail.Senton

lngCount2 = lngCount2 + 1

Next

End If

End With

Many thanks

This did the trick to speed up the load by around 500%! I also changed how I
was referencing the item object slightly and this also improved load time:

Thanks for your help.

Set objFolder = objOutlook.GetNamespace("MAPI").GetDefaultFolder(M ailBox)
Set objOutlookItems = objFolder.Items

With objOutlookItems

.SetColumns ("From, To, SenderName, Subject, SentOn")
lngCount = .Count

If lngCount 0 Then

ReDim aryEmails(3, lngCount - 1)

For lngCount2 = 1 To lngCount

aryEmails(0, lngCount2 - 1) = lngCount2

If MailBox = 5 Then
aryEmails(1, lngCount2 - 1) = Nz(.Item(lngCount2).To, "")
Else
aryEmails(1, lngCount2 - 1) =
Nz(.Item(lngCount2).SenderName, "")
End If

aryEmails(2, lngCount2 - 1) = Nz(.Item(lngCount2).Subject, "")
aryEmails(3, lngCount2 - 1) = .Item(lngCount2).SentOn
Progress lngCount2

Next

End If

End With





"Sue Mosher [MVP-Outlook]" wrote:

Try using the SetColumns method to retrieve only those properties that you're interested in, e.g.:

.Item.SetColumns ("To, Subject, SentOn")

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003
http://www.turtleflock.com/olconfig/index.htm
and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/jumpstart.aspx

"Simon Maystre" wrote in message ...

I am accessing Outlook from Access VBA and whilst the following code works
just fine it seems really slow to just show a list of the mail items that are
in the Inbox are there any suggestions on how this could be speeded up.

Set objFolder = objOutlook.GetNamespace("MAPI").GetDefaultFolder(M ailBox)

With objFolder

lngCount = .Items.Count

If lngCount 0 Then

For Each objOutlookMail In .Items

aryEmails(1, lngCount2) = Nz(objOutlookMail.To, "")
aryEmails(2, lngCount2) = Nz(objOutlookMail.Subject, "")
aryEmails(3, lngCount2) = objOutlookMail.Senton

lngCount2 = lngCount2 + 1

Next

End If

End With

Many thanks

Sue,

I am looking for a list of the column names to select from to pass with
SetColumns. I can get some columns to work but not all. Would you have the
list for me? Nice when mapped to the functions like SenderName, ReceivedTime
etc.

I also found out that when I pass multiple columnnames in one string with
SetColumns the performance is as poor as without but when I add the selected
columns in individual instructions I do have the performance.

"Sue Mosher [MVP-Outlook]" wrote:

Try using the SetColumns method to retrieve only those properties that you're interested in, e.g.:

.Item.SetColumns ("To, Subject, SentOn")

--
Sue Mosher, Outlook MVP
Author of Configuring Microsoft Outlook 2003
http://www.turtleflock.com/olconfig/index.htm
and Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/jumpstart.aspx

"Simon Maystre" wrote in message ...

I am accessing Outlook from Access VBA and whilst the following code works
just fine it seems really slow to just show a list of the mail items that are
in the Inbox are there any suggestions on how this could be speeded up.

Set objFolder = objOutlook.GetNamespace("MAPI").GetDefaultFolder(M ailBox)

With objFolder

lngCount = .Items.Count

If lngCount 0 Then

For Each objOutlookMail In .Items

aryEmails(1, lngCount2) = Nz(objOutlookMail.To, "")
aryEmails(2, lngCount2) = Nz(objOutlookMail.Subject, "")
aryEmails(3, lngCount2) = objOutlookMail.Senton

lngCount2 = lngCount2 + 1

Next

End If

End With

Many thanks

The Outlook VBA Object Browser Help has a list of what properties you cannot
use with SetColumns. Any other properties not listed there can be used with
SetColumns.

Speed with SetColumns and multiple properties is faster than not using
SetColumns. The gain in speed is related to how many columns you ask for and
how large those columns are. If any are larger than can be retrieved using a
MAPITable then retrieving that property will require falling back to an
IStream to retrieve the property, thereby cutting the speed somewhat.

--
Ken Slovak
[MVP - Outlook]
http://www.slovaktech.com
Author: Professional Programming Outlook 2007.
Reminder Manager, Extended Reminders, Attachment Options.
http://www.slovaktech.com/products.htm


"VOORSPRONG" wrote in message
...
Sue,

I am looking for a list of the column names to select from to pass with
SetColumns. I can get some columns to work but not all. Would you have the
list for me? Nice when mapped to the functions like SenderName,
ReceivedTime
etc.

I also found out that when I pass multiple columnnames in one string with
SetColumns the performance is as poor as without but when I add the
selected
columns in individual instructions I do have the performance.

Ken,

Thanks for the answer.

Two issues; Are the column names identically to the property names and if so
why does SenderName when passed as string not return a value when the
property SenderName is requested. Second; I am not using VBA but a likewise
COM solution (Visual DataFlex). And in VBA when I went to the SetColumns
instruction I only saw that I needed to enter a string.

Regards,
Vincent Oorsprong
Data Access Europe B.V.
Netherlands

"Ken Slovak - [MVP - Outlook]" wrote:

The Outlook VBA Object Browser Help has a list of what properties you cannot
use with SetColumns. Any other properties not listed there can be used with
SetColumns.

Speed with SetColumns and multiple properties is faster than not using
SetColumns. The gain in speed is related to how many columns you ask for and
how large those columns are. If any are larger than can be retrieved using a
MAPITable then retrieving that property will require falling back to an
IStream to retrieve the property, thereby cutting the speed somewhat.

--
Ken Slovak
[MVP - Outlook]
http://www.slovaktech.com
Author: Professional Programming Outlook 2007.
Reminder Manager, Extended Reminders, Attachment Options.
http://www.slovaktech.com/products.htm


"VOORSPRONG" wrote in message
...
Sue,

I am looking for a list of the column names to select from to pass with
SetColumns. I can get some columns to work but not all. Would you have the
list for me? Nice when mapped to the functions like SenderName,
ReceivedTime
etc.

I also found out that when I pass multiple columnnames in one string with
SetColumns the performance is as poor as without but when I add the
selected
columns in individual instructions I do have the performance.

Select SetColumns in the Object Browser and click F1. That will open the
Help which has the list of properties you cannot use. All properties use
their object model names. If SenderName is not blank in the items it should
be returned.

--
Ken Slovak
[MVP - Outlook]
http://www.slovaktech.com
Author: Professional Programming Outlook 2007.
Reminder Manager, Extended Reminders, Attachment Options.
http://www.slovaktech.com/products.htm


"VOORSPRONG" wrote in message
...
Ken,

Thanks for the answer.

Two issues; Are the column names identically to the property names and if
so
why does SenderName when passed as string not return a value when the
property SenderName is requested. Second; I am not using VBA but a
likewise
COM solution (Visual DataFlex). And in VBA when I went to the SetColumns
instruction I only saw that I needed to enter a string.

Regards,
Vincent Oorsprong
Data Access Europe B.V.
Netherlands