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
Hi, This code is awesome, the only issue I am having with it is that the it is only pulling the start date and not the start time across to the meeting invitation with the issue being with the " + xlSht.Cells(iRow, 7) ". Same with the end time. The code runs fine without it. Any suggestions?
I know this is an old thread, but I would like to specify which calendar to import the cvs data into versus into my default calendar. Any help would be appreciated!
Select the calendar then start the import wizard - it should go into that calendar. The exception: if its not in an account in your profile. If its a shared mailbox, you need to import them move - create a new calendar folder, import into it then move the events to the correct calendar. (Use a list view on the calendar so you can select all).
Thanks so much for the reply! I have tried that solution before commenting here and the script still places it on my main default calendar. I have even totally unchecked all calendars so that my Test calendar is the only one open, and still get same results.
Oh, shoot, my bad. I wasn't thinking straight... when you use a macro, you need to change the folder it is using.
Where is the calendar?
This uses the default calendar -
Set objAppt = Application.CreateItem(olAppointmentItem)
To use a different folder, you need to set the folder then .add the event. This will add the event to subfolder under Calendar, called new calendar -
Dim CalFolder As Outlook.MAPIFolder
Set CalFolder = olNs.GetDefaultFolder(olFolderCalendar).Folders("new calendar")
Set objAppt = CalFolder.Items.Add(olAppointmentItem)
(That sample is from Create Appointments Using Spreadsheet Data)
Hi Dianne, this script is amazing and comes in very handy for me to send out MULTIPLE invites at once!
Is there a way this can be modified to choose which outlook account the meeting invite is sent from?
I used to be able to change it in the dialogue that meeting invite that popped up, but since changing to O365 and having our accounts set up differently, I cannot!
Is the other address an account in your profile? If so, the macro to send meeting requests from another account should work -
put this before the line tyo create the new appt item.
Dim oAccount As Outlook.Account
For Each oAccount In Application.Session.Accounts
If oAccount = "account@displayname" Then
Set objAppt = Application.CreateItem(olAppointmentItem)
.SendUsingAccount = oAccount
' add subject and other fields
if its a shared mailbox, you would just need to add this to the With block - put it before the .subject line.
.SentOnBehalfOfName = "email@example.com"
In the excel picture posted with this article, is there any special format for the "Required Attendees" column for the csv upload?
it's a simple text field - I don't recall if I tested it with hyperlinks in the field.
This is great. I was already using this process without the macro, but this helps me understand it a bit better. Is it possible to cancel meetings using this process as well?
I have not tried, but it should be possible if the event is on the outlook calendar. You'd use vba to find it then cancel.
Diane- This code rocks! Thanks so much for pulling it together & answering all the questions.
We've got it working, now we want to do one additional thing. We want to have it send from a sub-calendar of mine. Any thoughts on how to do this?
Just wondering - if you wanted to save the files as a ics file to a specific file location (e.g. in the documents folder), how would you do that?
Do you want to use a macro to export the calendar as an ics or to convert a csv to an ics ?
I dont have a macro that does a direct csv to ics conversion, but i have one that could save the calendar as an ics.
Hi Diane. Thank you so much for the code. It's really helpful. Is it possible for you to share the macro code to save the calendar as an .ics, please?
I am trying to make se of your great code here, but note that all my Meeting Dates which I schedule are ALL appearing on the Calendar as 30/12/1899 and no matter what I do, it always comes back with this same date >>>
That points to a problem with the date format in the cells. What format are you using on the cells? The standard short date format should work... avoid formats with words.