[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 293
  • Last Modified:

Query a Mailbox

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
ISfish
Asked:
ISfish
  • 8
  • 7
1 Solution
 
David LeeCommented:
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
 
ISfishAuthor Commented:
Thanks for the response :)

I would like it to run automatically if possible.
P.S I have no idea how to script :(
0
 
David LeeCommented:
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
ISfishAuthor Commented:
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
 
David LeeCommented:
> 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
 
ISfishAuthor Commented:
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
 
David LeeCommented:
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
 
ISfishAuthor Commented:
I will try it and let you know tomorrow
Thanks Heaps
CT
0
 
David LeeCommented:
No problem.  
0
 
ISfishAuthor Commented:
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
 
David LeeCommented:
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
 
ISfishAuthor Commented:
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
 
David LeeCommented:
How does that factor in to search?  Are you saying that you want the code to search all sub-folders?  
0
 
ISfishAuthor Commented:
I did not want it to search sub folders.
Looks like it is all sweet.
0
 
David LeeCommented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now