Volume 11, Number 5 -- February 2, 2011

Synchronize Your Outlook Calendar with DB2 for i ERP Data

Published: 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?


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

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

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.


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

Sponsored By

Free 1-Hour Security Consultation
with Patrick Botz

                                                 Need advice you can trust on security audits,
                                                 PCI, HIPAA or SOX requirements, 3rd party
                                                 software, rational password management,
                                                 public authority or Security Level 40?

                                                 Spend an hour with Patrick Botz - FREE -
                                                 and get actionable info and tips
to get you
                                                 started. Custom, 100% independent advice

                                                 from the guy who helped architect Power
                                                 Systems / IBM i security.

                                                 Click to set up your free session with Pat.
                                                 World-class security expertise is no longer just
                                                 for the big guys!

Senior Technical Editor: Ted Holt
Technical Editor: Joe Hertvik
Contributing Technical Editors: Edwin Earley, Brian Kelly, Michael Sansoterra
Publisher and Advertising Director: Jenny Thomas
Advertising Sales Representative: Kim Reed
Contact the Editors: To contact anyone on the IT Jungle Team
Go to our contacts page and send us a message.

Sponsored Links

PowerTech:  Schedule a FREE IBM i Compliance Assessment!
Vision Solutions:  The State of Resilience 2010. Download the report now!
Four Hundred Monitor Calendar:  Latest info on national conferences, local events, & Webinars


IT Jungle Store Top Book Picks

BACK IN STOCK: Easy Steps to Internet Programming for System i: List Price, $49.95

The iSeries Express Web Implementer's Guide: List Price, $49.95
The iSeries Pocket Database Guide: List Price, $59
The iSeries Pocket SQL Guide: List Price, $59
The iSeries Pocket WebFacing Primer: List Price, $39
Migrating to WebSphere Express for iSeries: List Price, $49
Getting Started with WebSphere Express for iSeries: List Price, $49
The All-Everything Operating System: List Price, $35
The Best Joomla! Tutorial Ever!: List Price, $19.95

The Four Hundred
Notes/Domino: Less Platform Talk, More Programming Action

IBM Trumpets LotusLive Successes, New App Partnerships

RPG Surges in Popularity, According to Language Index

Mad Dog 21/21: The So-Called Network

Palmisano Rakes in $9 Million for IBM's 2010 Performance

Four Hundred Stuff
Magic to Sell MicroStrategy BI into IBM i Base

Info Builders Shrinks BI Apps with InfoMini

Mobility, the Cloud, and Social Business Top Lotusphere Agenda

Oracle Touts JDE EnterpriseOne Growth

m-Power Graphing Feature Gets an Overhaul

Four Hundred Monitor
Four Hundred Monitor's
Full iSeries Events Calendar

System i PTF Guide
September 25, 2010: Volume 12, Number 39

September 18, 2010: Volume 12, Number 38

September 11, 2010: Volume 12, Number 37

September 4, 2010: Volume 12, Number 36

August 28, 2010: Volume 12, Number 35

August 21, 2010: Volume 12, Number 34

TPM at The Register
ARM Holdings eager for PC and server expansion

VMware Go promoted to Pro

Intel shakes off $1bn chipset flaw

Intel finds flaw in Sandy Bridge chipset

VMware creates private clouds for newbies

Verizon borgs Terremark for $1.4bn

Mellanox itching to close Voltaire, crank up InfiniBand

Deliveries for final Apple Xserves stalled to April

Xen sends Citrix Q4 into the clouds

Dell talks shopping in Davos

Cisco borgs network 'guardian angel'

Robust network spending drives Juniper's Q4


Botz & Associates, Inc.
SEQUEL Software
System i Developer

Printer Friendly Version

Synchronize Your Outlook Calendar with DB2 for i ERP Data

Another Reason Why Function Subprocedures Should Not Modify Their Parameters

Admin Alert: QPWDRULES Rules!!! Opening Up User Password Options with i 6.1

Four Hundred Guru


Subscription Information:
You can unsubscribe, change your email address, or sign up for any of IT Jungle's free e-newsletters through our Web site at http://www.itjungle.com/sub/subscribe.html.

Copyright © 1996-2011 Guild Companies, Inc. All Rights Reserved.
Guild Companies, Inc., 50 Park Terrace East, Suite 8F, New York, NY 10034

Privacy Statement