Monitor Multiple Email Folders for ItemAdd

  • Thread starter Thread starter marjk
  • Start date Start date
M

marjk

I have an Outlook VBA solution for sending emails arriving in a shared
folder to an Oracle database. However, now they want to monitor
another folder for emails and send them to a database. I just use the
VBA code to pull a form which does all the real work. Can I monitor
two folders using VBA? Also, could these two programs conflict? Would
there be any advantage to create an add-in instead? I played with an
Exchange Script, but my code does not work without Oracle client
installed and I do not want to install Oracle client on the Exchange
Server.

Code Snippet:
Private WithEvents objItems As Items

Private Sub ScanEmails()

'Pause 15 seconds for Rules to process_
'and if multiple emails have to be looped_
'with the Referral Mail Process form

'Set duration time
PauseTime = 15
'Set start time
Start = Timer
Do While Timer < Start + PauseTime
'Yield to other processes
DoEvents
Loop
'Set end time
Finish = Timer


Set oApp = CreateObject("Outlook.Application")
Set oNameSpace = oApp.GetNamespace("MAPI")
'Initialize variable
Set objFolder = Nothing
'Resolve Recipient object to GetSharedDefaultFolder
Set myRecipient = oNameSpace.CreateRecipient("MyMailbox")
myRecipient.Resolve
If myRecipient.Resolved Then
'Get Shared Folder Inbox for MyMailBox
Set objFolder = oNameSpace.GetSharedDefaultFolder(myRecipient,
olFolderInbox)
End If
'Get Items in Folder
Set objItems = objFolder.Items
'Open Referral Mail Process Form to process emails
Set TelemarkItem =
objFolder.Items.Add("IPM.Note.Referral Mail Process")
TelemarkItem.Display

TelemarkItem Code Snippet:
Sub Item_Open()

On Error Resume Next
' start CDO session
Set objSession = CreateObject("MAPI.Session")
objSession.Logon "", "", False, False


Set objInfoStores = objSession.InfoStores
' Loop through the infostores
For Each objInfoStore In objInfoStores
If Trim(objInfoStore.Name) = "Mailbox - Vacation Specialist" Then
' Open the root folder
Set objRootFolder = objInfoStore.RootFolder
' Open the Inbox folder
Set objFolder = objRootFolder.Folders("Inbox")
End If
Next

'Formating Date in correct format for Oracle
strDate = Day(Date) & "-" & MonthName(Month(Date), True) & "-" &
Right(Year(Date), 2)



Set objMsg = objFolder.Messages
'Loop through messages taking one away each time
For i = objMsg.Count to 1 Step -1
Set objMsg = objFolder.Messages(i)
strSender = objMsg.Sender.Address
strBody = objMsg.Text
strSubject = objMsg.Subject


'Delete all apostrophes in Message Body, so body can go to Oracle
without missing comma error.
strQoute = "'"
intPOSQuote = Instr(1, strBody, strQoute , 1)
If intPOSQuote > 0 Then
strBody = Replace(strBody, "'", " ")
objMsg.Update
End If
'Do this do that yada yada

'Create connection to Oracle
Set ADOConn = Item.Application.CreateObject("ADODB.Connection")
'send email to Oracle.

'move message when done with tom foolery.
Set DestFolder = objFolder.Folders("Archive Inbound")
Set objMoveMsg = objMsg.MoveTo(DestFolder.ID)
 
You could set up different object variables declared WithEvents for each
Items collection you are interested in. Each one would fire separate ItemAdd
events.

Distributing a COM addin would most certainly be much easier than trying to
distribute macro code.

What I usually do in this type of situation is to set up a class module that
declares an Items collection WithEvents in it and has properties to set and
read the current folder and anything else I need. Then I instantiate an
instance of that class for each Items collection I want to monitor and add
it to a collection. That way I can monitor Items collections that are
selected on the fly and I'm not limited to as many as I declare ahead of
time. I use similar classes and collections for lots of other things
including variable numbers of buttons on menus, Explorers and Inspectors and
so on.

Download and examine the ItemsCB VB 6 COM addin sample from the Resources
page at www.microeye.com and see how that template sets up an Explorers
wrapper collection, which is necessary to work around an Outlook bug. That
model and the Inspector wrapper model I have posted at
http://www.slovaktech.com/code_samples.htm#InspectorWrapper can be used as a
model for your Items wrapper collection and class.
 
Ken,
Thank you for all the information. I will look at the two samples you
have. I apprciate all the help. Do you think there is a chance the
ItemAdd could conflict with each other with different mailboxes?
 
No, it won't conflict if you set up separate class modules to encapsulate
the handling of those ItemAdd events and add all your classes to a
collection. That way they all fire events only in their respective class
modules and there's no "cross talk".

Just follow the models I pointed you to.
 
Back
Top