Solved

Help with script to tag messages on a group mailbox

Posted on 2008-10-15
21
407 Views
Last Modified: 2010-04-21
I need some help with a very annoying thing
The scenario:
We have a group mailbox that several users can use both to see incoming mail and to send from. It's sort of a support mailbox. We sort the incoming mails after categories. If there is an incoming mail that one of us takes well then it's moved to his category and marked as read.
Lately we have gotten a lot of mails marked as read by someone and we can't tell if the mail has been fixed or not and we can't see who marked it as read either.
My question is to you all experts. Can it be fixed with a script so that if someone marks a mail as read that the email is edited with that persons userid.
Environment: Exchange/ Outlook 2003 on Windows XP Pro
0
Comment
Question by:Madjax
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 9
21 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 22720049
Hi, Madjax.

The answer is "yes", but I'm not sure how practical the approach is.  The code below monitors a folder and watches for an item to change.  When a change occurs Outlook fires the ItemChange event.  The code traps that event and gives us an opportunity to take action.  First it checks to see if the item is marked as read.  If it is, then to prevent tagging the item as read by the current user a second time, i.e. in case it had been read previously and this change had nothing to do with changing the status of read, the code checks to see if a category is already set.  If one is set, then the code takes no action.  Otherwise, it sets a category using the current user's name and saves the item.

This approach is flawed because a user could change the category name at any time to anything they want.  So long as there's anything entered in category the code is goign to fall through.  If you need to guard against that, then you could add code that'd send write the initial read to a log file, send a message to someone, or any other action you can think of that'd allow you to maitain an audt trail.

The code must go in the ThisOutlookSession module in Outlook and has to be installed on the computer of every user accessing the folder.
Public WithEvents olkFolder As Outlook.Items
 
Private Sub Application_MAPILogonComplete()
    'Change the folder path on the following line to that of the fodler you want to monitor
    Set olkFolder = OpenOutlookFolder("SomeFolderPath").Items
End Sub
 
Private Sub olkFolder_ItemChange(ByVal Item As Object)
    'If the item is marked as read
    If Item.UnRead = False Then
        'If no category is set already
        If Item.Categories = "" Then
            'Set the category and save the change
            Item.Categories = "BlueDevilFan"     '<- Change the name '
            Item.Save
        End If
    End If
End Sub
 
Function IsNothing(obj)
  If TypeName(obj) = "Nothing" Then
    IsNothing = True
  Else
    IsNothing = False
  End If
End Function
 
