Solved

How to assign categories to calendar items in Outlook based on a text string containing certain text

Posted on 2011-10-01
10
656 Views
Last Modified: 2012-06-27
I want to use a vba macro to do what is discussed in this thread   http://www.experts-exchange.com/Software/Office_Productivity/Groupware/Outlook/Q_25072154.html?sfQueryTermInfo=1+10+30+assign+calendar+categori+item+outlook

The calendar items that I want to change (actually add a category to) are already assigned to a category that always contain certain text. Aditionally, part of the subject line for each of those items also always contains the same text.

For example the calendar items always are named "NRVV-xxxxxxxx", where the "x" can be variable text, but the category name always starts with "NRVV".  Likewise, the Subject line also always contains "Bkg#" somewhere in the text string.

How can I automatically search the calendar items and add them to another category if they contain one of the text blocks.

Here is the code I currently have in ThisOutlookSession, but it doesn't change (add) the category to the calendar items like I want it to.  I'd like them to all be part of the "NRVV" category that is in the Master Cetegory list.
Dim WithEvents olkCalendar As Outlook.Items

Private Sub Application_Quit()
    Set olkCalendar = Nothing
End Sub

Private Sub Application_Startup()
    Set olkCalendar = Session.GetDefaultFolder(olFolderCalendar).Items
End Sub

Private Sub olkCalendar_ItemAdd(ByVal Item As Object)
    Dim olkAppt As Outlook.AppointmentItem
    Set olkAppt = Item
    'Here are a couple of examples of category rules you can construct to assign a category'
    
    'If the phrase Project X is in the item subject, then assign it to a category called Projects'
    If InStr(1, olkAppt.Subject, "NRVV") Then AddCategory olkAppt, "NRVV"
    
    'If you are not the meeting organizer, then assign it to a category of Meetings'
    'If olkAppt.Organizer <> Session.CurrentUser Then AddCategory olkAppt, "Meetings"
    
    olkAppt.Save
End Sub

Private Sub AddCategory(olkItem As Outlook.AppointmentItem, strCategory As String)
    If olkItem.Categories = "" Then
        olkItem.Categories = strCategory
    Else
        olkItem.Categories = olkItem.Categories & "," & strCategory
    End If
End Sub

Open in new window

0
Comment
Question by:mycomac
  • 5
  • 5
10 Comments
 
LVL 28

Expert Comment

by:omgang
ID: 36903962
mycomac, your code is working for me as expected, e.g. if I create a new Outlook appointment and include 'NRVV" in the subject line the code adds the category NRVV to the appointment item.

Is this not working for you?  Are you wanting to do this for existing appointment items?  Your code will only fire when new appointment items are created not when editing/updating existing appointment items.

OM Gang
0
 

Author Comment

by:mycomac
ID: 37162625
I doesn't seem to work for me.  The calendar items are being generated by another program and added to the Outlook calendar.  Could that be part of the issue?
0
 

Author Comment

by:mycomac
ID: 37697174
The outlook calendar item is being imported (or generated) by another program and then added to my Outlook Calendar.  The above code does not assign the category to those items.
0
 
LVL 28

Expert Comment

by:omgang
ID: 37697421
Your code is within Outlook and fires on an event that takes place within Outlook.  If the appointment items are being added by an outside program then the ItemAdd event never fires within Outlook itself.  You'll need to modify the process to work on exsiting Calendar items and then decide how/when to run it.

I'd look at creating a sub for function to enumerate all Calendar items and check to see if the string NRVV is in the Subject.  If so, call the AddCategory sub to update the Calendar item with the specified Category.  You'll need to decide when you want to run the routine - when Outlook starts?  or you could call it/run it manually with a shortcut key sequence.

OM Gang
0
 

Author Comment

by:mycomac
ID: 37697595
Thanks. But I don't know how, what the code should be to do that.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 28

Accepted Solution

by:
omgang earned 500 total points
ID: 37697909
This should do the trick.

You can create a custom toolbar (Tools --> Customize) and assign the UpdateCalendarCategory sub/macro to it.  All you have to do is click the toolbar button to update all Calendar events.

OM Gang


Sub UpdateCalendarCategory()
On Error GoTo Err_UpdateCalendarCategory

    Dim olkCal As Outlook.Folder
    Dim olkApptItem As Outlook.AppointmentItem
    Dim strSubject As String, strSubjText As String, strCategory As String
    
    strSubjText = "NRVV"
    strCategory = "NRVV"
    
    Set olkCal = Session.GetDefaultFolder(olFolderCalendar)
    
    For Each olkApptItem In olkCal.Items
        strSubject = olkApptItem.Subject
        
        If InStr(1, strSubject, strSubjText) Then
            AddCategory olkApptItem, strCategory
        End If

    Next

Exit_UpdateCalendarCategory:
    Set olkApptItem = Nothing
    Set olkCal = Nothing
    Exit Sub

Err_UpdateCalendarCategory:
    MsgBox Err.Number & " (" & Err.Description & ") in procedure UpdateCalendarCategory of VBA Document ThisOutlookSession"
    Resume Exit_UpdateCalendarCategory

End Sub

Private Sub AddCategory(olkItem As Outlook.AppointmentItem, strCategory As String)
On Error GoTo Err_AddCategory

    If olkItem.Categories = "" Then
        olkItem.Categories = strCategory
    Else
            'check to see if category is already assigned
        If InStr(1, olkItem.Categories, strCategory) = 0 Then
            olkItem.Categories = olkItem.Categories & "," & strCategory
        End If
    End If
    
    olkItem.Save

Exit_AddCategory:
    Exit Sub

Err_AddCategory:
    MsgBox Err.Number & " (" & Err.Description & ") in procedure AddCategory of VBA Document ThisOutlookSession"
    Resume Exit_AddCategory

End Sub

Open in new window

0
 

Author Comment

by:mycomac
ID: 37710229
This code throws an error when it runs - highlighting this sub

Private Sub AddCategory(olkItem As Outlook.AppointmentItem, strCategory As String


The error message is "Compile error:  Ambiguous name detected: AddCategory"
0
 
LVL 28

Expert Comment

by:omgang
ID: 37710291
Did you paste my code into ThisOutlookSesson or a seperate code module, e.g. Module1?

Do you have two procedures named AddCategory?  Copy and paste all the code from your ThisOutlookSession module.
OM Gang
0
 

Author Comment

by:mycomac
ID: 37710326
I did have it twice;

here is the first instance.  Can I just delete it?

Private Sub AddCategory(olkItem As Outlook.AppointmentItem, strCategory As String)
    If olkItem.Categories = "" Then
        olkItem.Categories = strCategory
    Else
        olkItem.Categories = olkItem.Categories & "," & strCategory
    End If
End Sub
0
 
LVL 28

Expert Comment

by:omgang
ID: 37710354
Yes.
OM Gang
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

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.
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 …
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

746 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

9 Experts available now in Live!

Get 1:1 Help Now