The January 2001 and February 2001 installments of Outlook VBA on Demand highlighted problems you might encounter when you work with dates in Microsoft Outlook. In this installment, I continue that topic by showing you how to create Outlook task items for an all-too-common scenario—tasks that you must finish either this week or next week. The key to this programming job is a little function that returns the next available date that corresponds to a particular day of the week.
The GetNextDay() function, which you see in Listing 1, contains two arguments: a date and an integer that represents the day of the week (starting with 1 for Sunday and ending with 7 for Saturday). Suppose today is Friday. If you use today's date and the integer 6 as your arguments, GetNextDay() returns the date for next Friday.
GetNextDay() uses two built-in Visual Basic (VB) functions that you'll probably find quite useful: Weekday() and DateAdd(). Weekday() takes any date and returns a number between 1 and 7 that represents the day of the week (with 1 representing Sunday).
DateAdd(), which lets you increase or decrease any date/time value by a specific number of units, uses the syntax
where unit is the interval you want to increase or decrease the date by. In Listing 1, the unit is "d" for days. You'll find the strings used for other intervals in the VBA Help file. (The procedures in Listing 4 use "ww" for weeks and "h" for hours.) The number argument is the number of intervals by which you want to increase or decrease the date. (You use a negative number to decrease.) Finally, date is the date you want to change.
The code you see in Listing 2 creates a new task for the current week, sets some basic properties, then displays the task so that the user can fill in additional information. The CreateThisWeekTask() procedure uses the GetNextDate() function to set the due date of the task to Friday of the current week:
Date() is an intrinsic VB function that returns the current day's date.
The CreateNextWeekTask() subroutine, which you see in Listing 3 has the same functionality as CreateThisWeekTask(). However, it uses GetNextDay() twice—first to set the task's start date to the following Monday, then to set the end date to the following Friday.
The solution in Listing 4 takes a different approach. The CreateTaskForXDay() subroutine prompts the user for the number of days necessary to complete the task. The subroutine depends on the GetNextWorkDay() function, which contains some useful user options: the number of days in the user's work week and whether the user wants the procedure to adjust the due date if it falls on a weekend. Notice that the subroutine also introduces a couple of operators—the \ division operator always rounds down to the nearest integer, and the Mod operator obtains the remainder from a division expression rather than the dividend.
Try It—You'll Like It
You probably find yourself creating many tasks that you must finish this week, next week, or in a specific number of days. If so, try out one (or more) of this column's subroutines. Just add the tool to your Outlook toolbar so that you can create a new task—with the dates already filled in—at the click of a button.