Every email which need follow-up is flagged in order of importance with today, tomorrow, this week or next week. Unfortunately however, the tomorrow flag is not usable on friday because the reminder is then set to Saturday although I have excluded both Saturday and Sunday in my work week set in the calendar options.
While Outlook doesn't have a function built in to skip weekends or holidays, you can do it using a set of macros. I have a function that can skip weekends and dates of All Day Events on your calendar that are marked Busy, Tentative, Out of Office, or Working Elsewhere.
In this example, I have Monday and Tuesday marked as Busy, so the flag is moved to Wednesday.
Because this runs when you flag a message, you need to put it in ThisOutlookSession.
If your weekends (or days off) are not Saturday and Sunday, you can change the Select Case as needed.
Dim strAllDayOOF As String Public WithEvents OlItems As Outlook.Items Public Sub Initialize_handler() Set OlItems = Application.GetNamespace("MAPI"). _ GetDefaultFolder(olFolderInbox).Items End Sub Private Sub OlItems_ItemChange(ByVal Item As Object) If Item.IsMarkedAsTask = True Then If Item.TaskDueDate = Date + 1 Then startDate = NextWeekDaySeries(Date, 1) With Item .MarkAsTask olMarkNoDate .TaskStartDate = startDate .TaskDueDate = startDate .ReminderSet = True .ReminderTime = startDate .Save End With End If End If End Sub Private Function NextWeekDaySeries(dateFrom As Date, _ Optional daysAhead As Long = 1) As Date Dim currentDate As Date Dim startDate As Date GetHolidaysOOF ' convert neg to pos If daysAhead < 0 Then daysAhead = Abs(daysAhead) End If ' determine next date currentDate = dateFrom startDate = DateAdd("d", daysAhead, currentDate) Dim arrHolidays As Variant ' To be included, holidays need to be marked with a busy state, not Free arrHolidays = Split(strAllDayOOF, ",") ' Test the date for multiple days off, covers Monday holidays Dim sameDate As Date sameDate = Date Do Until sameDate = startDate + 1 ' Go through the array and look for a match, then do something For i = LBound(arrHolidays) To UBound(arrHolidays) Debug.Print arrHolidays(i) If InStr(startDate, arrHolidays(i)) Then startDate = DateAdd("d", 1, startDate) Select Case Weekday(startDate, vbUseSystemDayOfWeek) Case vbSunday startDate = DateAdd("d", 1, startDate) Case vbSaturday startDate = DateAdd("d", 2, startDate) End Select Next i sameDate = sameDate + 1 Debug.Print sameDate, startDate Loop NextWeekDaySeries = CDate(startDate) End Function Sub GetHolidaysOOF() ' Check for all day events on calendar ' marked busy/off/tentative ' skips recurring events ' To be included, holidays need to be marked with a busy state, not Free Dim CalItems As Outlook.Items Dim ResItems As Outlook.Items Dim sFilter As String Dim iNumRestricted As Integer Dim itm As Object ' Use the selected calendar folder Set CalFolder = Session.GetDefaultFolder(olFolderCalendar) Set CalItems = CalFolder.Items ' Sort all of the appointments based on the start time CalItems.Sort "[Start]" CalItems.IncludeRecurrences = False ' Set dates sFilter = "[Start] >= '" & Date & "' And [AllDayEvent] = 'True' And [BusyStatus] <> '0' AND [IsRecurring] = 'False'" Set ResItems = CalItems.Restrict(sFilter) iNumRestricted = 0 'Loop through the items in the collection. For Each itm In ResItems iNumRestricted = iNumRestricted + 1 Debug.Print Format(itm.Start, "m/d/yyyy") ' Create list of dates strAllDayOOF = strAllDayOOF & Format(itm.Start, "m/d/yyyy") & "," Next ' clean the string for the array strAllDayOOF = Left(strAllDayOOF, Len(strAllDayOOF) - 1) Set ResItems = Nothing Set CalItems = Nothing Set CalFolder = Nothing End Sub
How to use the Macro
First: You will need macro security set to low during testing.
To check your macro security in Outlook 2010 and newer, go to File, Options, Trust Center and open Trust Center Settings, and change the Macro Settings. In Outlook 2007 and older, it’s at Tools, Macro Security. If Outlook tells you it needs to be restarted, close and reopen Outlook. Note: after you test the macro and see that it works, you can either leave macro security set to low or sign the macro.
Open the VBA Editor by pressing Alt+F11 on your keyboard.
When you are told to put the macro code in ThisOutlookSession:
- Expand Project1 and double click on ThisOutlookSession.
- Copy then paste the macro into ThisOutlookSession. (Click within the code, Select All using Ctrl+A, Ctrl+C to copy, Ctrl+V to paste.)
More information as well as screenshots are at How to use the VBA Editor
A very helpful code, just what I needed.
Just wanted to post a couple of fixes that I needed in order to make it work:
- Peter Hawkes 's issue below is caused by the line
If you've got no future all-day "Busy" events (as in my case), the strAllDayOOF variable will be empty and VBA throws an error. So I've put it under an If:Weekday(startDate, vbUseSystemDayOfWeek)
doesn't work well, because it returns 6 for Saturday for me, while vbSaturday is 7. So the Select Case needs to be just:On restart I get an error;
Highlighted in Code Window: WithEvents OlItems As Outlook.Items
In a dialog box: Compile Error: Invalid attribute in Sub or Function
is it in thisoutlooksession?
Hello Diane,
I followed your instructions but I don't seem to get it to work.
I copied the macro to my ThisOutlookSession, restarted Outlook and enabled macro's but as from there it is unclear how to get it to work.
I understood that it would run whenever a message or task is flagged but I tried every flag type and it does not take all day events into account. I also tried to run the macro from the developer tab but same result, the macro can be run but nothing happens.
I did notice in the macro that it is based on a US region whil I'm in Europe, for example in the line Debug.Print Format(itm.Start, "m/d/yyyy") which I changed to Debug.Print Format(itm.Start, "d/m/yyyy").
Can this have an impact and would the above change in the code be correct ?
Can you provide me some more info on how to a apply this macro to the flags ?
Thank you in advance !
Hello Diane,
Apologies for my late reply but did not found time until now to test your macro (for which a big thank you very much !).
I followed your instructions but I don't seem to get it to work.
I copied the macro to my ThisOutlookSession, restarted Outlook and enabled macro's but as from there it is unclear how to get it to work.
I understood that it would run whenever a message or task is flagged but I tried every flag type and it does not take all day events into account. I also tried to run the macro from the developer tab but same result, the macro can be run but nothing happens.
I did notice in the macro that it is based on a US region whil I'm in Europe, for example in the line Debug.Print Format(itm.Start, "m/d/yyyy") which I changed to Debug.Print Format(itm.Start, "d/m/yyyy").
Can this have an impact and would the above change in the code be correct ?
Can you provide me some more info on how to a apply this macro to the flags ?
Thank you in advance !
it won't run from the developer ribbon as written - so it wont appear to do anything when you try. The date format could definitely cause problems - you should use the one that matches the date format in windows. it will skip all day events that are not marked busy. That could be removed from the holiday sub: [BusyStatus] <> '0' AND - but hen it will skip every stupid little 'holiday' on your calendar, not just 'bank holidays'. it also won't use recurring events, because you'd need to get the date of each occurrence and that is too complicated for this.