Changing colors of today's appointments, but not recurring ones

schwarznavy

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
Using conditional formatting, I can change the colors of today's appointments by using the criteria "Starts" and setting to today. For some reason, that also affects any and all recurring appointments. Why?

I can add an additional criteria of "reccuring = no" but then that removes any recurring appointments for today.

Is there a way to apply conditional formating to only and all of today's appointments?
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
Recurring appointments are weird - the recurrence pattern makes it seem like it should be on every day within the recurrence period, so start filters fail. If you don't have a ton of recurring events, don't worry about it. If you have a lot, add recurring no - they'll still be on the calendar, mixed with the other highlighted appointments.

Also, Conditional formatting won't work on appointments that have categories.
 

schwarznavy

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
Thanks Diane. I knew about "recurring no" but I do have lots of recurring appointments. The mixture of formatted and non-formatted colored appointments doesn't sit well with me.

A tangent -- still upset that I can't get conditional formatting on the To-Do Bar Calendar!
 

schwarznavy

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
Diane - as an alternative - I'm thinking to automatically categorize appointments based on "Show As" status. I found your script on categorizing appointments based on subject here: How to Auto Assign a Color Category to New Appointments via Outlook VBA

First, the script throws me a compile error when I startup Outlook. The "WithEvents olItems As Outlook.Items" is highlighted and it says "Invalid attribute in Sub or Function."

Unfortunately, I'm starting to learn javascript but haven't gotten around to VBA. Once I can get past that error, I figure I will play around with the .busystatus property (AppointmentItem.BusyStatus property (Outlook)) instead of subject...
 

schwarznavy

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
Actually, figured it out.

I already had macros. At the top there were already several "WithEvents" lines. So I moved yours to the top. I changed Public to Private. Also, I already had a section with Application_Startup(). I moved the olItems line into that. Now it works based on subject.
 

schwarznavy

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
Follow-on -- using Select Case Item.BusyStatus works, following the constants returned (BusyStatus Property (AppointmentItem Object)).

Now I'd like to make it work whenever I change an appointment's busystatus. For example, I saved it earlier set to "Busy" but then later I change it to "Tentative."
 

schwarznavy

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
Diane - as an alternative - I'm thinking to automatically categorize appointments based on "Show As" status. I found your script on categorizing appointments based on subject here: How to Auto Assign a Color Category to New Appointments via Outlook VBA

First, the script throws me a compile error when I startup Outlook. The "WithEvents olItems As Outlook.Items" is highlighted and it says "Invalid attribute in Sub or Function."

Unfortunately, I'm starting to learn javascript but haven't gotten around to VBA. Once I can get past that error, I figure I will play around with the .busystatus property (AppointmentItem.BusyStatus property (Outlook)) instead of subject...
Oops. That wasn't your script. I had too many window up!
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
A tangent -- still upset that I can't get conditional formatting on the To-Do Bar Calendar!
Color categories, yes, formatting - no. But Tasks can be filtered and formatted. :)



First, the script throws me a compile error when I startup Outlook. The "WithEvents olItems As Outlook.Items" is highlighted and it says "Invalid attribute in Sub or Function."
Is the macro in ThisOutlookSession or in a module? WithEvents goes at the top of thisoutlook session.



I already had macros. At the top there were already several "WithEvents" lines. So I moved yours to the top. I changed Public to Private. Also, I already had a section with Application_Startup(). I moved the olItems line into that. Now it works based on subject.
Yeah. :) That is exactly what you needed to do. The public and private stuff doesn't usually make a difference - unless you have two macros with the same name in different modules. Then private can only be called by other macros in that module.



Now I'd like to make it work whenever I change an appointment's busystatus. For example, I saved it earlier set to "Busy" but then later I change it to "Tentative."

You can watch the folder and when a new item is added, set a category if the status is busy.

there is an example here to watch for changes and do something -

It's doing a look up in another calendar - so I guess it not a good example. :)


Not tested - and you need to set the curCal object to the calendar you are watching.... but something like this should work.

Code:
Private Sub curCal_ItemChange(ByVal Item As Object)

if Item.BusyStatus = olBusy then 
item.categories = "Whatever"
item.save ' may not be required
end if
 
End Sub
 

schwarznavy

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
Private Sub Items_ItemChange(ByVal Item As Object) Dim Appt As Outlook.AppointmentItem If Item.Class = olAppointment Then Select Case Item.BusyStatus Case 2 Item.Categories = "Busy" Item.Save Case 3 Item.Categories = "Out of Office" Item.Save Case 1 Item.Categories = "Tentative" Item.Save End Select End If End Sub

Next - figure out how to not blow away any other categories that may be set on the items.
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
Would help if I could read. :) If you are just adding, use

item.categories = "new category" & item.categories

That should put it first - switch it to put the new one at the end:
item.categories = item.categories & "new category"
 

