Link to home
Start Free TrialLog in
Avatar of JohnnyD74
JohnnyD74

asked on

Outlook/Exchange Mailbox Monitor

Hello Folks,

I need to solve a strange problem. We use a program called ConnectWise and are customers send support requests to us that help@ourdomain.com which ConnectWise automatically converts the service tickets. Sometimes emails stuck the service ticket inbox.

I need some kind of mailbox monitoring software that will notify me if any email messages are present in the ConnectWise (Outlook/Exchange) email inbox for more than 60 seconds. Note: ConnectWise parses them out of this account's Outlook inbox folder to another folder (called "processed") immediately when they arrive and a ticket is created if it is functioning properly. If it is not functioning properly they accumulate in this inbox and no tickets are created.

Does anyone know if any tools or solutions I might use to monitor for this condition?
Avatar of David Lee
David Lee
Flag of United States of America image

Hi, JohnnyD74.

I don't know of an off-the-shelf solution for this.  I can do this after a fashion with an Outlook macro.  A macro will only provide monitoring while Outlook is open though.  If you closed Outlook, then the monitoring would stop.  The macro would check the mailbox at a fixed interval (e.g. once a minute) to see if there are any messages in the mailbox that are more than a minute old.  If if found any, then it would notify you.  The macro wouldn't be able to notify you the instant a message turned one minute old, but it would notify within a minute of the message becoming a minute old.  If the solution needs to run all the time and needs to let you know the moment a message has been in the box for a minute, then the solution needs to run at the server.
Avatar of JohnnyD74
JohnnyD74

ASKER

Hi BlueDevinFan,

That would do the trick as I have a machine that Outlook is running on all the time! I have ever set up an Outlook marco though. Do you know how I might set such a macro up and what parameters I would use? Thanks!!!
I can provide the code and instructions.  What sort of notification do you want?  Email, text message, popup, all three, or something else?
An email would be sufficient and great! Thank you.
ASKER CERTIFIED SOLUTION
Avatar of David Lee
David Lee
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Dear Blue Devils Fan,

I am blown away that you went to such lengths to create a macro to address this problem!!! My Deepest thanks and graditite!!!

For some reason however...I can't seem to make it work. I set it up as best I could per your instructions: http://screencast.com/t/X2mKYmD2pm 

Is there anything I might try to make this work? Thanks again.
You're welcome!

The screenshot you linked to shows the code from part #1.  Is the code from part #2 in place as well?  What did you set macro security to?  What version of Outlook are you using?
For some reason I missed part2 but just added it! I am using Outlook 2007 and set the macros via the trust center to your specs as well. It still does not appear to be working. Is there a minor adjustment I can make for Outlook 2007 or would it be better that I install Outlook 2010?
Did you close and restart Outlook after adding the code from part #2?  Also, in part #1 of the code, did you edit the path to the folder you want to monitor?  That's on line #5.  If you have done both, then are you saying that there are items in the mailbox you're monitoring that are more than a minute old, but you aren't being notified?
Hi. Sorry for my delay - busy day at work. I did close and re-open Outlook after adding the code form part2. Here is what I have for line 5:

Const FOLDER_PATH = "Mailbox - AlertDesk\Inbox"

Yes...I do not yet unfortunately get a notification on items in the inbox more than 1 minute old. I waited 10 minutes but still no response.

I am getting the path from here: http://screencast.com/t/jqQnPtvuEu . Is this correct?
And you're certain that there is at least one item in the subject mailbox that's more than a minute old, correct?  If so, then we need to do some debugging.  On the computer where the code is installed, switch to Outlook, then press ALT+F11 to open the VB editor window.  Look for a pane labeled "Immediate" toward the bottom of the VB editor window.  If you don't see that pane, then press CTRL+G to open it.  Once the pane is visible, is there anything written in it?
Yes...right now there are more than 10 items in the inbox. I pressed Control-G and a blank box popped up at the bottom.
Then the code isn't running at all.  What security setting did you choose?
I choose "Warnings for all marcros" and then selected the option to "Enable Macros" when I opened Outlook.
That looks right.  Edit the code in part #2 changing

Private Sub Application_Startup()
    ActivateTimer 1
End Sub

Open in new window


to

Private Sub Application_Startup()
    MsgBox "Running ActivateTimer"
    ActivateTimer 1
End Sub

Open in new window


Close and restart Outlook and let me know what happens.
Hello Blue Devils Fan,

I made that last change you advised but unfortunately....still no go. One question. What window in Outlook are you getting the value on line 5 of your macro?
I made that last change you advised but unfortunately....still no go.

I assume that means that on retarting Outlook you did not see a dialog-box with the words "Running ActivateTimer".  If that's the case, then macros are disabled despite the security setting.  That's very odd, but I have seen it happen before.  Please confirm that you didn't see a dialog-box after restarting Outlook and we'll take it from there.

