Extending my Outlook VBA code to use Free/Busy and schedule appts.

Question

I sometimes travel internationally and when I do, I have to meet with 5 to 10 internal collegues. Till now, what I do is use the following code to send out introductions and see when they may be free to meet. I put their first and last names in Excel, as well as whether or not I've met them before.

Sub CreateInvite(lngDuration As Long, strInviteBody As String, strLocation As String, strSubject As String, strRec As String)
Dim olApp As Outlook.Application
Dim olMeetingInvite As AppointmentItem
Set olApp = CreateObject("outlook.application")
Set olMeetingInvite = olApp.CreateItem(olAppointmentItem)
With olMeetingInvite
.Body = strInviteBody
.Display
.Recipients.Add strRec
.Recipients.ResolveAll
.Subject = strSubject
.Location = strLocation
.Duration = lngDuration
End With
End Sub
Sub RunInviteCreator()
Dim strRec As String
Dim strRecFirstName As String
Dim strLoc As String
Dim xlApp As Excel.Application
Set xlApp = CreateObject("excel.application")
Dim xlWS As Excel.Workbook
Set xlWS = xlApp.Workbooks.Open("C:\Invitees.xls")
Dim xlSheet As Excel.Worksheet
Set xlSheet = xlWS.ActiveSheet
Dim strSubject As String
Dim strLocation As String
Dim i As Integer
Dim iMeetingLength As Integer
Dim strHour As String
Dim strMeetingLength As String
Dim strInviteBody1 As String
Dim strInviteBody2 As String
Dim strInviteBody As String
Dim strKnown As String

For i = 2 To 21
strRecFirstName = xlSheet.Cells(i, 1)
strRec = xlSheet.Cells(i, 3)
strSubject = xlSheet.Cells(i, 6)
strLocation = xlSheet.Cells(i, 8) & "/" & xlSheet.Cells(i, 9)
iMeetingLength = xlSheet.Cells(i, 7)
If iMeetingLength > 60 Then strHour = "hours" Else strHour = "hour"
strMeetingLength = CStr(iMeetingLength / 60)
strInviteBody1 = "Dear " & strRecFirstName & "," & vbCrLf

strKnown = "Allow me to introduce myself. I am Mahin from HQ in Boise." & vbCrLf & vbCrLf

strInviteBody2 = _
"I will be in " & area & "from May 17 to May 23 and would really like to meet with you, learn about your business and see how we can cooperate to drive initiatives in FY10." & vbCrLf & vbCrLf & _
"I’ve checked your calendar and this looks like a good time. If not, however, please feel free to propose a new time. The best time would be Friday, which I am leaving open to accommodate calendar reschedules. Also, I have booked this for " & strMeetingLength & " " & strHour & ". If you feel the meeting needs to be longer or shorter, please let me know." & vbCrLf & vbCrLf & _
"I really look forward to meeting you and working with you." & vbCrLf & vbCrLf & _
"Best Regards," & vbCrLf & vbCrLf & _
"Mahin" 

If xlSheet.Cells(i, 10) = "Yes" Then
strInviteBody = strInviteBody1 & strInviteBody2
Else
strInviteBody = strInviteBody1 & strKnown & strInviteBody2
End If

CreateInvite CLng(iMeetingLength), strInviteBody, strLocation, strSubject, strRec
Next i

End Sub

What I'd like to do is to check Exchange's free/busy for all of these people first and then have my program send an invite to their calendar and reference that in the mail above. Can anyone provide me with some pointers on how to do this in Outlook OM? Thx!

Answer 1

For the free/busy info, take a look at the Recipient.FreeBusy member - you'll need to do some parsing of the result.

For the calendar invite, you'd just create a new item of type olAppointmentItem, e.g.: Set myItem = myOlApp.CreateItem(olAppointmentItem) Example here.