S
SiJP
Greetings fellow developers.
I would like some opinions on the methods I have used to achieve an 'outlook
imitation', using ms access. I have built an Access form that contains a
treeview and a listview, that imitate Outlooks folders and email items
respectively.
The way my application builds the treeview is through a recursive function,
i.e. check an outlook object folder for subfolders, then for each subfolder
check for more subfolders, etc etc, until you reach the end of the "for each"
statement.
Private Sub AddSubFoldersToTreeview(olSelectedFolder As MAPIFolder)
On Error GoTo AddSubFoldersToTreeview_Err
Dim tvxObj As TreeView
Dim ilxObj As ImageList
Dim iImage As Integer
Dim olFolder As MAPIFolder
Dim olFolderParent As MAPIFolder
Set ilxObj = ilxOutlook.Object 'ilxOutlook is an ActiveX Image Control
Set tvxObj = tvxFolders.Object 'tvxFolders is an ActiveX Treeview Control
Set tvxObj.ImageList = ilxObj
Set olFolderParent = olFolder
For Each olSelectedFolder In olFolderParent.Folders
If olSelectedFolder.DefaultItemType = olMailItem Then
Set olFolderParent = olFolder.Parent
'add the item as a node to the treeview
Set objNode = tvxObj.Nodes.Add(olFolderParent.FolderPath,
tvwChild, olSelectedFolder.FolderPath, olSelectedFolder.Name, 1)
objNode.Tag = olSelectedFolder.EntryID
If olSelectedFolder.Folders.Count > 0 Then
Call AddSubFoldersToTreeview(olSelectedFolder)
End If
End If
Next
AddSubFoldersToTreeview_Exit:
Set olFolderParent = Nothing
Set tvxObj = Nothing
Set ilxObj = Nothing
Exit Sub
AddSubFoldersToTreeview_Err:
Msgbox("Error:" & Err & "-" & Error) 'Yet another rubbish error message...
Resume AddSubFoldersToTreeview_Exit
End Sub
Each time that a folder in this list is clicked, the intention is that the
listview is then populated with the mail messages stored within this folder.
To achieve this, I again use recursiveness, like the above code...
- Loop through EVERY folder in the current users outlook session, until I
reach the one I want.
- Load the email's from that folder into the list view.
<snip>
' THIS KICKS OF THE RECURSIVENESS (is that a word? recursiveness??)
For Each olFolder In olNS.Folders 'where olNS is Outlook.Namespace
If olFolder.DefaultItemType = olMailItem Then 'Only for
Mailbox items, not public folders...
Call LoadOutlookFolderData(olFolder, tvxObj.SelectedItem)
End If
Next
</snip>
Private Sub LoadOutlookFolderData(ByRef olSelectedFolder As MAPIFolder,
ByRef sFolderTarget As String)
On Error GoTo LoadOutlookFolderData_Err
Set olFolderParent = olFolder
For Each olSelectedFolder In olFolderParent.Folders
If olSelectedFolder.DefaultItemType = olMailItem Then
Set olFolderParent = olFolder.Parent
If olSelectedFolder.Name = sFolderTarget Then
For Each olItem In olSelectedFolder.Items
If Not bCancelLoad Then 'Ensure that the user hasn't
cancelled processing
'LoadListItems is a function that adds details to the listview..
Call LoadListItems(olItem.EntryID,
IIf(olItem.Attachments.Count <> 0, True, False), olItem.To,
olItem.SenderName, olItem.Subject, olItem.CreationTime, olItem.ReceivedTime,
olItem.Size, olItem.UnRead)
Else
GoTo LoadOutlookFolderData_Exit
End If
DoEvents
Next
GoTo LoadOutlookFolderData_Exit
Else
'Recurse..
Call LoadOutlookFolderData(olSelectedFolder, sFolderTarget)
End If
End If
Next
LoadOutlookFolderData_Exit:
Exit Sub
LoadOutlookFolderData_Err:
Select Case Err.Number
Case 13 'If its a 13 (Type Mismatch), then its not a mail item...
Resume
Case 287 'User clicked 'NO' to the security Prompt
MsgBox "Cannot access Microsoft Outlook because you have clicked
'No' to the security prompt." & vbCrLf & vbCrLf & "Please re try opening the
email selection form and click 'Yes' when prompted by Microsoft Outlook.",
vbOKOnly + vbExclamation, "Warning!"
Resume LoadOutlookFolderData_Exit
Case Else
Beep
Msgbox("Error:" & Err & "-" & Error) 'Yet another rubbish error
message...
Resume LoadOutlookFolderData_Exit
End Select
End Sub
So lets say I click on the Inbox.. off the application trots to load the
emails, looping through every folder. Then I click on sent items - again,
away we go, looking for emails. Now lets click back on the Inbox - yep, the
app hums away looking for the emails again.
All in all, it works well on my (development) computer, and loads the data
*reasonably* quickly. But then again I have only the one mailbox open in my
outlook session, and the amount of emails is a mere few hundred (300 emails
in one folder takes 6 seconds to populate in my listview). Taking a scenario
that may occur - an end user has 10 or more mailboxes open. Most of these
relate to various customer support mailboxes, or colleagues
mailboxes, and the target folder may have over 1000 emails in it.
Each time I selected a new node (folder) in my treeview, the application
recurses through EVERY outlook folder, until the node selected is matched.
This is a serious waste of time and resources.
So - how can I speed the process up?! Should I cache things in access, if
so how would I know which folders have been cached, and would doing the
cacheing slow things down even further?
Thanks for reading, and I hope to get a good discussion going!
I would like some opinions on the methods I have used to achieve an 'outlook
imitation', using ms access. I have built an Access form that contains a
treeview and a listview, that imitate Outlooks folders and email items
respectively.
The way my application builds the treeview is through a recursive function,
i.e. check an outlook object folder for subfolders, then for each subfolder
check for more subfolders, etc etc, until you reach the end of the "for each"
statement.
Private Sub AddSubFoldersToTreeview(olSelectedFolder As MAPIFolder)
On Error GoTo AddSubFoldersToTreeview_Err
Dim tvxObj As TreeView
Dim ilxObj As ImageList
Dim iImage As Integer
Dim olFolder As MAPIFolder
Dim olFolderParent As MAPIFolder
Set ilxObj = ilxOutlook.Object 'ilxOutlook is an ActiveX Image Control
Set tvxObj = tvxFolders.Object 'tvxFolders is an ActiveX Treeview Control
Set tvxObj.ImageList = ilxObj
Set olFolderParent = olFolder
For Each olSelectedFolder In olFolderParent.Folders
If olSelectedFolder.DefaultItemType = olMailItem Then
Set olFolderParent = olFolder.Parent
'add the item as a node to the treeview
Set objNode = tvxObj.Nodes.Add(olFolderParent.FolderPath,
tvwChild, olSelectedFolder.FolderPath, olSelectedFolder.Name, 1)
objNode.Tag = olSelectedFolder.EntryID
If olSelectedFolder.Folders.Count > 0 Then
Call AddSubFoldersToTreeview(olSelectedFolder)
End If
End If
Next
AddSubFoldersToTreeview_Exit:
Set olFolderParent = Nothing
Set tvxObj = Nothing
Set ilxObj = Nothing
Exit Sub
AddSubFoldersToTreeview_Err:
Msgbox("Error:" & Err & "-" & Error) 'Yet another rubbish error message...
Resume AddSubFoldersToTreeview_Exit
End Sub
Each time that a folder in this list is clicked, the intention is that the
listview is then populated with the mail messages stored within this folder.
To achieve this, I again use recursiveness, like the above code...
- Loop through EVERY folder in the current users outlook session, until I
reach the one I want.
- Load the email's from that folder into the list view.
<snip>
' THIS KICKS OF THE RECURSIVENESS (is that a word? recursiveness??)
For Each olFolder In olNS.Folders 'where olNS is Outlook.Namespace
If olFolder.DefaultItemType = olMailItem Then 'Only for
Mailbox items, not public folders...
Call LoadOutlookFolderData(olFolder, tvxObj.SelectedItem)
End If
Next
</snip>
Private Sub LoadOutlookFolderData(ByRef olSelectedFolder As MAPIFolder,
ByRef sFolderTarget As String)
On Error GoTo LoadOutlookFolderData_Err
Set olFolderParent = olFolder
For Each olSelectedFolder In olFolderParent.Folders
If olSelectedFolder.DefaultItemType = olMailItem Then
Set olFolderParent = olFolder.Parent
If olSelectedFolder.Name = sFolderTarget Then
For Each olItem In olSelectedFolder.Items
If Not bCancelLoad Then 'Ensure that the user hasn't
cancelled processing
'LoadListItems is a function that adds details to the listview..
Call LoadListItems(olItem.EntryID,
IIf(olItem.Attachments.Count <> 0, True, False), olItem.To,
olItem.SenderName, olItem.Subject, olItem.CreationTime, olItem.ReceivedTime,
olItem.Size, olItem.UnRead)
Else
GoTo LoadOutlookFolderData_Exit
End If
DoEvents
Next
GoTo LoadOutlookFolderData_Exit
Else
'Recurse..
Call LoadOutlookFolderData(olSelectedFolder, sFolderTarget)
End If
End If
Next
LoadOutlookFolderData_Exit:
Exit Sub
LoadOutlookFolderData_Err:
Select Case Err.Number
Case 13 'If its a 13 (Type Mismatch), then its not a mail item...
Resume
Case 287 'User clicked 'NO' to the security Prompt
MsgBox "Cannot access Microsoft Outlook because you have clicked
'No' to the security prompt." & vbCrLf & vbCrLf & "Please re try opening the
email selection form and click 'Yes' when prompted by Microsoft Outlook.",
vbOKOnly + vbExclamation, "Warning!"
Resume LoadOutlookFolderData_Exit
Case Else
Beep
Msgbox("Error:" & Err & "-" & Error) 'Yet another rubbish error
message...
Resume LoadOutlookFolderData_Exit
End Select
End Sub
So lets say I click on the Inbox.. off the application trots to load the
emails, looping through every folder. Then I click on sent items - again,
away we go, looking for emails. Now lets click back on the Inbox - yep, the
app hums away looking for the emails again.
All in all, it works well on my (development) computer, and loads the data
*reasonably* quickly. But then again I have only the one mailbox open in my
outlook session, and the amount of emails is a mere few hundred (300 emails
in one folder takes 6 seconds to populate in my listview). Taking a scenario
that may occur - an end user has 10 or more mailboxes open. Most of these
relate to various customer support mailboxes, or colleagues
mailboxes, and the target folder may have over 1000 emails in it.
Each time I selected a new node (folder) in my treeview, the application
recurses through EVERY outlook folder, until the node selected is matched.
This is a serious waste of time and resources.
So - how can I speed the process up?! Should I cache things in access, if
so how would I know which folders have been cached, and would doing the
cacheing slow things down even further?
Thanks for reading, and I hope to get a good discussion going!