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

Status
Not open for further replies.

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?
 
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.
 
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!
 
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...
 
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.
 
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."
 
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!
 
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
 
This might be a better example - I will add an automated macro to it.

 
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.
 
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"
 
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
 
I have not had a chance to test your full macro - but this -
Set Appt = Item2
should be
Set Appt = Item
 
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
 
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
 
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
 
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
 
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.
 
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
J Changing Colors or Font Styles on the Outlook Bar Using Outlook 1
J Calendar events created on iPhone automatically changing default alert from 'None' to 'Time of Event' Using Outlook.com accounts in Outlook 0
F Auto changing email subject line in bulk Using Outlook 2
K Changing the Deleted Items location in Outlook 2019 Using Outlook 2
MattC Changing the font of an email with VBA Outlook VBA and Custom Forms 1
V Outlook 2021 Can anyone explain why my Outlook views keep changing?! Using Outlook 2
wayneame Changing the Form Used by Existing Task Items in a Folder Outlook VBA and Custom Forms 4
S Changing Message Class Outlook VBA and Custom Forms 4
C Pop Server Changing Verizon/Aol to Yahoo Using Outlook 6
P Outlook tasks keeps changing (updating) dates that I type Using Outlook 2
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

Similar threads

Back
Top