Solved

Help with script to tag messages on a group mailbox

Posted on 2008-10-15
21
377 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
  • 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Learn more about how the humble email signature can be used as more than just an electronic business card. When used correctly, a signature can easily be tailored for different purposes by different departments within an organization.
Granting full access permission allows users to access mailboxes present in their database. By giving full access permission one can open and read the content of any mailbox but cannot send emails from that mailbox.
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 …

758 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

22 Experts available now in Live!

Get 1:1 Help Now