Outlook Prototyping - Your Suggestions

[Guest]

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!

 

[David C. Holley]

What is the SPECIFIC reason for creating an Outlook imitation as opposed
to using Outlook itself.

 

[Guest]

Hi David,

Unspecifically, it's more of a challenge than anything. I've proved that it
will work, and now I want to speed things up. Refine things. Perhaps
increase my general knowledge on different approaches that people have to
outlook, or data storage in general.

Specifically:

I have produced a database that manages customer transactions. If one of
these customers sends an email to a communal mailbox, then any operator of
the database should be able to:

- Search for tha customer email.
- 'Attach' that email to that transaction record (e.g. save the file as an
*.msg to a shared network location, and store the location to the file in the
transaction record)
- Open the stored file.

This was the specification from my client who I produced the database for,
and what I've based a prototype on. I'd love any alternative ideas on
acheving the above aims.

Simon

 

[John Griffiths]

What event are you using?
It looks like an Access event.

I moved the object declaration to form level.
The WithEvents allows the events to selected from the module window.

Private WithEvents tvxObj As TreeView

Selecting tvxFolders from the 1st combo on the form code module
Then selecting NodeClick from the second combo box.
Will give you the event with parameter Node of the selected item, no lookups
required.

Private Sub tvxFolders_NodeClick(ByVal Node As Object)
Debug.Print "Node(" & Node.Key &")=" & Node.Text

End Sub

===
Another speed up is not to fully load up the treeview and expand it as the
user clicks on the nodes.
===
Regards John

 

[Guest]

Hi John, thanks for the comment.

All of my code is in a form class, and the treeview, listview and other
object variables (like olApp for outlook) are declared at form level.

I am using the Form_Load event, and the treeview node click event already
(using trvFolders.SeletedItem instead of Node.Key - is there any difference
in efficiency between the two?).

I'm not sure what benefit WithEvents would give me. I don't need to monitor
the treeview for changes, as the data within it is predominently static.

Another speed up is not to fully load up the treeview and expand it as the
user clicks on the nodes.

I see what you mean but perhaps I need to rephrase my initial post.

If my Outlook session has multiple folders, it takes Access an age to go
through to find the right folder that contains my email. Is there a quicker
way to get to the folder selected, as opposed to recursiveness?

 

[John Griffiths]

In post

Hi John, thanks for the comment.

All of my code is in a form class, and the treeview, listview and other
object variables (like olApp for outlook) are declared at form level.

I thought that might be the case.

I am using the Form_Load event, and the treeview node click event already
(using trvFolders.SeletedItem instead of Node.Key - is there any difference
in efficiency between the two?).

Just that NodeClick passes a Node object in it's
parameters so you don't have to declare and Set one.

I'm not sure what benefit WithEvents would give me. I don't need to monitor
the treeview for changes, as the data within it is predominently static.

My mistake, got my feathers in a twist.

I see what you mean but perhaps I need to rephrase my initial post.

If my Outlook session has multiple folders, it takes Access an age to go
through to find the right folder that contains my email. Is there a quicker
way to get to the folder selected, as opposed to recursiveness?

If you used olSelectedFolder.EntryID
instead of olSelectedFolder.FolderPath
when creating user inteface.

Set objNode = tvxObj.Nodes.Add( _
olFolderParent.EntryID, _
tvwChild, _
olSelectedFolder.EntryID, _
olSelectedFolder.Name, _
1)

Then I think that

Sub tvxFolders_NodeClick(ByVal Node As Object)
Set olFolder olNS.GetFolderFromID(Node.Key)

Call LoadOutlookFolderData(olFolder)
End Sub

Private Sub LoadOutlookFolderData(ByRef olSelectedFolder As MAPIFolder)
On Error GoTo LoadOutlookFolderData_Err
'-----------------------------------------------------
' need to clear listview of items from previous chosen folder
'-----------------------------------------------------
If olSelectedFolder.DefaultItemType <> olMailItem Then
Exit Sub
End If
'=====================================================
For Each olItem In olSelectedFolder.Items
If bCancelLoad Then 'Ensure that the user hasn't cancelled
processing
Exit Sub
End If

'LoadListItems is a function that adds details to the listview..
Call LoadListItems(olItem)

DoEvents
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

would give the results you need.

Heres hoping John