Import meetings from a CSV or XLSX file

Last reviewed on October 23, 2013   —  29 comments

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.)

Add meeting details to a spreadsheet

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:

  1. Right click on Project1 and choose Insert > Module
  2. Copy and paste the macro into the new module.

More information as well as screenshots are at How to use the VBA Editor

More Information

Original version of the macro: Import Appointments FROM Excel

About Diane Poremsky

Diane Poremsky
A Microsoft Outlook Most Valuable Professional (MVP) since 1999, Diane is the author of several books, including Outlook 2013 Absolute Beginners Book. She also created video training CDs and online training classes for Microsoft Outlook. You can find her helping people online in Outlook Forums as well as in the Microsoft Answers and TechNet forums.

Please post long or more complicated questions at Outlook forums by Slipstick.com.

29 responses to “Import meetings from a CSV or XLSX file”

  1. Neil M

    Hi Diane,

    I tried this, for me it doesn't work, but it may be I'm not doing something right:

    I can't seem to find an option to import xlsx within outlook, I can see CSV and excel97-2003 but not one for macros. (I am using outlook 2010).

    Secondly when I run the macro in excel I get the following error:

    Compile error:
    Syntax error

    I have seperated names by ; and added one extra column in column K for optional attendees.

    Please help?

    1. Diane Poremsky

      You need to use a macro to "import" xlsx files, its not a format that outlook supports by default.

  2. Neil M

    Ok, fiddled around with it and now I have a new error on the macro:

    Compile error:
    User-defined type not defined

    1. Diane Poremsky

      Both user-defined and syntax errors indicate errors in the code. User-defined means something you added to the code is not Dimmed. For example if you add optional attendees using something like this:
      Set myOptionalAttendee = objAppt.Recipients.Add(xlSht.Cells(iRow, 11))
      myOptionalAttendee.Type = olOptional

      you need to add this up at the top with the other DIMs.
      Dim myOptionalAttendee As Outlook.Recipient

      Syntax error means something was spelled wrong, or you were trying to do something like use a string value when Outlook expects a date. "Wrong" values in the time or date columns can cause this error - the value might look like a date to you, but outlook sees it as a string.

  3. Neil M

    Thanks for continued help with this, I'm a bit of a novice obviously. Ok I've got the references set now (seemed to be missing Outlook 14 object library and scripting run time).

    Full list now: Visual Basic for Applications, Excel/Office/Outlook 14.0 Library OLE Automation and Scripting Runtime selected (am I missing any?)

    But when I hit run it ask's to open a file. What am I supposed to select? My macro file has an error Runtime error '438'. Or when I select outlook itself it does nothing.

    I fear I may be too much of a novice to figure this out.

    1. Diane Poremsky

      You'll select the csv or excel file that holds the data you want to import.

      438 means object doesn't support the property - you'd get it if a field has a time value and the code expects a string value. Where does it error on that? You can press F8 to step through the macro and watch where it stops.

  4. Neil M

    Sorry Diane,

    I've figured it out (was running it from excel vba rather than outlook vba doh!), works fab. Only thing is, it puts optional attendees in required. But this isn't a massive issue.

    Thank you for all your help.

    1. Diane Poremsky

      Ah, yeah, that makes sense. :) Thanks for letting me know, I'm sure other people will try the same thing and you've just saved them a headache.

      If you set the optional attendees as optional, it should work... I'll get the code for it (and resources, as I'm sure someone will ask about it too.)

    2. Diane Poremsky

      I used this and it worked - add the optional attendees and resource as the last two columns in the spreadsheet (you can move them, i didn't want to renumber the fields in the code)
      At the top, with the other Dims:
      Dim myOptional As Outlook.Recipient
      Dim myResource As Outlook.Recipient

      Then after the Set myAttendee, add these lines:
      Set myOptional = objAppt.Recipients.Add(xlSht.Cells(iRow, 11))
      myOptional.Type = olOptional
      Set myResource = objAppt.Recipients.Add(xlSht.Cells(iRow, 12))
      myResource.Type = olResource

  5. JP

    "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."

    Actually, it's because you are declaring xlApp as Object that you don't need to manually set a reference. It's the declaration, not the instantiation, that determines the binding. I always recommend using CreateObject to instantiate because you can switch between early and late binding easier.

  6. andygoshorn

    Is there any way to allow for all day events?

    This is a great script btw for folks who have to bounce between walled off calendars.

    1. Diane Poremsky

      That would be .AllDayEvent = True (in with the other properties, before .Save) you wouldn't need to use end time and date - if you have a mix of timed and all day, I would use duration rather than end times and set the all day flag True or False.

  7. Çağlar Şahingöz

    Dear Diane,
    I followed your instructions but once I try to run macro, it gives "there must be at least one name or distribution list in the To,CC, or BCC box." error
    Indeed there is a column for required attendies and all of them are filled with an email address.

    Many thanks in advance.

    1. Diane Poremsky

      Do the attendee columns match the code? (Columns 3, 11, 12) Do you get the dialog to End or Debug? If so, click Debug and see where it says the problem is. If you don't get that dialog, press F8 while viewing the code and step through each line and see what it does.

  8. Jesse Wood

    I have attempted to use the macro above, for the most part it went well. For some reason my required attendees are still not coming over. I have doubled checked and the excel file columns are in the right place as well as the fields are mapped correctly when doing the import. Any help would be greatly appreciated. Is there anything special required for inputting the email addresses? (semicolons, commas, quotations, etc.)

    1. Diane Poremsky

      You need properly formatted addresses - alias@domain.com. They need to be in the correct columns - K & L. Multiple addresses should use semicolon separators.

  9. Jesse Wood

    Not sure what is going on. I double checked and I have the addresses in the correct column C (Required Attendees). For some reason it still will not accept them in Outlook. Any thoughts?

    1. Diane Poremsky

      Can you send me a copy of your book to test against? use diane@slipstick.com

  10. Graham Brown

    I had this problem, I had to comment out optional and resource in order to get it to work.

    1. Diane Poremsky

      Thanks for the update!

  11. Graham Brown

    Hi Diane, Thanks for this article, has helped me to migrate 700 meetings from an old oracle diary system to Kerio Connect. Intrigued as to whether there is a way to set the appointment status to Tentative this way. Do you have any ideas for that? I have a field which is "Firm", "Tentative" or "Cancelled" which I would like to map across.

    1. Diane Poremsky

      You can - you'd put .BusyStatus = olTentative within the With / End with statement if you want every imported appointment to be tentative. If you are mapping them to Busy, tentative, or Free (outlook doesn't have a cancelled), use a Select Case block similar to the one used for reminders to map the oracle words to olbusy, olfree, or oltentative

    2. Diane Poremsky

      Select Case xlSht.Cells(iRow,15)
      Case "Firm"
      .BusyStatus = olBusy
      Case "Tentative"
      .BusyStatus = olTentative
      Case "Cancelled"
      .BusyStatus = olFree
      End Select

  12. David

    Hi, I was able to do it through CSV. The only one thing I need and have not been able to figure out is how to set all up to 1 week reminder. I am not sure in what column to put "1week" I tried and made a reminder column but it didn't work. I have a all tasks in excel, and have been able to export all to Outlook Calendar but I want all those to have reminders of 1 week. Anyone?

  13. Helena

    Hi, Brilliant macro!
    I was wondering if there was a way to create the meetings within different calendars.
    I have 3 calendars in 'My Calendars' : The original "Calendar", and additional calendars "SS16" and "FW16".
    Can you use a column to specify which calendar the meeting is placed in?
    Or can the macro be adjusted so say all are placed in SS16. Then I can just have slightly different macros that put the meetings in different places.

  14. Helena

    Also,
    Is there a way to prevent duplicates? Preferably if an meeting has already been added then the macro will skip the row?
    Thanks! This is making my life so much easier already!

Leave a Reply

If the Post Comment button disappears, press your Tab key.

This site uses XenWord.