Synchronize Your Outlook Calendar with DB2 for i ERP Data
February 2, 2011 Hey, Mike:
We would like to merge the Resource Assigned Calendar from our JDE Service Management software with the Outlook Calendar so that we only need to look in one place. We also want to receive an email from Outlook and create a case.
How can we approach this?
As always, there is more than one way to skin a cat, and this calendar synchronization task is no exception.
The approach I will be taking to solve this problem is to allow an Outlook client to monitor the ERP system’s calendar table using Visual Basic for Applications (VBA) coding. The underlying concepts necessary for using Outlook and VBA code can be found in my article, Automate E-Mail Operations with Outlook and VBA. If you’re unfamiliar with Outlook and VBA coding, please review this tip first as it will get you started with references to the Outlook object model, discuss how to make your custom code run when Outlook starts, etc.
Keep in mind that Outlook and VBA aren’t the only possible approach to a solution. If, for example, your company uses Microsoft Exchange Server 2007 (Service Pack 1) or higher, there may be a more centralized way of accomplishing this task using Exchange’s Web service-based API. Also, note that Outlook can behave differently when connected to exchange versus a POP3 server, so some experimentation may be required to understand exactly how Outlook behaves in a given environment.
I haven’t been on a JD Edwards system in a while so I don’t have access to the “Resource Assigned Calendar” table(s). Instead, in this tip I’ll just be referring to a generic ERP calendar table. It is assumed that this table(s) has assignments by date, expected start time, resources (a.k.a., users), etc. Due to length and complexity, I will not be including ADO code to talk with DB2 for i. Instead, I will be showing unadorned examples of how to use the essential elements of the Outlook object model for this subject. Assuming you have a DB2 ADO recordset from your ERP system you can modify the code accordingly. If you want an example of ADO coding for DB2 for i within Outlook, please see my tip. Further, there are a multitude of samples of VBA, ADO, and DB2 for i code on the Web.
Before starting, there are a few thorny questions to tackle for this task:
How you answer these questions will determine how you can best approach the solution. Since there is no “one size fits all” solution, here is some food for thought. Outlook has three types of items that can assist with this task: a standard email, an appointment, and a meeting.
Further, the custom Outlook synchronization VBA code can be run under the authority of a single administrative or power user. Under this implementation, the code can be run on a single machine that has Outlook installed.
Now let’s move on to coding examples. Take a quick look at these simple VBA code examples that illustrate the creation of an appointment item or a meeting item.
Creating an Outlook Appointment
The code below illustrates how to create an appointment using constant values. Normally, you’d probably use values retrieved directly from DB2 for i using the ActiveX Data Objects (ADO) library and one of IBM‘s OLE DB providers (IBMDA400/IBMDASQL) or the ODBC driver.
' Appointment Example ' Dim ApptItem As AppointmentItem Set ApptItem = Outlook.Application.CreateItem(OlAppointmentItem) ApptItem.Subject = "My Test Appointment" ApptItem.Body = "Boring details about my appointment" ApptItem.Start = #10/13/2010 1:30:00 PM# ApptItem.End = #10/13/2010 2:00:00 PM# ApptItem.AllDayEvent = False ApptItem.BusyStatus = olOutOfOffice ApptItem.Save
There is nothing complex about this code. Creating an appointment is similar to creating an email because it requires a subject and body. It also requires a start time and an end time. Further, other properties available in the Outlook Calendar’s user interface such as the “AllDayEvent” checkbox, the busy status (Out of office, tentative, busy, free) are also available to set using VBA. It is even possible to add attachments to an appointment item. Finally, the Save method is called to permanently save the appointment.
Creating an Outlook Meeting
Next, take a look at this meeting creation example. The code is very similar to creating an appointment with the exception of a few more properties:
' Meeting Example ' Dim MeetingItem As AppointmentItem Set MeetingItem = Outlook.Application.CreateItem(OlAppointmentItem) With MeetingItem .Subject = "My Test Meeting" .Body = "Boring details about my meeting" .Start = #10/13/2010 1:30:00 PM# .End = #10/13/2010 2:00:00 PM# .AllDayEvent = False .MeetingStatus = olMeeting 'Can be set to olMeetingCanceled, etc. .ReminderMinutesBeforeStart = 5 .Location = "Small Conference Room" .RequiredAttendees = John.Doe@MyCompany.com;Jane.Doe@MyCompany.com .BusyStatus = olBusy .Importance = olImportanceHigh .Send End With
As you can see, the meeting request is actually a specialized case of the Appointment item. Outlook will consider an “appointment item” as a meeting request when the “MeetingStatus” property is set appropriately and recipients are specified.
The “RequiredAttendees” property is a semi-colon delimited list of email addresses, so there should be some sort of cross-reference between your ERP user IDs and their email addresses. Other properties to note from this code snippet are Location (text of where meeting will be), ReminderMinutesBeforeStart (allows Outlook to show a reminder specified number of minutes before meeting), and Importance (low, medium, or high.) Other properties not shown in the code that are useful to know about are:
Finally, using the Send method (instead of Save) will email all the required and optional attendees a meeting invitation. When accepted, the meeting will show up in the user’s Outlook calendar.
Here is what a meeting reminder may look like to the Outlook user (shown for Outlook 2007):
As already noted, any synchronization process needs to know if something changed and, if so, what to do about it. In this case, for simplicity it is assumed that the synchronization process is one way (from ERP to Outlook) and that each distinct request for a resource’s time from the ERP data has a unique identifier of some kind. Further, if Outlook meetings are the chosen vehicle for alerting users, this limitation implies that a user is not allowed to decline an invitation. Otherwise, you’d have to go to the pains of changing the ERP system’s meeting request as a result of a resource being unavailable.
If you have a cross-reference table that contains unique identifiers between the ERP Calendar table and the Outlook calendar, logical steps for the sync process might look something like this:
A. Find all future resource requests in the ERP calendar.
How to record the unique IDs from each system can be as easy as making a new DB2 database table to record both, using an existing user field on the ERP calendar table (if one exists), or recording the ERP’s unique identifier right in the Appointment or Meeting item using a custom property. The code below shows how a property called ERPUNIQUEID can be added to a meeting item (object MeetingItem):
' Create a custom property called ERPUNIQUEID to keep an ' association between this Outlook meeting and the original ' ERP Calendar identifier ' Dim CustomProperty As ItemProperty Set CustomProperty = MeetingItem.ItemProperties.Add("ERPUNIQUEID", olText) CustomProperty.Value = "My_ERP_ID" 'Unique id from ERP System
If, as a synchronization mechanism, you choose to use a custom property to store the ERP resource identifier, then your approach to synchronization coding will be a little different than the logic described above. In this scenario, your code will need to loop through all future Outlook calendar items, retrieve the ERP identifier, and then make sure the current Outlook calendar definition matches the information in the ERP calendar. If the data no longer matches, then appropriate action should be taken within Outlook to modify the meeting. If the Outlook calendar item no longer exists in the ERP table, then remove the item from the Outlook calendar altogether by removing appointment or sending out a meeting cancellation notice.
Keeping Your Calendar Current!
This brief introduction to creating Outlook appointments and meetings should be enough to get your coding well underway. The ability to leverage VBA code within Outlook gives developers great capacity to synchronize Outlook Calendars with a remote data source such as DB2 for i.