Importing meeting details from an CSV or Excel .xlsx file won't send meeting requests. If you want to import meetings and then send them to the attendees, you will need to use a macro to create the meeting from the spreadsheet data.
Begin by creating an Excel file with the following column headers:
Subject, Location, Required Invitees, Categories, Start Date, End Date, Start Time, End Time, Reminder, Duration, Optional Attendee, Resource. (You can add other Outlook fields, if needed.)
Create one row for each meeting, using a semicolon as the delimiter if you have more than one attendee per meeting. Save the workbook as either a CSV or as a native Excel (*.xlsx) file. (Yes, you can import Excel .xlsx files when you use a macro.)
Note: when you have more than one attendee, the name resolution dialog may come up when you use .Send in the macro. Avoid it by clicking the Send button yourself, after the macro creates all of the meeting requests.
Create meetings macro
If you just want to create appointments, remove the lines that apply to myAttendee and the line that sets the meeting status.
Because we're using Set xlApp = CreateObject("Excel.Application") we don't need to set a reference to Microsoft Excel Object Model in the VB Editor's Tools, References dialog. This makes the code a little more portable, as the user doesn't have to set the reference before using the code.
If we use Set xlApp = New Excel.Application a reference would be required.
Sub CreateMeetingsfromCSV() ' Worksheet format: Subject, Location, Required Invitees, Categories, Start_Date, End_Date, Start_Time, End_Time, Reminder, Duration, Optional Attendees, Resource ' Possible Values for Reminder Field is :'No Reminder','0 Minutes','1 Day','2 Days', '1 Week' Dim xlApp As Object 'Excel.Application Dim xlWkb As Object ' As Workbook Dim xlSht As Object ' As Worksheet Dim rng As Object 'Range Dim objAppt As Outlook.AppointmentItem Dim myAttendee As Outlook.Recipient Dim myOptional As Outlook.Recipient Dim myResource As Outlook.Recipient 'Set xlApp = New Excel.Application Set xlApp = CreateObject("Excel.Application") strFilepath = xlApp.GetOpenFilename If strFilepath = False Then xlApp.Quit Set xlApp = Nothing Exit Sub End If Set xlWkb = xlApp.Workbooks.Open(strFilepath) Set xlSht = xlWkb.Worksheets(1) Dim iRow As Integer Dim iCol As Integer iRow = 2 iCol = 1 While xlSht.Cells(iRow, 1) <> "" Set objAppt = Application.CreateItem(olAppointmentItem) Set myAttendee = objAppt.Recipients.Add(xlSht.Cells(iRow, 3)) myAttendee.Type = olRequired Set myOptional = objAppt.Recipients.Add(xlSht.Cells(iRow, 11)) myOptional.Type = olOptional Set myResource = objAppt.Recipients.Add(xlSht.Cells(iRow, 12)) myResource.Type = olResource With objAppt .Subject = xlSht.Cells(iRow, 1) & Now() .Location = xlSht.Cells(iRow, 2) .Categories = xlSht.Cells(iRow, 4) .Start = xlSht.Cells(iRow, 5) + xlSht.Cells(iRow, 7) ' Use either .Duration or .End '.End = xlSht.Cells(iRow, 6) + xlSht.Cells(iRow, 8) .Duration = xlSht.Cells(iRow, 10) ' This tells Outlook it's a meeting .MeetingStatus = olMeeting Select Case xlSht.Cells(iRow, 9) Case "No Reminder" .ReminderSet = False Case "0 minutes" .ReminderSet = True .ReminderMinutesBeforeStart = 0 Case "1 day" .ReminderSet = True .ReminderMinutesBeforeStart = 1440 Case "2 days" .ReminderSet = True .ReminderMinutesBeforeStart = 2880 Case "1 week" .ReminderSet = True .ReminderMinutesBeforeStart = 10080 End Select For Each myAttendee In .Recipients myAttendee.Resolve Next .Save .Display '.Send ' hit the send button yourself to avoid Select names dialog End With iRow = iRow + 1 Wend xlWkb.Close xlApp.Quit Set xlWkb = Nothing Set xlApp = Nothing End Sub
How to use macros
First: You will need macro security set to low during testing.
To check your macro security in Outlook 2010 or 2013, go to File, Options, Trust Center and open Trust Center Settings, and change the Macro Settings. In Outlook 2007 and older, it’s at Tools, Macro Security.
After you test the macro and see that it works, you can either leave macro security set to low or sign the macro.
Open the VBA Editor by pressing Alt+F11 on your keyboard.
To put the code in a module:
- Right click on Project1 and choose Insert > Module
- Copy and paste the macro into the new module.
More information as well as screenshots are at How to use the VBA Editor
Original version of the macro: Import Appointments FROM Excel