Synchronize Your Outlook Calendar with DB2 for i ERP Data

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?

–Robert

Hi, Robert:

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:

• Can the resources (a.k.a., users) identified in the ERP calendar table be converted to an email identity that Outlook can recognize? Example: if the Calendar stores i/OS user profile names to track profile “MIKE” as a resource, how can i/OS user profile “MIKE” be mapped to “Michael.Sansoterra@MyCompany.com”?
• Does each entry in the ERP Calendar table have a unique identifier that can be used to track changes or deletions to entries that have already propagated to the Outlook Calendar?
• How can you maintain a cross-reference between the Calendar items in both systems?
• Can this code be run on behalf of all ERP “resource” users using a single instance of Outlook dedicated to this synchronization task? Or, will each Outlook user need the VBA code distributed to his or her machine to run the synchronization for his or her own personal Calendar?

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.

• The mail item can be used to send a standard email message to each resource whenever the ERP Calendar is synchronized with Outlook and the time frame for the resource to do something is within a certain threshold (maybe send an email out to each resource 30 minutes before the work is to begin). However, a normal email doesn’t put anything on the calendar so we’ll ignore this option in favor of better alternatives.
• An appointment item can be created in VBA to put an appointment for the task right on the Outlook Calendar. However, since the appointment item is specific to a personal calendar of the logged in user, the drawback of using an appointment item is that each “ERP Resource” user would be required to run this Outlook code (or you’d need a complex mechanism to run an instance of Outlook under varying user profiles.)
• Finally, a meeting item can be implemented to combine the best of the email and appointment approach into a single unit. If you’re unfamiliar with a meeting request in Outlook, it grants users the ability to setup a “meeting” at a specific time and for a specific duration, to invite attendees to the meeting, to specify a location for the meeting, etc. Additionally, as with appointments, meetings can be assigned automatic “reminders” that will remind users of an upcoming meeting a specified duration before the meeting starts. When the meeting request is finished, each attendee will receive an email inviting them to attend.

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:

• EntryID–A unique ID string assigned to every mail item; this is important because it can be used as a synchronization marker to match this specific meeting request with a specific entry in the ERP calendar table.
• Attachments–For including e-documents with the meeting request.
• OptionalAttendees–A delimited list of optional attendees, similar to “RequiredAttendees”.

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):

Synchronization Ideas

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.
B. Check to see if the appointment already has an entry in Outlook:
1. If YES, check for changes and update the Outlook appointment or meeting.
2. If NO, create a new Outlook appointment or meeting and record unique identifiers from each system.

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.

Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. Send your questions or comments for Mike via the IT Jungle Contact page.

RELATED STORY

Automate E-Mail Operations with Outlook and VBA

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot