New Mail Notification for an Additional Mailbox – Part Deux


As the title suggests this post is a continuation of an entry I made in December of last year. A couple of folks have requested modifications to the original version and I decided to handle those as new posts rather than edit the original.

This post then is for Marcin who requested a couple of changes in a comment he posted a few days ago on the original post. The changes Marcin asked for are

  1. A version that can handle two mailboxes
  2. Box 1 to be on all time
  3. Box 2 to be monitored from 10:00 PM until 6:00 AM only

Meeting Marcin’s requirements is actually quite simple. First, I need to change the code in part 2 of the original article to allow monitoring to be turned on and off at set times. I’ll manage this by adding two properties to the class module, one each for the time that monitoring should begin and end, and introducing a compound IF statement that will compare the current time to the start/end times Marcin sets. Second, I’ll have to modify the code in part 3 of the original to handle multiple mailboxes. Doing that just requires creating and instantiating another object, and setting the properties for both objects. Here then is Marcin’s solution.

The code for this comes in three parts.

Part 1.

Follow these instructions to add this code to Outlook.

  1. Start Outlook
  2. Click Tools > Macro > 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
  9. Close the VB Editor
'On the next line change the file name and path of the sound you want to play.'
Public Const SOUND_TO_PLAY = "C:\Windows\Media\Notify.wav"
Public Const SND_ASYNC = &H1

Public Declare Function sndPlaySound Lib "winmm.dll" Alias "sndPlaySoundA" _
(ByVal lpszSoundName As String, ByVal uFlags As Long) As Long
Public Declare Function MessageBox _
    Lib "User32" Alias "MessageBoxA" _
        (ByVal hWnd As Long, _
        ByVal lpText As String, _
        ByVal lpCaption As String, _
        ByVal wType As Long) _
    As Long


Public Function OpenOutlookFolder(strFolderPath As String) As Outlook.MAPIFolder
    ' Purpose: Opens an Outlook folder from a folder path.'
    ' Written: 4/24/2009'
    ' Author:  BlueDevilFan'
    ' 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

Part 2.

Follow these instructions to add this code to Outlook.

  1. If not already expanded, expand Microsoft Office Outlook Objects
  2. Right-click on Class Modules, select Insert > Class Module
  3. In the Properties panel click on Name and enter FolderMonitor
  4. Copy the code from the Code Snippet box and paste it into the right-hand pane of Outlook’s VB Editor window
  5. Edit the code as needed. I included comments wherever something needs to or can change
  6. Click the diskette icon on the toolbar to save the changes
Private WithEvents olkItems As Outlook.Items
Private timStart As Date, timEnd As Date

Private Sub Class_Terminate()
    Set olkItems = Nothing
End Sub

Public Sub FolderToWatch(objFolder As Outlook.Folder)
    Set olkItems = objFolder.Items
End Sub

Private Sub olkItems_ItemAdd(ByVal Item As Object)
    Dim lngHandle As Long, lngReturn As Long, bolNotify As Boolean
    bolNotify = False
    If Hour(timStart) > Hour(timEnd) Then
        If (Time() >= timStart) Or (Time() <= timEnd) Then
            bolNotify = True
        End If
    Else
        If (Time() >= timStart) And (Time() <= timEnd) Then
            bolNotify = True
        End If
    End If
    If bolNotify Then
        sndPlaySound SOUND_TO_PLAY, SND_ASYNC
        MessageBox &O0, "New message arrived.", olkItems.Parent.Name, vbInformation + vbOKOnly + vbSystemModal
    End If
End Sub

Public Property Let ActivateAt(ByVal datValue As Date)
    timStart = datValue
End Property

Public Property Let DeactivateAt(ByVal datValue As Date)
    timEnd = datValue
End Property

Part 3.

Follow these instructions to add this code to Outlook.

  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 Tools > Trust Center
  7. Click Macro Security
  8. Set Macro Security to “Warnings for all macros”
  9. Click OK
  10. Close Outlook
  11. 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.
Dim objFM1 As FolderMonitor
Dim objFM2 As FolderMonitor

Private Sub Application_Quit()
    Set objFM1 = Nothing
    Set objFM2 = Nothing
End Sub

Private Sub Application_Startup()
    Set objFM1 = New FolderMonitor
    With objFM1
        'Edit the folder path on the next line as needed.'
        .FolderToWatch OpenOutlookFolder("Mailbox - Doe, John\Inbox")
        'Set the times that monitoring should start/end'
        .ActivateAt = #12:00:00 AM#
        .DeactivateAt = #11:59:59 PM#
    End With
    Set objFM2 = New FolderMonitor
    With objFM2
        'Edit the folder path on the next line as needed.'
        .FolderToWatch OpenOutlookFolder("Mailbox - Brown, Bob\Inbox")
        'Set the times that monitoring should start/end'
        .ActivateAt = #10:00:00 PM#
        .DeactivateAt = #06:00:00 AM#
    End With
