And Tasks to Outlook

If you have an Access table of employee, contact, or customer information, you may need to create Outlook appointments or tasks based on information in the table records. The tblEmployees table in the sample database has two employee review date fields: LastReviewDate and NextReviewDate. Figure 4.1 shows the frmEmployees form, which is bound to this table.

The next employee review can be scheduled by entering a date in the Next Review Date field and then clicking the Schedule Appointment button. Code on the BeforeUpdate event of txtNextReviewDate (listed next) checks that the date entered (or selected using the Calendar pop-up) is a Tuesday or Thursday (the assumption is that employee reviews are only done on those days):

Private Sub txtNextReviewDate_BeforeUpdate(Cancel As Integer)

On Error GoTo ErrorHandler

Dim strWeekday As String Dim intWeekday As Integer

An Employees form with review date fields.

An Employees form with review date fields.

Check that a date has been entered (or selected):

If IsDate(Me![NextReviewDate]) = False Then

GoTo ErrorHandlerExit Else dteNextReviewDate = CDate(Me![NextReviewDate]) intWeekday = Weekday(dteNextReviewDate) Select Case intWeekday

Check whether selected date is a weekend day, and put up error message and exit if so:

Case vbSunday, vbSaturday strTitle = "Wrong day of week" strPrompt = _

"Reviews can't be scheduled on a weekend" MsgBox strPrompt, vbOKOnly + vbExclamation, _

strTitle Cancel = True GoTo ErrorHandlerExit

Case vbMonday, vbWednesday, vbFriday Check whether selected date is the wrong day of the week, and put up error message and exit if so: strTitle = "Wrong day of week"

strPrompt = "Reviews can only be scheduled on " _

& "a Tuesday or Thursday" MsgBox strPrompt, vbOKOnly + vbExclamation, _

strTitle Cancel = True GoTo ErrorHandlerExit

Case vbTuesday, vbThursday

Date is a Tuesday or Thursday; put up message and continue:

strTitle = "Right day of week" strPrompt = "Review date OK"

MsgBox strPrompt, vbOKOnly + vblnformation, _ strTitle

End Select

End If

ErrorHandlerExit: Exit Sub

ErrorHandler:

& "; Description: " & Err.Description Resume ErrorHandlerExit

End Sub

To work with Outlook items in code, you need to set a reference to the Outlook object library (select Tools O References in the Visual Basic window, as shown in Figure 4.2). To avoid creating multiple instances of Outlook, I like to use an error handler that will open a new instance of Outlook using CreateObject if the GetObject function fails because Outlook is not running.

FIGURE 4.2

Setting a reference to the Outlook object library.

References - Access to Word

Available References:

□ Microsoft Office Web Discussions Client Type Library *

□ Microsoft Office.NET Private Object Library

□ Microsoft OLAP Designer Server Driver 8,0

□ Microsoft OLE DB provider for OLAP Services conned

□ Microsoft OLE DB Service Component 1,0 Type Librar

□ Microsoft OLE DB Simple Provider 1,5 Library

□ Microsoft PenlnputPanel 1.7

D Microsoft PowerPoint 12.0 Object Library

□ Microsoft Publisher 12,0 Object Library

□ Microsoft Remote Data Services 6.0 Library

□ Microsoft Remote Data Services Server 6.0 Library

□ Microsoft Script Control 1.0

Microsoft Outlook 12.0 Object Library

Location: J: program Files Per osoft Office\Officel2\MSOLHL.OLB Language: Standard

Once a correct Tuesday or Thursday date has been selected or entered, clicking the Schedule Appointment button creates three Outlook items: an appointment for the employee, an appointment for the supervisor (the person the employee reports to), and a task for the supervisor. The button's Click event procedure is listed as follows:

Private Sub cmdScheduleAppt_Click()

On Error GoTo ErrorHandler

Dim appOutlook As Outlook.Application

Dim strEmployeeName As String

Dim strSupervisorName As String

Dim appt As Outlook.AppointmentItem

Dim fldTopCalendar As Outlook.Folder

Dim fldContactCalendar As Outlook.Folder

Dim fldSupervisorCalendar As Outlook.Folder

Dim fldTasks As Outlook.Folder

Dim tsk As Outlook.TaskItem

Dim nms As Outlook.NameSpace

Set appOutlook = GetObject(, "Outlook.Application") Set nms = appOutlook.GetNamespace("MAPI")