Function OpenOutlookFolder(strFolderPath As String) As Outlook.Folder
    Dim arrFolders As Variant, _
        varFolder As Variant, _
        olkFolder As Outlook.MAPIFolder
    On Error GoTo ehOpenOutlookFolder
    If strFolderPath = "" Then
        Set OpenOutlookFolder = Nothing
    Else
        If Left(strFolderPath, 1) = "\" Then
            strFolderPath = Right(strFolderPath, Len(strFolderPath) - 1)
        End If
        arrFolders = Split(strFolderPath, "\")
        For Each varFolder In arrFolders
            If IsNothing(olkFolder) Then
                Set olkFolder = Session.Folders(varFolder)
            Else
                Set olkFolder = olkFolder.Folders(varFolder)
            End If
        Next
        Set OpenOutlookFolder = olkFolder
    End If
    On Error GoTo 0
    Exit Function
ehOpenOutlookFolder:
    Set OpenOutlookFolder = Nothing
    On Error GoTo 0
End Function

Open in new window

0
 
LVL 10

Author Comment

by:Madjax
ID: 22721189
Thanks for the fast response
I wondering a thing though. I am not sure I understood what you said above.The emails that get changed to read don't have any category on them. All incoming mails don't have a category set on them when they first come in unless it's a reply from before that we answered when categorized. My coworkers puts the new emails in their category (a name they choose) and then read it. Thats how it works now. Will this script help to identify the ones that make the emails read, the ones that stay read in the (none) category?
If possible, I would like the script to whenever someone makes a mail read, it should create a category with their name/userid from LDAP and move the mail into that category. That would be nice
I hope I don't kill you on this one BlueDevilFan ...hehe
0
 
LVL 76

Expert Comment

by:David Lee
ID: 22721936
No problem.  I like interesting questions.

I was explaining that the process is flawed because Outlook's object model doesn't give us the fine control we need to determine what changed in the item when the ItemChange event fires.  Let's look at two scenarios, one where an item is being read for the first time and the other for an item being changed, say adding a category, at some point well after it was initially read.

Scenario 1 - Initial Read
A user opens the item which causes the Unread property to change.  The change causes the ItemChange event to fire, but we don't know what changed in the item.  So, the code tests the Unread property to see if the it's false (read).  It is.  The code then checks to see if any categories are set.  None are.  The code assumes that means that this is a first read and adds a category, the name of the user who read the item.

Scenario 2 - Subsequent Read and Change
A user opens the item, reads it, and decides to add a category.  In the process of editing they mistakenly overwrite the existing category, the name of the user who initially read the item, with their new category.  The change triggers the ItemChange event.  The code can't tell that this change had nothing to do with the read/unread status of the item.  So, it checks the Unread property and sees that the item has been read.  It then looks at the category property and sees that it is not blank.  The code falls through and exits.  You've just lost the name of the person that read the message initially.

There are other combinations of events that could cause problems too, like clearing the category field entirely and marking the item unread.  The best solution is to add two custom fields to all mail items, one to mark an item as initially read and the other to capture the reader's name.

I'm not sure which LDAP name you mean.  Changing line #14 of the code to the line in the snippet will insert the current user's name based on the Outlook login which should correspond to the LDAP displayName.
Item.Categories = Session.CurrentUser

Open in new window

0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 10

Author Comment

by:Madjax
ID: 22723711
Nice!
How can I apply this script to Outlook, make it run? I am not so experienced when it comes to script stuff in Outlook. I can to some extent understand what a code does, but not write it myself.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 22723878
No problem.  Here's what you need to do to use the script.

1.  Start Outlook
2.  Click Tools->Macro->Visual Basic Editor
3.  If not already expanded, expand Microsoft Outlook Objects in the Project pane, then click on ThisOutlookSession
4.  Copy and paste the script into the right-hand pane of the VB Editor
5.  Edit the code as needed.  I included a comment for any line that needs editing.
6.  Click the diskette icon on the toolbar to save the changes
7.  Close the VB Editor
8.  Click Tools->Macro->Security
9.  Set Security Level to Medium
10.  Close Outlook
11.  Start Outlook
12.  A dialog-box will appear telling you the ThisOutlookSession contains macros and asking if you want to enable them.  Say yes.

You're ready to go.  

0
 
LVL 10

Author Comment

by:Madjax
ID: 22728256
I tried it, but got an error for this bolded row below>
Compile error
User-defined type not defined
Function OpenOutlookFolder(strFolderPath As String) As Outlook.Folder
0
 
LVL 76

Accepted Solution

by:
David Lee earned 500 total points
ID: 22729072
Sorry, that's the version for 2007.  I forgot to modify it for 2003.  Change Outlook.Folder to Outlook.MAPIFolder.
0
 
LVL 10

Author Comment

by:Madjax
ID: 22729156
got the error>
Run-time error '91'
Object variable or With block variable not set
0
 
LVL 10

Author Comment

by:Madjax
ID: 22729169
compaining about this one> Set olkFolder = OpenOutlookFolder("SomeFolderPath").Items
0
 
LVL 76

Expert Comment

by:David Lee
ID: 22729244
Did you change the folder path?  SomeFolderPath has to be replaced with the path to your folder.  I don't know what that is.
0
 
LVL 10

Author Comment

by:Madjax
ID: 22729411
Ok, but how is the format? I tried with most in there and it doesn't take it. I have 2 mailboxes mapped in my Outlook. Primary=my own personal one, Secondary=the group mailbox I want to have this on
0
 
LVL 10

Author Comment

by:Madjax
ID: 22729418
it's the Inbox folder I wish this on.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 22729477
The path to a folder in Outlook is just like the path to a file folder in Explorer, only without the drive letter.  It begins with top level folder and moves through all the intervening folders, separating each folder name by a backslash (i.e. \) character.  So, if the name of the shared mailbox is John Doe, then the path would look something like

    Mailbox - Doe, John\Inbox
0
 
LVL 10

Author Comment

by:Madjax
ID: 22730226
It works, but it made 2 categories.....one for my firstname and one for my lastname. Both had the same email.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 22730309
Oops, I guess that's because the names are comma separated values and commas are used as category separators too.  I hadn't thought about that.  Easy enough to fix though.  Change line #14 to the code in the snippet.  That'll eliminate the comma.
Item.Categories = Replace(Session.CurrentUser, ",", "")

Open in new window

0
 
LVL 10

Author Comment

by:Madjax
ID: 22730411
ok it works better, but now the emails are locked in place in the newly created category and I can't move them out from there. Did you put a lock on that or was it unexpected?
Overall it works as planned.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 22730484
Sorry, I don't understand what you mean by "locked in place".  There are no locking mechanisms in Outlook.
0
 
LVL 10

Author Comment

by:Madjax
ID: 22730547
Nevermind...it works even better than I thought. If someone has read a mail and wants to push it out from his/her category to (none), the script won't let it happen. However it will allow moving to a different category....which is good!
You did it BlueDevilFan!!!! THANKS MAN. Points to you
0
 
LVL 10

Author Closing Comment

by:Madjax
ID: 31506258
Great job!!! You're the man when it comes to script. Superthanks!
0
 
LVL 76

Expert Comment

by:David Lee
ID: 22730757
You're welcome.  Glad I could help out.
0
 
LVL 10

Author Comment

by:Madjax
ID: 22731131
Sorry to bother you once more.
The script worked for me, but it seemed like it had some very odd side effects.
When I coworker of mine clicked on an email in the mailbox (he didnt have the script installed in his Outlook) it put the email in my category and didnt create one for him.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Is your Office 365 signature not working the way you want it to? Are signature updates taking up too much of your time? Let's run through the most common problems that an IT administrator can encounter when dealing with Office 365 email signatures.
Find out what you should include to make the best professional email signature for your organization.
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

732 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