Solved

Outlook/Exchange Mailbox Monitor

Posted on 2014-03-03
38
961 Views
Last Modified: 2015-10-29
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?
0
Comment
Question by:JohnnyD74
  • 17
  • 11
  • 10
38 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 39902999
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.
0
 

Author Comment

by:JohnnyD74
ID: 39903627
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!!!
0
 
LVL 76

Expert Comment

by:David Lee
ID: 39903651
I can provide the code and instructions.  What sort of notification do you want?  Email, text message, popup, all three, or something else?
0
 

Author Comment

by:JohnnyD74
ID: 39903661
An email would be sufficient and great! Thank you.
0
 
LVL 76

Accepted Solution

by:
David Lee earned 500 total points
ID: 39904100
Here's my proposed solution.  This solution uses a Windows timer to run a process once per minute.  You can change the interval if you want.  I went with one minute because that's what you mentioned in your question.  When the timer fires it runs some code that checks the designated Outlook folder for items that are more than one minute old.  If it finds any, then it sends you an email telling you how many items it found that are more than a minute old.  If it doesn't find any items more than a minute old, then it does nothing.  Outlook must be open and running for this to work.  If Outlook is closed, then you won't get any notifications.  This macro starts automatically.  DO NOT attempt to run it manually.  It starts when Outlook is opened and shuts itself off when Outlook is closed.

I gave this a quick test on my system and it appeared to work properly.  Please be sure to test it in your environment before relying on it.

This solution comes in two parts.  

This is part #1.  Follow these instructions to add this portion of the code to Outlook.

1.  Start Outlook
2.  Press ALT+F11 to open the Visual Basic editor
3.  If not already expanded, expand Microsoft Office Outlook Objects
4.  If not already expanded, expand Modules
5.  Select an existing module (e.g. Module1) by double-clicking on it or create a new module by right-clicking Modules and selecting Insert > Module.
6.  Copy the code from the Code Snippet box and paste it into the right-hand pane of Outlook's VB Editor window
7.  Edit the code as needed.  I included comments wherever something needs to or can change
8.  Click the diskette icon on the toolbar to save the changes

Declare Function SetTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerfunc As Long) As Long
Declare Function KillTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long) As Long

'On the next line, edit the path to the Outlook folder you want to monitor
Const FOLDER_PATH = "Mailbox - Doe, John\Inbox"
'On the next line, edit the subject of the notification email
Const MSG_SUBJECT = "Items Stuck in the Queue"
'On the next line, edit the email address of the person notification are sent to
Const MSG_TO = "john.doe@company.com"
Const MACRO_NAME = "Monitor Folder for Old Messages"

'Need a timer ID to eventually turn off the timer. If the timer ID <> 0 then the timer is running'
Private lngTimerID As Long, _
       olkFolder As Outlook.Items

Public Sub ActivateTimer(ByVal lngMinutes As Long)
    Set olkFolder = OpenOutlookFolder(FOLDER_PATH).Items
    'The SetTimer call accepts milliseconds, so convert to minutes'
    lngMinutes = lngMinutes * 1000 * 60
    'Check to see if timer is running before call to SetTimer
    If lngTimerID <> 0 Then Call DeactivateTimer
    lngTimerID = SetTimer(0, 0, lngMinutes, AddressOf TriggerTimer)
    If lngTimerID = 0 Then
        MsgBox "The timer failed to activate.", vbCritical + vbOKOnly, MACRO_NAME
    End If
End Sub

Public Sub DeactivateTimer()
    Dim lSuccess As Long
    lSuccess = KillTimer(0, lngTimerID)
    If lSuccess = 0 Then
        MsgBox "The timer failed to deactivate.", vbCritical + vbOKOnly, MACRO_NAME
    Else
        lngTimerID = 0
    End If
    Set olkFolder = Nothing
End Sub

Public Sub TriggerTimer(ByVal hwnd As Long, ByVal uMsg As Long, ByVal idevent As Long, ByVal Systime As Long)
    Dim lngCnt As Long, olkMsg As Outlook.MailItem, olkNot As Outlook.MailItem
    Debug.Print Now & vbTab & "Fired"
    For Each olkMsg In olkFolder
        If DateDiff("n", olkMsg.ReceivedTime, Now) > 1 Then
            lngCnt = lngCnt + 1
        End If
    Next
    If lngCnt > 1 Then
        Set olkNot = Application.CreateItem(olMailItem)
        With olkNot
            .Subject = MSG_SUBJECT
            .Body = "There are " & lngCnt & " items in the mailbox that are more than 1 minute old."
            .Recipients.Add MSG_TO
            .Send
        End With
    End If
    Set olkNot = Nothing
    Set olkMsg = Nothing
End Sub