schwarznavy

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
I can't get it to work on changes, but it does work on new appointments. I basically duplicated much of the code for _ItemAdd and _ItemChange as follows. Any idea what I'm doing wrong? When I add an appointment - even if I've added a different category - the appropriate additional category will be added bepending on the BusyStatus. When I change an appointment's BusyStatus, there is no change of category.

Code:
Private WithEvents olItems As Outlook.Items
Private WithEvents olItem2 As Outlook.Items

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

Private Sub Initialize_handler()
    Dim Ns As Outlook.NameSpace
    Set Ns = Application.GetNamespace("MAPI")
    Set olItems2 = Ns.GetDefaultFolder(olFolderCalendar).Items
End Sub

Private Sub olItems_ItemAdd(ByVal Item As Object)
    Dim NewAppt As Outlook.AppointmentItem

    If Item.Class = olAppointment Then
        Set NewAppt = Item
        
        Select Case Item.BusyStatus
            Case 2
                Item.Categories = Item.Categories & "," & "Busy"
                Item.Save
            Case 3
                Item.Categories = Item.Categories & "," & "Out of Office"
                Item.Save
            Case 1
                Item.Categories = Item.Categories & "," & "Tentative"
                Item.Save
        End Select
        
    End If
End Sub

Private Sub olItems2_ItemChange(ByVal Item As Object)
    Dim Appt As Outlook.AppointmentItem

    If Item.Class = olAppointment Then
        Set Appt = Item2
        Select Case Item.BusyStatus
            Case 2
                Item.Categories = Item.Categories & "," & "Busy"
                Item.Save
            Case 3
                Item.Categories = Item.Categories & "," & "Out of Office"
                Item.Save
            Case 1
                Item.Categories = Item.Categories & "," & "Tentative"
                Item.Save
        End Select
        
    End If
End Sub
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
I have not had a chance to test your full macro - but this -
Set Appt = Item2
should be
Set Appt = Item
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
This is working to add the category once on changes - its using the olitems in app startup, not Initialize_handler - without the If lines, it added a ton of categories on change. But... you really need to remove the busy state category on changes, not add to it. But its a start.

Code:
Private Sub olItems_ItemChange(ByVal Item As Object)

    If Item.Class = olAppointment Then
    
        Select Case Item.BusyStatus
            Case 2
            If InStr(Item.Categories, "Busy") = 0 Then
                Item.Categories = Item.Categories & "," & "Busy"
                Item.Save
            End If
            Case 3
            If InStr(Item.Categories, "Out of Office") = 0 Then
                Item.Categories = Item.Categories & "," & "Out of Office"
                Item.Save
            End If

            Case 1
            If InStr(Item.Categories, "Tentative") = 0 Then
                Item.Categories = Item.Categories & "," & "Tentative"
                Item.Save
            End If
          
        End Select

    End If
End Sub
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
This is removing the old categories, if you changed the busy type.

Code:
Private Sub olItems_ItemChange(ByVal Item As Object)

If Item.Class = olAppointment Then


Dim strCat As String
Dim arrCat As Variant


        Select Case Item.BusyStatus
            Case 2
            If InStr(Item.Categories, "Busy") = 0 Then
                Item.Categories = Item.Categories & "," & "Busy"
                Item.Save
            End If

             arrCat = Array("Out of Office", "Tentative")
            
            Case 3
            If InStr(Item.Categories, "Out of Office") = 0 Then
                Item.Categories = Item.Categories & "," & "Out of Office"
                Item.Save
            End If
            arrCat = Array("Busy", "Tentative")
            
            Case 1
            If InStr(Item.Categories, "Tentative") = 0 Then
                Item.Categories = Item.Categories & "," & "Tentative"
                Item.Save
            End If
            arrCat = Array("Out of Office", "Busy")

            Case Else
' remove the categories if not one of these
            arrCat = Array("Out of Office", "Busy", "Tentative")

        End Select
        
   arr = Split(Item.Categories, ",")
  
For j = LBound(arrCat) To UBound(arrCat)
   Debug.Print arrCat(j)
    If UBound(arr) >= 0 Then
  ' Check for Category
    For i = 0 To UBound(arr)
    Debug.Print arr(i)
      If Trim(arr(i)) = Trim(arrCat(j)) Then
        ' remove it
            arr(i) = ""
            Item.Categories = Join(arr, ",")

        End If
        Next
        End If
Next j
    Item.Save

    End If
        

End Sub
 

schwarznavy

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
Thank you Diane. I won't be able to test until I get back to work on Tuesday. But, quick question...with your suggestion above do I need to change any of the starting items, such as:
Code:
Private WithEvents olItems As Outlook.Items
Private WithEvents olItem2 As Outlook.Items

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

Private Sub Initialize_handler()
    Dim Ns As Outlook.NameSpace
    Set Ns = Application.GetNamespace("MAPI")
    Set olItems2 = Ns.GetDefaultFolder(olFolderCalendar).Items