Set variables for information to be exported to Outlook:

strTitle = "Missing Information"

If IsDate(Me![txtNextReviewDate].Value) = True Then dteNextReviewDate = CDate(Me![txtNextReviewDate].Value) Else strPrompt = _

"No next review date; can't create appointment" MsgBox strPrompt, vbOKOnly + vbExclamation, strTitle GoTo ErrorHandlerExit End If strEmployeeName = Me![FirstNameFirst] strSupervisorName = Nz(Me![cboReportsTo].Column(1))

If strSupervisorName = "" Then strPrompt = "No supervisor selected; can't schedule review" strTitle = "No supervisor"

MsgBox strPrompt, vbOKOnly + vbExclamation, strTitle GoTo ErrorHandlerExit End If

Set reference to (or create) contact's calendar:

On Error Resume Next

Set fldTopCalendar = _

appOutlook.Session.GetDefaultFolder(olFolderCalendar) Set fldContactCalendar = _

fldTopCalendar.Folders(strEmployeeName) If fldContactCalendar Is Nothing Then Set fldContactCalendar = _

fldTopCalendar.Folders.Add(strEmployeeName)

End If

Set reference to (or create) supervisor's calendar:

Set fldSupervisorCalendar = _

fldTopCalendar.Folders(strSupervisorName) If fldSupervisorCalendar Is Nothing Then Set fldSupervisorCalendar = _

fldTopCalendar.Folders.Add(strSupervisorName)

End If

On Error GoTo ErrorHandler

Create appointment in contact's calendar:

Set appt = fldContactCalendar.Items.Add With appt

.Start = CStr(dteNextReviewDate) & " 10:00 AM"

.AllDayEvent = False

.Location = "Small Conference Room"

.ReminderMinutesBeforeStart = 3 0

.ReminderSet = True

.ReminderPlaySound = True

.Subject = "Review with " & strSupervisorName .Close (olSave) End With

Create appointment in supervisors calendar:

Set appt = fldSupervisorCalendar.Items.Add With appt

.Start = CStr(dteNextReviewDate) & " 10:00 AM" .AllDayEvent = False .Location = "Small Conference Room" .ReminderMinutesBeforeStart = 3 0 .ReminderSet = True .ReminderPlaySound = True .Subject = strEmployeeName & " review" .Close olSave End With

Create task for supervisor (day before the appointment):

appOutlook.Session.GetDefaultFolder(olFolderTasks) Set tsk = fldTasks.Items.Add With tsk

.StartDate = DateAdd("d", -1, dteNextReviewDate) .DueDate = DateAdd("d", -1, dteNextReviewDate) .ReminderSet = True .ReminderPlaySound = True

.Subject = "Prepare materials for " & strEmployeeName _

& " review" .Close (olSave) End With strTitle = "Done" strPrompt = dteNextReviewDate _

& " appointments scheduled for " _ & strEmployeeName & " (employee) and " _ & strSupervisorName _

& " (supervisor) and a task scheduled for " _ & strSupervisorName MsgBox strPrompt, vbOKOnly + vblnformation, strTitle

End Sub

The code first attempts to set references to the supervisors and employee's folders under the default Calendar folder. If there is no folder for the employee (or supervisor), it then creates a new folder for the employee or supervisor, using the Add method of the Calendar folders Folders collection. Next, the Items collection of the supervisors folder is used to create a new item of the default item type in that folder, and similarly for the employees folder. You can also create a new item using the Createltem method of the Outlook Application object, but that creates the item in the default folder; if you want to create an item in a custom folder, you need to use the Add method instead.

You can't use the Add method directly with an Outlook folder; this method works with collections, such as the Items collection or the Folders collection.

Finally, you will get a "Done" message (Figure 4.3) reporting on the appointments and task that have been scheduled.

Figure 4.4 shows several employee and manager folders under the default Calendar folder, and a supervisor appointment in the daily calendar.

i ^ you don't see the employee and manager folders, you are probably in another view;

— ■ switch to Folder view to see the calendar folders.

FIGURE 4.3

A success message with details about the Outlook items created.

FIGURE 4.3

FIGURE 4.4

Employee and supervisor folders and an appointment created from code.

FIGURE 4.4

Employee and supervisor folders and an appointment created from code.

You can double-click the appointment to open it in a separate window.
0 0

Post a comment