Public Function OpenOutlookFolder(strFolderPath As String) As Outlook.MAPIFolder
    ' Purpose: Opens an Outlook folder from a folder path.
    ' Written: 4/24/2009
    ' Author:  David Lee
    ' Outlook: All versions
    Dim arrFolders As Variant, _
        varFolder As Variant, _
        bolBeyondRoot As Boolean
    On Error Resume Next
    If strFolderPath = "" Then
        Set OpenOutlookFolder = Nothing
    Else
        Do While Left(strFolderPath, 1) = "\"
            strFolderPath = Right(strFolderPath, Len(strFolderPath) - 1)
        Loop
        arrFolders = Split(strFolderPath, "\")
        For Each varFolder In arrFolders
            Select Case bolBeyondRoot
                Case False
                    Set OpenOutlookFolder = Outlook.Session.Folders(varFolder)
                    bolBeyondRoot = True
                Case True
                    Set OpenOutlookFolder = OpenOutlookFolder.Folders(varFolder)
            End Select
            If Err.Number <> 0 Then
                Set OpenOutlookFolder = Nothing
                Exit For
            End If
        Next
    End If
    On Error GoTo 0
End Function

Open in new window


This is part #2.  Follow these instructions to add this portion of the code to Outlook.  I made the assumption that you are using Outlook 2010.  If you're using a different version, then these instructions might be a little off.

1.  If not already expanded, expand Microsoft Office Outlook Objects and click on ThisOutlookSession
2.  Copy the code from the Code Snippet box and paste it into the right-hand pane of Outlook's VB Editor window
3.  Edit the code as needed.  I included comment lines wherever something needs to or can change
4.  Click the diskette icon on the toolbar to save the changes
5.  Close the VB Editor
6.  Click File and select Options
7.  Click Trust Center
8. Click the Trust Center Settings button
9. Click Macro Settings
10. Change the Macro Settings to "Notifications for all macros"
11. Click OK
12. Click OK
13. Close Outlook
14. Start Outlook.  Outlook will display a dialog-box warning that ThisOutlookSession contains macros and asking if you want to allow them to run.  Say yes.

Private Sub Application_Quit()
    DeactivateTimer
End Sub

Private Sub Application_Startup()
    ActivateTimer 1
End Sub

Open in new window

0
 

Author Comment

by:JohnnyD74
ID: 39905827
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.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 39905955
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?
0
 

Author Comment

by:JohnnyD74
ID: 39906760
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?
0
 
LVL 76

Expert Comment

by:David Lee
ID: 39906798
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?
0
 

Author Comment

by:JohnnyD74
ID: 39908389
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?
0
 
LVL 76

Expert Comment

by:David Lee
ID: 39908870
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?
0
 

Author Comment

by:JohnnyD74
ID: 39909913
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.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 39910784
Then the code isn't running at all.  What security setting did you choose?
0
 

Author Comment

by:JohnnyD74
ID: 39910905
I choose "Warnings for all marcros" and then selected the option to "Enable Macros" when I opened Outlook.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 39920064
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.
0
 

Author Comment

by:JohnnyD74
ID: 39925747
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?
0
 
LVL 76

Expert Comment

by:David Lee
ID: 39925879
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.
0
 

Author Comment

by:JohnnyD74
ID: 39926708
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
0
 
LVL 76

Expert Comment

by:David Lee
ID: 39944832
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".
0
Promote certifications in your email signature

Has your company recently won an award or achieved a certification? They'll no doubt want to show it off. Email signature images used to promote certifications & awards can instantly establish credibility with a recipient and provide you with numerous benefits.

 

Author Comment

by:JohnnyD74
ID: 39945625
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!!!
0
 

Author Closing Comment

by:JohnnyD74
ID: 39951358
I turns out that I did not implement this correctly at first and it works now thanks to you!

Thanks again David!
0
 
LVL 76

Expert Comment

by:David Lee
ID: 39951712
You're welcome, John.  I'm happy I was able to help.
0
 

Expert Comment

by:rtrick
ID: 41060864
Hope you are still looking at this.  I get the following on debug.

    Set olkFolder = OpenOutlookFolder(FOLDER_PATH).Items
0
 

Expert Comment

by:rtrick
ID: 41068147
And the Timer fails to deactivate.

Hoping you are still around.  This is something we really need.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 41072525
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?
0
 

Expert Comment

by:rtrick
ID: 41086995
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.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 41092327
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

0
 

Expert Comment

by:rtrick
ID: 41098024
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:)
0
 
LVL 76

Expert Comment

by:David Lee
ID: 41101184
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

1
 

Expert Comment

by:rtrick
ID: 41118805
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
0
 

Expert Comment

by:rtrick
ID: 41122210
Also, is there a way to adjust it to scan every 5 minutes looking for anything older than 30 minutes?
0
 
LVL 76

Expert Comment

by:David Lee
ID: 41131505
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.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 41131506
When do you get the deactivate timer error?
0
 

Expert Comment

by:rtrick
ID: 41134648
Thanks.  I get the deactivate timer error when closing outlook.
0
 

Expert Comment

by:rtrick
ID: 41136004
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.
0
 

Expert Comment

by:rtrick
ID: 41136231
figured that out.  reversed the items.  still get deactivate error.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 41139170
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.
0
 

Expert Comment

by:rtrick
ID: 41142967
Thanks, BlueDevilFan.  Great script!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Exchange server is not supported in any cloud-hosted platform (other than Azure with Azure Premium Storage).
Not sure what the best email signature size is? Are you worried about email signature image size? Follow this best practice guide.
In this video we show how to create a Distribution Group in Exchange 2013. We show this process by using the Exchange Admin Center. Log into Exchange Admin Center.: First we need to log into the Exchange Admin Center. Navigate to the Recipients >>…
This video discusses moving either the default database or any database to a new volume.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now