End Sub
 

schwarznavy

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
Thank you Diane. I won't be able to test until I get back to work on Tuesday. But, quick question...with your suggestion above do I need to change any of the starting items, such as:
Code:
Private WithEvents olItems As Outlook.Items
Private WithEvents olItem2 As Outlook.Items

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

Private Sub Initialize_handler()
    Dim Ns As Outlook.NameSpace
    Set Ns = Application.GetNamespace("MAPI")
    Set olItems2 = Ns.GetDefaultFolder(olFolderCalendar).Items
End Sub
Actually, I loaded Outlook at home and tested. It works great with just this
Code:
Private WithEvents olItems As Outlook.Items

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

schwarznavy

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
I also switched the order of these:
Code:
Item.Categories = Item.Categories & "," & "Busy"
to these:
Code:
Item.Categories = "Busy" & "," & Item.Categories
Since that puts my Busy/Tentative/O-o-O colors at the forefront, since the whole purpose of this was to give me better visual cues as to the BusyStatus of my appointments.
 
Similar threads
Thread starter Title Forum Replies Date
J Changing Colors or Font Styles on the Outlook Bar Using Outlook 1
e_a_g_l_e_p_i Changing where data .pst is saved to Using Outlook 3
P Changing the font that the task view shows Using Outlook 5
T Changing Sent Items location in Outlook 2019 Using Outlook 0
E Outlook view grouping keeps changing Using Outlook 3
B BCC issues after changing root folder path for gmail Using Outlook 1
M Changing the preferred order for "Put this entry in" list for adding new contacts to the Address Book Using Outlook 1
J Outlook 2010 Changing events in Outlook calendar via opening file, importing CSV Using Outlook 0
A .restrict results changing after moving to Exchange online Outlook VBA and Custom Forms 0
T Outlook Contacts ... Changing Font Size, Style, Bold, etc. Using Outlook 2
N Rule for "on behalf of" - with changing names Using Outlook 2
O Save attachments using hotkey without changing attributes Outlook VBA and Custom Forms 1
M Outlook 2016: Changing default font for Notes and Reading Pane Using Outlook 4
V Changing default date for task follow-up buttons Using Outlook 2
Gary Hile Outlook 2016 changing editor options Using Outlook 6
J Outlook Rules - Changing auto-submit address in multiple rules, according to rule name Outlook VBA and Custom Forms 0
S Problems syncing emails with webmail after changing to Outlook 2016 Using Outlook 1
T Changing default Mail Account in Outlook 2016 - POP3 Using Outlook 1
S Changing notification sound for new incoming messages in Outlook 365/2016 Using Outlook 1
Stephen Weinberg Changing the mailing address checkbox Using Outlook 0
D Outlook 2013 changing iCloud reminder time? Using Outlook 0
C Changing the name of Outlook Messages saved to a folder Using Outlook 1
A Outlook.com changing appointments Using Outlook 8
B Changing CC list to .add Outlook VBA and Custom Forms 2
Diane Poremsky Changing the Message Size in Exchange Server Using Outlook 0
R changing FW: on forward Outlook VBA and Custom Forms 3
B changing Win7 default backup schedule for Previous Versions Using Outlook 0
Diane Poremsky Changing the default *.pst and *.ost sizes Using Outlook 0
P Message Class keeps changing back to IPM.Contact Outlook VBA and Custom Forms 2
C Macro to send email after changing from address and adding signature Outlook VBA and Custom Forms 1
Diane Poremsky Changing Outlook.com color schemes Using Outlook 0
R Outlook calendar appointments Free/Busy time is changing from "Busy" to "Free" Using Outlook 2
W Changing looks of emails in Outlook 2003 Using Outlook 0
L Office 365 Outlook changing default contact folder Using Outlook 0
Diane Poremsky Changing the From Domain in Office 365 Using Outlook 0
R The changing way to access information in Office 365 Using Outlook 0
N Creating or changing the main new mail message template in Outlook 2010 Using Outlook 2
T issue changing [Type] in dbo.ContactMainTable/ contacts seem to be deleted BCM (Business Contact Manager) 5
William Yeack Outlook/Exchange - Changing display of “From” user Using Outlook 3
divan VbaProject: Changing email format Using Outlook 15
C Changing Domain -- Assigned To User field Migration? BCM (Business Contact Manager) 1
M CHANGING ACCOUNT NAME Using Outlook 0
J Changing margins in received email display Using Outlook 3
B Automatic picture download and changing email addresses Using Outlook 3
L changing view settings based on message body Using Outlook 1
M How to automatically refresh an appointment windows after changing the MessageClass property ? Outlook VBA and Custom Forms 4
J Changing Contact Notes Font Outlook VBA and Custom Forms 9
cimbian Changing default contact view Using Outlook 2
S Outlook custom checkbox not changing value Using Outlook 0
E Changing layout of forms in BCM Using Outlook 1

Similar threads

Top