Add-Ins
Blogs I Read
ContactAdd 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:
-
Function AddToRange(Rng As Excel.Range, lNum As Long) As Boolean
-
On Error Resume Next
-
Range(Rng).Value = lNum
-
On Error Goto 0
-
If Err = 0 Then
-
AddToRange = True
-
End If
-
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:
-
Dim bIsDone As Boolean
-
bIsDone = AddToRange("A1", 5)
or
-
If AddToRange("A1", 5) Then
-
' your code here
-
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.
-
Function AddToCalendar(dteDate As Date, strSubject As String, strLoc As String, dteStart As Date, dteEnd As Date) As Boolean
-
-
On Error GoTo ErrorHandler
-
-
Dim olApp As Object
-
Dim objNewAppt As Object
-
-
'get reference to Outlook
-
Set olApp = GetOutlookApplication
-
-
If olApp Is Nothing Then
-
MsgBox "Cannot access Outlook. Exiting now", vbInformation
-
GoTo ErrorHandler
-
End If
-
-
Set objNewAppt = olApp.createitem(1) ' 1 is the constant for olAppointmentItem when using late-bound code, or VBScript
-
-
With objNewAppt
-
.Start = dteDate & " " & dteStart
-
.End = dteDate & " " & dteEnd
-
.Subject = strSubject
-
.Location = strLoc
-
.reminderset = True
-
.ReminderMinutesBeforeStart = 30
-
.Save
-
End With
-
-
AddToCalendar = True
-
GoTo ExitProc
-
-
ErrorHandler:
-
AddToCalendar = False
-
-
ExitProc:
-
Set olApp = Nothing
-
Set objNewAppt = Nothing
-
End Function
-
Function GetOutlookApplication()
-
-
On Error Resume Next
-
Set GetOutlookApplication = GetObject(, "Outlook.Application")
-
-
If Err <> 0 Then
-
Set GetOutlookApplication = CreateObject("Outlook.Application")
-
End If
-
On Error GoTo 0
-
-
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:
-
Sub SaveAppt()
-
-
If AddToCalendar(#10/11/2008#, "My Meeting", "My desk", #10:00:00 AM#, #11:00:00 AM#) Then
-
MsgBox "appointment was added"
-
End If
-
-
End Sub
If you are doing it from the worksheet, all of the arguments must be in quotes:
-
=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
Print This Post
|
Email This Post
|
Permalink
|
Subscribe now!
Filed Under: Access, Excel, Outlook, UDF, VBA, automation
Tags: Boolean, CreateObject, GetObject, UDF
This post has 217 views since September 10, 2008 – 6:33 pm.
Loading ...