End Sub
Advertisements

21 comments on “New Mail Notification for an Additional Mailbox – Part Deux

  1. Just spent a few hours cursing why the this wasn’t working for me, but I finally found what was going on.

    I had some macros already set up in ThisOutlookSession and pasted your code at the bottom. That’s where I was going wrong.

    The “Dim objFM1 As FolderMonitor” part need to go right at the top of the editor, above any other macros you have already set up.

    • Hi, Jordan.

      Sorry about that. Glad you worked it out. I don’t include instructions on how to merge my code samples into existing code because, quite frankly, so few people have any code already in Outlook.

  2. I want a help for new mail notification for an additional inbox in 2003.
    would you please help me.
    Thanks in advance.

  3. Still getting the “Compile Error: User-defined type not defined” error regardless of changing the line to MAPI folder. Help (2007)

  4. Hi David,

    Thanks for this code, it works well in our environment.
    Do you happen to know whenewer it is possible with VBA to produce a Desktop Alert instead of MessageBox?
    I do not ask for the exact code only the function to call.

    Thanks in advance,
    Ginger

    • Hi, Ginger.

      The answer is sort of, but not really. Outlook’s object model does not have a built-in method for displaying a desktop notification like the one you see when you receive a new message. However, if you are a skilled enough programmer you could produce something very similar. You’d have to build it outside of Outlook, probably as a DLL, and then call it from inside Outlook.

  5. Thanks for posting this code!
    I am getting the Compile Error: User-defined type not defined also.
    It highlights
    Dim objFM1 As FolderMonitor

    Thanks!

    • Hi, Donnie.

      You’re welcome for the code. I hope it proves useful.

      Concerning the error, did you follow the instructions in part 2 exactly? If so, then you should have a class module called FolderMonitor. The error suggests that this class module doesn’t exist, or is misnamed.

  6. I’ve just modified the code to squash a bug discovered by another reader. The bug in question caused notifications to fail if the start time was greater than the end time (e.g. start at 11:00 PM and end at 7:00 AM). Anyone downloading the code from this point forward will get the corrected version. If you’re already using the code, then replace the code in Part #2 with the new version, close and restart Outlook and you should be in business.

  7. Hi David,

    I’d love to have this set up on my outlook as well, but I seem to be running into some difficulties…

    My first concern is that I run Outlook 2003 (don’t know if this would even be possible for me), and when you mentioned to go to Macro Security and set it to “Warnings for all macros” would that be equivalent to Tools > Macros > Security > “Medium. You can choose whether or not to run potentially unsafe macros.”?

    Also, I run into an error once I open/close Outlook:

    Compile Error: User-defined type not defined

    and it highlights the line:

    Public Sub FolderToWatch(objFolder As Outlook.Folder)

    I hope there’s a way to accomplish this macro for Outlook 2003, that would be a huge help!

    Thanks Dave,

    John

  8. Hey David,

    Totally appreciate your contributions to the community with your programming skills! To extend this modified version a bit further,

    is there a way to also set days for which the script is running? Example is a Helpdesk schedule where someone works Monday, Wednesday, Friday from 10PM – 6AM.

    And not to ask for tooo much but also how we could add a delay in the check, like wait 5 minutes after an e-mail arrives in the case that someone else has already checked/removed the message from the shared Inbox.

    If you could also direct me to a paypal button or any way I can contribute to helping out the site. Please let me know.

    • Hey, Frank.

      I can modify the script to handle the days. Delaying for 5 minutes is a problem though. The script’s action is tied to the event (ItemAdd) that’s triggered when a new message is added to the folder. A 5 minute delay would require setting a timer each time a new message arrives. It’d have to trigger code that checks to see if the message associated with the timer is still there. Depending on the volume of messages the number of timers could quickly get out of control. It’s also a problem because Outlook doesn’t have a built-in timer capability. Another approach would be to change the code entirely so it checks the folder every 5 minutes for unread items that are more than 5 minutes old. It’d display a notification if it found any. I’m not sure how practical either solution is. If the goal is to know about new messages as soon as possible, then this won’t help. You wouldn’t know there was a new message until 5 minutes had passed. A better solution might be to display the notification immediately and have another routine that checks at 5 minute intervals for messages that still haven’t been read. But perhaps I just don’t understand the goal well enough.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s