Add To Outlook Calendar UDF


September 10, 2008 – 6:33 pm by JP

    Here is a simple UDF that adds an appointment to your Outlook calendar. It can be used from Excel or VBA, and it should be usable from Access as well (untested). It uses another concept I've also been trying out: checking functions to make sure they are successful by using Boolean return values. I believe I read this in Professional Excel Development, but I'd be hard pressed to find the page number. :)

    It's actually quite simple. Just create your function and tell it to return a Boolean value, which you then use to test if the function completed its task. It's part of the encapsulation/modularization habit I've been working on.

For example:

VBA:
  1. Function AddToRange(Rng As Excel.Range, lNum As Long) As Boolean
  2. On Error Resume Next
  3.   Range(Rng).Value = lNum
  4. On Error Goto 0
  5. If Err = 0 Then
  6.   AddToRange = True
  7. End If
  8. End Function

    The above UDF accepts a Range object and Long as arguments, and returns a Boolean value (True or False) depending on the success or failure of the internal operation. It works like this:

VBA:
  1. Dim bIsDone As Boolean
  2. bIsDone = AddToRange("A1", 5)

or

VBA:
  1. If AddToRange("A1", 5) Then
  2. ' your code here
  3. End If

    In other words, the function carries out its operation and returns True or False to the calling sub. You just have to tell the function how to communicate that it was successful (or failed). If we were able to insert the number 5 into Range("A1"), it returns True. This may have nothing to do with the actual function's operation, we just need a way to communicate back to the calling sub whether our function did what it was supposed to do.

    Now here's the real sub. We'll use late-binding to ensure the code can literally be cut and pasted anywhere. Don't forget to include the GetOutlookApplication function below as well.

VBA:
  1. Function AddToCalendar(dteDate As Date, strSubject As String, strLoc As String, dteStart As Date, dteEnd As Date) As Boolean
  2.  
  3. On Error GoTo ErrorHandler
  4.  
  5. Dim olApp As Object
  6. Dim objNewAppt As Object
  7.  
  8. 'get reference to Outlook
  9. Set olApp = GetOutlookApplication
  10.  
  11. If olApp Is Nothing Then
  12.   MsgBox "Cannot access Outlook. Exiting now", vbInformation
  13.   GoTo ErrorHandler
  14. End If
  15.  
  16. Set objNewAppt = olApp.createitem(1) ' 1 is the constant for olAppointmentItem when using late-bound code, or VBScript
  17.  
  18. With objNewAppt
  19.   .Start = dteDate & " " & dteStart
  20.   .End = dteDate & " " & dteEnd
  21.   .Subject = strSubject
  22.   .Location = strLoc
  23.   .reminderset = True
  24.   .ReminderMinutesBeforeStart = 30
  25.   .Save
  26. End With
  27.  
  28. AddToCalendar = True
  29. GoTo ExitProc
  30.  
  31. ErrorHandler:
  32. AddToCalendar = False
  33.  
  34. ExitProc:
  35. Set olApp = Nothing
  36. Set objNewAppt = Nothing
  37. End Function

VBA:
  1. Function GetOutlookApplication()
  2.  
  3. On Error Resume Next
  4. Set GetOutlookApplication = GetObject(, "Outlook.Application")
  5.  
  6. If Err <> 0 Then
  7.   Set GetOutlookApplication = CreateObject("Outlook.Application")
  8. End If
  9. On Error GoTo 0
  10.  
  11. End Function

    This function may be used in VBA, or directly in the worksheet. Just be careful when using it in an Excel worksheet, because it will recalculate and you'll end up with multiple duplicate appointments (unless you happen to change the parameters before every recalculation).

    Also note there are two different ways to call this function, depending on where you are calling it. If you call it from VBA, you must enclose the date (and time, which is technically stored as a Date) arguments in hash marks, like this:

VBA:
  1. Sub SaveAppt()
  2.  
  3. If AddToCalendar(#10/11/2008#, "My Meeting", "My desk", #10:00:00 AM#, #11:00:00 AM#) Then
  4.   MsgBox "appointment was added"
  5. End If
  6.  
  7. End Sub

    If you are doing it from the worksheet, all of the arguments must be in quotes:

VBA:
  1. =AddToCalendar("10/11/2008","My topic","My location","10:00 AM","11:00 AM")

    Since the function returns a Boolean value, the worksheet will show "TRUE" in the cell, if the function was successful, and "FALSE" if not.

    The GetOutlookApplication function returns a reference to the Outlook object to the calling function. Notice that in this case, we don't set up a Boolean return variable, because we want to set an actual object reference, not just test to see if our function was successful.

Download a working sample here.

Enjoy,
JP


Share and Enjoy:
  • StumbleUpon
  • Technorati
  • Digg
  • Google
  • del.icio.us
  • MisterWong

Print This Post Print This Post  |  Email This Post Email This Post  |  Permalink  |  Subscribe to this feed Subscribe now!

Filed Under: Access, Excel, Outlook, UDF, VBA, automation
Tags: , , ,

This post has 217 views since September 10, 2008 – 6:33 pm.

Post a Comment

To post VBA code in your comment, use [VBA] tags, like this: [VBA]Code goes here[/VBA].





Subscribe without commenting

Keep Reading:

Browse Posts:


« Excel Blogs List and OPML Feeds List || Where were you? »