Last reviewed on October 23, 2013   —  39 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

Comments

  1. Neil M says

    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?

    • Diane Poremsky says

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

  2. Neil M says

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

    Compile error:
    User-defined type not defined

    • Diane Poremsky says

      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 says

    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.

    • Diane Poremsky says

      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 says

    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.

    • Diane Poremsky says

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

    • Diane Poremsky says

      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 says

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

    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.

    • Diane Poremsky says

      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 says

    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.

    • Diane Poremsky says

      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 says

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

  9. Jesse Wood says

    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?

  10. Graham Brown says

    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.

    • Diane Poremsky says

      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

    • Diane Poremsky says

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

  11. David says

    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?

    • Diane PoremskyDiane Poremsky says

      Reminder are handled through the case statement - you'd put 1 week in xlSht.Cells(iRow, 9) - or, if you wanted every reminder to be one week, just use .ReminderMinutesBeforeStart = 10080 in the code.

      Case "1 week"
      .ReminderSet = True
      .ReminderMinutesBeforeStart = 10080

  12. Helena says

    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.

  13. Helena says

    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!

  14. Sankalp Das says

    Hi Diane,
    Thanks for writing such a useful code. I have tried it with default calendar and it works fine but I want to use it for a shared / people's calendar. Could you please suggest edits to make this code work for a shared calendar.
    Rergards,
    Sankalp

    • Diane PoremskyDiane Poremsky says

      This line tells it to create a new item in the default calendar:
      Set objAppt = Application.CreateItem(olAppointmentItem)

      This should work if the calendar's mailbox is open in your profile - get the getfolderpath function from http://www.slipstick.com/developer/working-vba-nondefault-outlook-folders/#GetFolderPath

      Set CalFolder= GetFolderPath("other-datafile-display-name\Calendar")
      Set objAppt = CalFolder.Items.Add(olAppointmentItem)

      if it's just a shared calendar, there is a code sample in the same article with the function that shows how to get it.

  15. Sankalp Das says

    Diane,
    I tried all but I couldn't able to send invites through calendar named "Resource - My Unlimited Potential". Here is the code I am using, it works fine with default calendar:
    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

    With objAppt
    .Subject = xlSht.Cells(iRow, 1)
    .Location = xlSht.Cells(iRow, 2)
    .Categories = xlSht.Cells(iRow, 4)
    .Start = xlSht.Cells(iRow, 5) + xlSht.Cells(iRow, 7)
    .Duration = xlSht.Cells(iRow, 10)
    .Body = xlSht.Cells(iRow, 13)
    .MeetingStatus = olMeeting
    ' This tells Outlook it's a meeting

    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

    Please edit it to work with shared calendar named " Resource - My unlimited Potential"

    Thanks in advance,
    Sankalp

    • Diane PoremskyDiane Poremsky says

      Try this replacing the lines that set the new item with this (the two calfolder lines can go up with the other Dim lines or stay with these lines).

      Dim calFolder as Outlook.folder
      Set CalFolder= GetFolderPath("Resource - My unlimited Potential\Calendar")

      While xlSht.Cells(iRow, 1) ""
      Set objAppt = CalFolder.Items.Add(olAppointmentItem)
      Set myAttendee = objAppt.Recipients.Add(xlSht.Cells(iRow, 3))

  16. Sankalp Das says

    Hi Diane,
    Now I am getting "Object variable or With block variable not set on the following line:
    Set objAppt = calFolder.Items.Add(olAppointmentItem)

    I even used the getfolderpath function you mentioned in other post.
    Regards,
    Sankalp

    • Diane PoremskyDiane Poremsky says

      that error generally means a variable wasn't set - but it is set in the macro:
      Dim objAppt As Outlook.AppointmentItem

      is the resource calendar opened as part of a mailbox (with all of the other mailbox folders visible) or just as a shared calendar?

    • Diane PoremskyDiane Poremsky says

      Then you may need to use different code to get the calendar.

      put these lines after the other Dim lines at the top:
      Dim calFolder as Outlook.folder
      Dim NS As Outlook.NameSpace
      Dim objOwner As Outlook.Recipient
      Set NS = Application.GetNamespace("MAPI")
      Set objOwner = NS.CreateRecipient("Resource - My unlimited Potential")
      objOwner.Resolve
      If objOwner.Resolved Then
      'MsgBox objOwner.Name
      Set CalFolder = NS.GetSharedDefaultFolder(objOwner, olFolderCalendar)
      End If

      and use this in the code:
      While xlSht.Cells(iRow, 1) ""
      Set objAppt = CalFolder.Items.Add(olAppointmentItem)
      Set myAttendee = objAppt.Recipients.Add(xlSht.Cells(iRow, 3))

  17. Sankalp das says

    Following code is working byt i want to use your code with excel:
    Sub CreateOtherUserAppointment()
    Dim objApp As Outlook.Application
    Dim objNS As Outlook.NameSpace
    Dim objFolder As Outlook.MAPIFolder
    Dim objDummy As Outlook.MailItem
    Dim objRecip As Outlook.Recipient
    Dim objAppt As Outlook.AppointmentItem
    Dim strMsg As String
    Dim strName As String
    On Error Resume Next

    ' ### name of person whose Calendar you want to use ###
    strName = "FlaviusJ"

    Set objApp = CreateObject("Outlook.Application")
    Set objNS = objApp.GetNamespace("MAPI")
    Set objDummy = objApp.CreateItem(olMailItem)
    Set objRecip = objDummy.Recipients.Add(strName)
    objRecip.Resolve
    If objRecip.Resolved Then
    On Error Resume Next
    Set objFolder = _
    objNS.GetSharedDefaultFolder(objRecip, _
    olFolderCalendar)
    If Not objFolder Is Nothing Then
    Set objAppt = objFolder.Items.Add
    If Not objAppt Is Nothing Then
    With objAppt
    .Subject = "Test Appointment"
    .Start = Date + 14
    .AllDayEvent = True
    .Save
    End With
    End If
    End If
    Else
    MsgBox "Could not find " & Chr(34) & strName & Chr(34), , _
    "User not found"
    End If

    Set objApp = Nothing
    Set objNS = Nothing
    Set objFolder = Nothing
    Set objDummy = Nothing
    Set objRecip = Nothing
    Set objAppt = Nothing
    End Sub

Leave a Reply

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

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