Excel and Outlook Question

[JeenLeen]

Does anyone know how to generate multiple meeting/appointment requests (to multiple people) from a table in Excel (or a .txt or .csv)?

For example, if I had a table with the following rows: MeetingTitle, Day, Time
and I wanted to send it to 3 people, is there any built-in aspect of Outlook/Excel, something in VBA, or a macro that could let me use that table to generate corresponding meetings in Outlook?

I tried using Google, which brought up a few things, but nothing that really seemed to answer my question (that or the VBA was so over my head I didn't understand that it was answering my question.)

Thanks!

[Manga Shoggoth]

Excel and VBA would probably be the best way to go for this.

How To: Create an Appointment as a meeting on the Calendar (http://msdn.microsoft.com/en-us/library/office/ff861308.aspx) gives this:


Sub CreateAppt()
Dim myItem As Object
Dim myRequiredAttendee, myOptionalAttendee, myResourceAttendee As Outlook.Recipient

Set myItem = Application.CreateItem(olAppointmentItem)
myItem.MeetingStatus = olMeeting
myItem.Subject = "Strategy Meeting"
myItem.Location = "Conf Rm All Stars"
myItem.Start = #9/24/2009 1:30:00 PM#
myItem.Duration = 90
Set myRequiredAttendee = myItem.Recipients.Add("Nate Sun")
myRequiredAttendee.Type = olRequired
Set myOptionalAttendee = myItem.Recipients.Add("Kevin Kennedy")
myOptionalAttendee.Type = olOptional
Set myResourceAttendee = myItem.Recipients.Add("Conf Rm All Stars")
myResourceAttendee.Type = olResource
myItem.Display
myItem.Send
End Sub


The code itself is straightforward enough - myItem is the actual appointment (where you specify the times dates and descriptions).

It then shows how you add required and optional attendees, and this is where the extra logic would come in, and that depends on how you have formatted your spreadsheet.

I'm at work at the moment, so I don't have much time to do a detailed example - if you are still stuck (and my family doesn't get in the way...) I'll have a look tonight.

EDIT:

Assuming a worksheet called "Messages" like this:

{table="head"]Meeting Title|Location|Start (Date/Time)|Duration (Min)
Strategy Meeting|Conf Rm All Stars|24/09/2009 13:30:00 | 90
Strategy Meeting|Conf Rm All Stars|01/10/2009 13:30:00 | 90
Strategy Meeting|Conf Rm All Stars|08/10/2009 13:30:00 | 90
Strategy Meeting|Conf Rm All Stars|15/10/2009 13:30:00 | 90
Strategy Meeting|Conf Rm All Stars|22/10/2009 13:30:00 | 90
Strategy Meeting|Conf Rm All Stars|29/10/2009 13:30:00 | 90
Strategy Meeting|Conf Rm All Stars|05/11/2009 13:30:00 | 90
Strategy Meeting|Conf Rm All Stars|12/11/2009 13:30:00 | 90
[/table]


The following code should do the trick:



Sub CreateAppt()
Dim myItem As Object
Dim myRequiredAttendee, myOptionalAttendee, myResourceAttendee As Outlook.Recipient
Dim lngI As Long

lngI = 2
Do
Set myItem = Application.CreateItem(olAppointmentItem)
myItem.MeetingStatus = olMeeting
myItem.Subject = Sheets("Meetings").Cells(lngI, 1)
myItem.Location = Sheets("Meetings").Cells(lngI, 2)
myItem.Start = Sheets("Meetings").Cells(lngI, 3)
myItem.Duration = Sheets("Meetings").Cells(lngI, 4)
Set myRequiredAttendee = myItem.Recipients.Add("First Victim")
myRequiredAttendee.Type = olRequired
Set myRequiredAttendee = myItem.Recipients.Add("Second Victim")
myRequiredAttendee.Type = olRequired
Set myRequiredAttendee = myItem.Recipients.Add("Third Victim")
myRequiredAttendee.Type = olRequired
myItem.Display
myItem.Send

lngI = lngI + 1
Loop Until Sheets("Meetings").Cells(lngI, 1) = ""

End Sub


Caveat: Not fully tested - I don't have Exchange at home.

[JeenLeen]

Thank you. I hope to test this early next week.

Edit: Work delayed me a fair bit, but I'm finally getting to testing it. It doesn't work as is. I'm mainly adding this edit-note to not leave it incomplete for anyone who stumbles on it in the future. (Not responding to the post to avoid thread necromancy.) I think the main problem is that, when I go into Excel in VBA, it doesn't seem to recognize the Outlook items. I tried Tools --> References to add the Outlook reference, but References is grayed out.

I did find a... not great but at least quicker method via Access at
http://support.microsoft.com/kb/160502.

EDIT Last of Many: Finally got some code that actually works as I want it to. (This has you type in the e-mail addresses instead of pull them from Excel, but pulling from Excel should be easy enough.)


Sub CreateAppt()

Dim outobj As Outlook.Application
Dim outappt As Outlook.AppointmentItem
Dim lngI As Long

lngI = 2

Do While Sheets("Meetings").Cells(lngI, 1) <> ""
Set outobj = CreateObject("outlook.application")
Set outappt = outobj.CreateItem(olAppointmentItem)
With outappt
.MeetingStatus = olMeeting 'makes it a meeting instead of appt
.Start = Sheets("Meetings").Cells(lngI, 3)
.Duration = Sheets("Meetings").Cells(lngI, 4)
.Subject = Sheets("Meetings").Cells(lngI, 1)
.Location = Sheets("Meetings").Cells(lngI, 2)
.ReminderMinutesBeforeStart = 1440 'one day
.ReminderSet = True

.Recipients.Add ("[email protected]")
.Recipients.Add ("[email protected]")
.Recipients.Add ("[email protected]")

'.Display '.Display causes the request to pop up. Unnecessary
.Send
.Save

lngI = lngI + 1
End With
Loop

MsgBox "All " & (lngI - 2) & " appointments sent"

End Sub