What window in Outlook are you getting the value on line 5 of your macro?

Do you mean the folder path?  The text I entered is just sample text.  As the comment above that line says, you need to edit it and insert the path to your folder.
Thank you again for your update:-) I am using Microsoft Exchange with Outlook so the folder is actually stored in a SQL database. I am not sure how to referance the path. Could you elaborate on that? I only see an "Enable Macros" dialog box. I produced a video of my settings and the box that pops up here: http://screencast.com/t/eeWGYEVj
JohnnyD74,

A folder path in Outlook is essentially the same as a folder path in the file system.  The one difference being that Outlook folder paths do not include a drive letter.  The path to a folder is a list of all the folders from the root to the target folder with each folder name separated from the preceding folder name by a backslash (i.e. \).  Consider the following folder structure:
 
Mailbox - Doe, John
    - Calendar
    - Inbox
    - Tasks
Personal Folders
    + Marketing
        + Proposals
        + Reviews
    + Projects
        + Project 1
        + Project 2

Open in new window


The path to "Inbox" is "Mailbox - Doe, John\Inbox".
The path to "Reviews" is "Personal Folders\Marketing\Reviews".
The path to "Project 1" is "Personal Folders\Projects\Project 1".
Hi,

I do have the folder path correct judging by your advice...thanks! I have decided to hire a MS Office expert who can get involved over the phone & via a remote session with me since there seems to be more at play here than we can handle via email. I am sure the macro you built will work perfectly - just an issue on my end somewhere. When I come up with the solution, I will post it and give you credit for helping me.

Thanks so much for all of your kind efforts!!!
I turns out that I did not implement this correctly at first and it works now thanks to you!

Thanks again David!
You're welcome, John.  I'm happy I was able to help.
Hope you are still looking at this.  I get the following on debug.

    Set olkFolder = OpenOutlookFolder(FOLDER_PATH).Items
And the Timer fails to deactivate.

Hoping you are still around.  This is something we really need.
Hi, rtrick.

I'm still here, just not as active as I used to be.

Are you sure the path set by the FOLDER_PATH constant is valid?
I know the mailbox is valid and opened on this pc. But I may not have the path correct.

I have tried the following folder paths:

Const FOLDER_PATH = "Mailbox - testcitrix@worldinc.com\Inbox"
Const FOLDER_PATH = "Mailbox - citrix, test\Inbox"
Const FOLDER_PATH = "Mailbox - testcitrix\Inbox"

In Active directory the account is listed as testcitrix.
Add the code below to Outlook.  Once you've done that, select the folder then run the DisplayFolderPath macro.  It will display the path to the selected folder.  What does the folder path look like?

Sub DisplayFolderPath()
    Const SCRIPT_NAME = "Display Folder Path"
    Dim olkFol As Outlook.MAPIFolder
    Set olkFol = Application.ActiveExplorer.CurrentFolder
    MsgBox "The path to the currently selected folder is " & vbCrLf & vbCrLf & olkFol.FolderPath, vbInformation + vbOKOnly, SCRIPT_NAME
    Set olkFol = Nothing
End Sub

Open in new window

it showed \\testcitrix@worldtravelinc.com\Inbox

I edited the code.  now I get

run-time error '91'
Object variable or With block variable not set

Might be getting closer:)
Change this line

Const FOLDER_PATH = "Mailbox - Doe, John\Inbox"

Open in new window


to

Const FOLDER_PATH = "testcitrix@worldtravelinc.com\Inbox"

Open in new window

Perfect.  Thanks!  It works.  

I still have an error message on Outlook close "timer failed to deactivate".  Here is what I have

Private Sub Application_Quit()
    DeactivateTimer
End Sub

Private Sub Application_Startup()
    MsgBox "Running ActivateTimer"
    ActivateTimer 1
End Sub
Also, is there a way to adjust it to scan every 5 minutes looking for anything older than 30 minutes?
To run every 5 minutes change the 60 on line 19 to 5.

To look for items older than 30 minutes change the 1 on line 43 to 30.
When do you get the deactivate timer error?
Thanks.  I get the deactivate timer error when closing outlook.
I did the following:
To run every 5 minutes change the 60 on line 19 to 5.
To look for items older than 30 minutes change the 1 on line 43 to 30.

After about 30 minutes I started getting alerts every couple of seconds.
figured that out.  reversed the items.  still get deactivate error.
Sorry.  The value on line 19 needs to be 300 not 5.  I was thinking the original code had it set to 60 minutes when in fact it was 60 seconds.  

I don't know why you're getting the deactivate error.  The only two causes I can think of are that Outlook is closing too quickly and isn't giving the deactivate code time to complete, or that there are multiple timer ID variables and the deactivation process is trying to use the wrong one.
Thanks, BlueDevilFan.  Great script!