Solved

Query a Mailbox

Posted on 2007-03-26
15
278 Views
Last Modified: 2010-04-08
Hi All,

My Helpdesk has an SLA of 1hour for logging emails into our job logging system. I want to be able to be alerted of any emails that have been sitting in the mailbox for more than an hour. Does anyone know a script or a tool that can do this.

We are using Excahnge 2003 and Outlook 2003?
Cheers
CT
0
Comment
Question by:ISfish
  • 8
  • 7
15 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 18798683
Hi, ISfish.

It's simple enough to create such a query.  How do you envision it operating?  By that I mean do you want to run it manually or should it check at periodic intervals?
0
 

Author Comment

by:ISfish
ID: 18820798
Thanks for the response :)

I would like it to run automatically if possible.
P.S I have no idea how to script :(
0
 
LVL 76

Expert Comment

by:David Lee
ID: 18832311
I've been thinking about this and I may have been hasty in proposing a scripted solution.  I think we can accomplish what you want with a Search Folder.

1.  Right-click on Search Folders.
2.  Select New Search Folder.
3.  Select Create a custom Search Folder
4.  Click the Choose button.
5.  Give the Search Folder a name
6.  Click the Criteria button.
7.  Click the Advanced tab.
8.  Click the Field pulldown.
9.  Select Date/Time fields.
10. Select Received.
11. Click the Condition pulldown
12. Select on or before
13. Click in the Value field
14. Type 1 hour ago
15. Click the Add to List button
16. Click the OK button
17. By default the folder this works against is your Inbox.  Change to the folder you want the search applied to.
0
 

Author Comment

by:ISfish
ID: 18833826
Thanks for that search function. It allows me to see emails that have been received within one hour. Is there a way to get an email if this contition exists?

For example;
If an email has been sitting in the helpdesk email box for over 1 hour, we have breached our SLA and our manager wants to be notified of this.

I think a script needs to be done :(

CT
0
 
LVL 76

Expert Comment

by:David Lee
ID: 18840871
> Thanks for that search function. It allows me to see emails that have been received within one hour.
It should be showing all the messages an hour or older.  

> Is there a way to get an email if this contition exists?
Yes, but that does require a script, something like the one below.  There are a couple of hitches though.  First, sending messages via code is going to trigger Outlook's built in security.  When that happens a dialog-box pops up warning that an application is attempting to send and asking for your permission to allow it to continue.  You have to click the "yes" button to allow it to continue.  Outlook security cannot be turned off, but tehre are work arounds.  Unfortunately, these usually involve the use of third-party tools not all of which are free.  The second issue is how to run this process at regular intervals.  Outlook doesn't have a natvie scheduling capability.  I've sometimes used a scheduled task's reminder to trigger events, resetting the reminder each time it fires.  There are other code snippets we can use to schedule something, or we could take the code out of Outlook and run it from Windows task scheduler.  That brings up the third issue.  Who is going to run this and how often should it run?  Whether we run it from inside or outside of Outlook the process still requires Outlook, an Outlook profile, and logging in to Outlook.  In other words, it cannot run as an unattended service.  All this aside, follow these instructions to use this code.

1.  Start Outlook
2.  Click Tools->Macro->Visual Basic Editor
3.  If not already expanded, expand Modules and click on Module1
4.  Copy the code below and paste it into the right-hand pane of the VB editor window
5.  Edit the code as needed.  I placed comment lines where things need to be changed
6.  Click the diskette icon on the toolbar to save changes
7.  Close the VB Editor
8.  Click Tools->Macro->Security
9.  Set the Security Level to Medium
10. Run the macro

Sub QueryMessageAge()
    Dim olkFolder As Outlook.MAPIFolder, _
        olkOverdue As Outlook.Items, _
        olkItem As Outlook.MailItem, _
        olkMessage As Outlook.MailItem, _
        strMessage As String, _
        strQuery As String
    Set olkFolder = Session.GetDefaultFolder(olFolderInbox)
    strQuery = "[ReceivedTime] <= '" & Format(DateAdd("h", -1, Now()), "ddddd h:nn AMPM") & "'"
    Set olkOverdue = olkFolder.Items.Restrict(strQuery)
    If olkOverdue.Count > 0 Then
        For Each olkItem In olkOverdue
            strMessage = strMessage & "<a href=""outlook:" & olkItem.EntryID & """>" & olkItem.Subject & "</a><br>"
        Next
        Set olkMessage = Application.CreateItem(olMailItem)
        With olkMessage
            'Change the recipient's address as desired
            .Recipients.Add "someone@company.com"
            'Change the subject as desired
            .Subject = "Overdue Items"
            .HTMLBody = strMessage
            .Send
        End With
    End If
    Set olkMessage = Nothing
    Set olkItem = Nothing
    Set olkOverdue = Nothing
    Set olkFolder = Nothing
End Sub
0
 

Author Comment

by:ISfish
ID: 18887361
Thanks heaps for the script.

I tried to run the Macro and got the message:
Run-time error '13'
Type mismatch

I clicked Debug and it highlighted Next

Any ideas?

CT
0
 
LVL 76

Accepted Solution

by:
David Lee earned 500 total points
ID: 18888312
Sorry.  Try this.

Sub QueryMessageAge()
    Dim olkFolder As Outlook.MAPIFolder, _
        olkOverdue As Outlook.Items, _
        olkItem As Object, _
        olkMessage As Object, _
        strMessage As String, _
        strQuery As String
    Set olkFolder = Session.GetDefaultFolder(olFolderInbox)
    strQuery = "[ReceivedTime] <= '" & Format(DateAdd("h", -1, Now()), "ddddd h:nn AMPM") & "'"
    Set olkOverdue = olkFolder.Items.Restrict(strQuery)
    If olkOverdue.Count > 0 Then
        For Each olkItem In olkOverdue
            strMessage = strMessage & "<a href=""outlook:" & olkItem.EntryID & """>" & olkItem.Subject & "</a><br>"
        Next
        Set olkMessage = Application.CreateItem(olMailItem)
        With olkMessage
            'Change the recipient's address as desired
            .Recipients.Add "someone@company.com"
            'Change the subject as desired
            .Subject = "Overdue Items"
            .HTMLBody = strMessage
            .Send
        End With
    End If
    Set olkMessage = Nothing
    Set olkItem = Nothing
    Set olkOverdue = Nothing
    Set olkFolder = Nothing
End Sub
0
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

 

Author Comment

by:ISfish
ID: 18888494
I will try it and let you know tomorrow
Thanks Heaps
CT
0
 
LVL 76

Expert Comment

by:David Lee
ID: 18888527
No problem.  
0
 

Author Comment

by:ISfish
ID: 18903297
I know that I am being a bit annoying now, but is there a way to specify the mail messages within the inbox and not the folders within the inbox?

CT
0
 
LVL 76

Expert Comment

by:David Lee
ID: 18904481
Sorry, I'm not understanding what you mean.  This code finds all of the messages in a given folder (e.g. Inbox) that are an hour or more old.  I don't understand what you mean when you say "... not the folders within the Inbox".  This code doesn't find folders nor does it search subfolders.
0
 

Author Comment

by:ISfish
ID: 18914947
Sorry about that.
We have our Inbox then there are folders within the Inbox. For example: In our helpdesk inbox there are folders called Jan, Feb, March, Apr, Current Week etc..

We manually move emails into this folder once read so there are no rules that automatically move the emails
0
 
LVL 76

Expert Comment

by:David Lee
ID: 18916588
How does that factor in to search?  Are you saying that you want the code to search all sub-folders?  
0
 

Author Comment

by:ISfish
ID: 18922307
I did not want it to search sub folders.
Looks like it is all sweet.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 18923262
I'm still confused.  The code is not designed to search sub-folders and I don't see any way that it could be doing that.  This is the line of code that actually does the search:

    Set olkOverdue = olkFolder.Items.Restrict(strQuery)

It can only search a single folder.  In this case that lone folder is the Inbox.  Each sub-folder is a separate folder object with its own Items collection.  To search sub-folders we'd have to have a line like the one above for each sub-folder or we'd have to put that line in a loop and process all the entries in the SubFolders property of the Inbox.  

Are you saying that the code is definitely searching sub-folders too, or were you just expressing concern that it looked like it could process sub-folders too?
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

Use these top 10 tips to master the art of email signature design. Create an email signature design that will easily wow recipients, promote your brand and highlight your professionalism.
This process describes the steps required to Import and Export data from and to .pst files using Exchange 2010. We can use these steps to export data from a user to a .pst file, import data back to the same or a different user, or even import data t…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

708 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

17 Experts available now in Live!

Get 1:1 Help Now