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
More Information
Original version of the macro: Import Appointments FROM Excel
kierran says
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?
Joe Davis says
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!
Diane Poremsky says
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).
Joe Davis says
Hi Diane,
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.
Diane Poremsky says
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)
Robbie Shelson says
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!
Diane Poremsky says
Is the other address an account in your profile? If so, the macro to send meeting requests from another account should work -
https://www.slipstick.com/developer/send-using-default-or-specific-account/#meeting
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)
With objAppt
.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 = "alias@domain.com"
Nicole Yamamoto says
In the excel picture posted with this article, is there any special format for the "Required Attendees" column for the csv upload?
Diane Poremsky says
it's a simple text field - I don't recall if I tested it with hyperlinks in the field.
Michael Coleman says
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?
Diane Poremsky says
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.
Jeannine Moegenburg says
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?
Chev says
Hi Diane,
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?
Diane Poremsky says
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.
Bernardo Senra says
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?
stuart says
Thanks Diane.
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 >>>
Diane Poremsky says
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.
Lee Millward says
Hi Diane, This is wonderful piece of code, i'm trying to use it to populate room mailbox calendars. I have it all working but don't know how to change the meeting organizer from the default outlook profile. Any tips?
Thanks
Diane Poremsky says
You can change the send from account but can't change the organizer to any greater extent than you can in Outlook.
https://www.slipstick.com/developer/send-using-default-or-specific-account/#meeting shows how to do it in code.
Moritz says
Thank you very much for this efficient and helpful tool!
The one and only difficulty which I encountered is that a blank excel cell
for the optional attendees stops the macro and gives an error.
So what is the best thing to do if I have some meetings with required and optional attendees and some with required ones only?
Is there anything like a "NaN" in Matlab to set an "empty" cell?
Thanks a lot for your help!
Moritz
Diane Poremsky says
You'd use an if...
If xlSht.Cells(iRow, 11))<> "" then ' (WordPress might remove the less than and greater than symbols before the quotes)
Set myOptional = objAppt.Recipients.Add(xlSht.Cells(iRow, 11))
myOptional.Type = olOptional
End if
Mike says
If you're still reading this thread, I tried a lot tonight. I have a shared calendar called TO Calendar. The way my name appears is FIrst Name Last Name, but in the code below I tried the part of my email before the @ sign (Ive been trying everything). No matter what it puts the meeting on my personal calendar, and not the shared. What am I doing wrong?? Thank you. This is most of the code. It was too long,but the rest is identical to above
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
Dim calFolder As Outlook.Folder
Dim NS As Outlook.NameSpace
Dim objOwner As Outlook.Recipient
Set NS = Application.GetNamespace("MAPI")
Set objOwner = NS.CreateRecipient("mtamoush")
objOwner.Resolve
If objOwner.Resolved Then
'MsgBox objOwner.Name
Set calFolder = NS.GetSharedDefaultFolder(objOwner, olFolderCalendar)
End If
'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 objAppt = calFolder.Items.Add(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)
' 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"
Diane Poremsky says
The problem is these lines:
Set objAppt = Application.CreateItem(olAppointmentItem)
' Set objAppt = calFolder.Items.Add(olAppointmentItem)
move the ' to the first one -
' Set objAppt = Application.CreateItem(olAppointmentItem)
Set objAppt = calFolder.Items.Add(olAppointmentItem)
to use the shared calendar.
Mike says
Can you explain again how I would have this send from a shared calendar?
Diane Poremsky says
These lines set the calendar to use - the first one uses the default calendar, the second uses the calendar identific by calFolder and is commented out. Delete or comment out the first line, remove the ' from the second one.
Set objAppt = Application.CreateItem(olAppointmentItem)
' Set objAppt = calFolder.Items.Add(olAppointmentItem)
Mike says
Are you still answering questions on this? I dont knwo how old this thread is.
Diane Poremsky says
We answer on any thread that is still open for comments, although sometimes I am so swamped that it takes a few days (or months) before i get them answered. (a few articles were being hit up constantly by spammers and had to be locked.)
John R says
Hi Diane.
This is my first time working with a script (in any program). Thank you!
I followed your steps, and copy and pasted exactly what you created.
Somehow my recipients each received 4 instances of the calendar invite on their calendars so I think I goofed somehow.
I never use "optional attendee" or "resource" in Outlook.
I had 8 lines on my csv file with no more than 1 recipient under "email" (column 3) on each line (with 2 different recipients in all for that column). (Each recipient being invited to 4 different dates/meetings)
I didn't know what to put for optional resource so I entered my secretaries email, and I never use "resource" with Outlook so I didn't know what to put - I maybe misunderstood something lower in this thread and I thus put my own email/name of my personal outlook calendar (so my own email address).
Is that where I goofed and what would have caused each recipient (and my sectary and myself) to each see 4 instances of the invite on our calendars?
Otherwise, it worked - each of the 2 recipients only received invites on the 4 correct days (but again 4 instances of them on EACH date on their calendar).
Also - if I don't want to use optional attendee or resource, can I just delete one or two particular lines in your code?
Help!
Thanks
John R
Diane Poremsky says
Delete these lines to not use optional and resources -
Set myOptional = objAppt.Recipients.Add(xlSht.Cells(iRow, 11))
myOptional.Type = olOptional
Set myResource = objAppt.Recipients.Add(xlSht.Cells(iRow, 12))
myResource.Type = olResource
Do you have 4 copies in your sent folder? it should only send 1 per each meeting unless you run it multiple times.
John R says
No, I only had 8 outgoing (4 for each recipient) emails in my sent folder.
Great, I deleted those script lines for the optional attendee and for the "resource" and it works now with no duplicates.
It may have convoluted things that my secretary I listed under "optional attendee" is also some kind of delegate for me for meeting invitations in Exchange. That may be why I (and the recipients) received the duplicates?? (I'm sure it doesn't help that I don't know the Outlook fields optional attendee and "resource" well).
I forgot to ask you about the fact that for some reason the subject line gets the time and day added on like a time stamp. So each invite had for example Board Mtng Update 1/4/2016 10:04:30 PM - this was for a meeting invite on 1/27 which landed correctly on 1/27 (but that I sent last night). Any ideas on why the date and time of sending are added to the subject line on each invitation?
Diane Poremsky says
No, adding yourself and your secretary shouldn't have caused duplicates, except to her as a delegate - but not 4 copies.
There is an issue with iphones where accepting meetings from the phone can cause them to be resent multiple times.
John R says
Ok - the duplication of 4 instances on my own Outlook Calendar was immediate - so I suspect it was immediate for the recipients also (and no iphones involved) - Maybe if the issue comes up again for anyone else, it will be clearer or resolved later. For now, my deletion of the optional attendee and the resource lines you said to delete seems to have me all set. Thank you again!
John R says
oops - sorry - I forgot that you didn't respond to my "date/time" stamp question. Ideas?
Diane Poremsky says
it's in the code - .Subject = xlSht.Cells(iRow, 1) & Now() - not sure why, maybe the person who asked for the macro wanted that added. :) Remove "& Now()" and it'll use just the subject from the spreadsheet.
Sankalp Das says
Hi Diane,
I am using your valuable macro to send multiple invites and it's working fine.
Is it possible to send multiple appointments in one row/email to participants?
Thanks,
Sankalp Das
Diane Poremsky says
As in several attached to a message? You'd need to create appointments, save them use attachments.add to add them to the message.
Sankalp Das says
I meant sending multiple outlook invites in one email to one person.
For example if a person has appointment with exercise trainer on Monday, Tuesday and Thursday in 1st week of Jan then I would like to send all three apoointments in one email.
Regards and Thanks,
Sankalp Das
Diane Poremsky says
A recurring appointment? It is possible, but I don't have any code samples handy that do it.
Sankalp Das says
Ok, Thanks for your help.
Ian S says
Diane,
I really appreciate that you've shared this macro. I got the macro to work with my personal calendar. I've tried adding the code you have below in a couple of places and in your other post to put them on a shared calendar but am getting an error. I suspect I'm not following the new code and may need to change something but I don't know what. Can you provide any guidance? My version of your code is below.
Thanks!
Sub ImportEventInvitees()
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
Dim calFolder As Outlook.Folder
Dim NS As Outlook.NameSpace
Dim objOwner As Outlook.Recipient
Set NS = Application.GetNamespace("MAPI")
Set objOwner = NS.CreateRecipient("Test 2016 Events Master Calendar ")
objOwner.Resolve
If objOwner.Resolved Then
'MsgBox objOwner.Name
Set calFolder = NS.GetSharedDefaultFolder(objOwner, olFolderCalendar)
End If
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 = calFolder.Items.Add(olAppointmentItem)
Set myAttendee = objAppt.Recipients.Add(xlSht.Cells(iRow, 3))
myAttendee.Type = olRequired
With objAppt
.Subject = xlSht.Cells(iRow, 1) & Now()
.Location = xlSht.Cells(iRow, 2)
.Categories = xlSht.Cells(iRow, 4)
.Start = xlSht.Cells(iRow, 5)
.End = xlSht.Cells(iRow, 6)
.AllDayEvent = xlSht.Cells(iRow, 7)
.Body = xlSht.Cells(iRow, 10)
.MeetingStatus = olMeeting
For Each myAttendee In .Recipients
myAttendee.Resolve
Next
.Save
.Display
End With
iRow = iRow + 1
Wend
xlWkb.Close
xlApp.Quit
Set xlWkb = Nothing
Set xlApp = Nothing
End Sub
Diane Poremsky says
Is this the name of the mailbox? Test 2016 Events Master Calendar
Ian S says
That's the calendar name. It's in this location: \Mailbox - LastName,FirstNameCalendar. Thanks for your help!
Diane Poremsky says
If the calendar is at Mailbox - LastName,FirstName then the objOwner is the mailbox name: LastName,FirstName (or you can use the alias instead of the mailbox display name.)
Ian S says
By the way, I didn't get email notification of your reply. I checked spam etc. Not sure if it's your site or something on my end. Just thought you should know in case others are having the same issue.
Diane Poremsky says
Thanks for letting me know. I'm not sure where the problem is, but it looks like it might be on my end. now to figure out what is wrong. :(
Ian S says
Diane,
I've been playing with this over the past 10 days and haven't had any luck. I think I understand that I'm using the wrong approach to call the calendar - it seems that I need to refer to it as a part of the hierarchy of my own account since it's not a public folder or separate PST file.
I really appreciate your guidance here. I looked at the other post about determining the folder path but am unsure of where to put that code. Is it a separate macro or should it replace some of the code I already have?
I hope you had a happy, restful Thanksgiving.
Ian
Diane Poremsky says
The code you posted earlier should work - but you need to correctly call the account by entering the mailbox's name or alias as the objOwner - if the mailbox is John Smith's and the GAL uses last, first format, you can either use john.smith or Smith, John in CreateRecipient. If it's a resource mailbox named Events Master (events), you'd use Events Master or events.
Set objOwner = NS.CreateRecipient("display name or alias")
Van Martin says
Diane,
New to this forum thing. Forgive me for not starting a new string. Something that seems simple but not working. I am trying to export to a calendar in Outlook. I have a data set that includes
Subject Start Date Start Time End Date End Time Reminder on/off Reminder Date Reminder Time Meeting Organizer Required Attendees
4/27/2015 8:30:00 AM 4/27/2015 8:40:00 AM TRUE 4/27/2015 5 "Last Name, First Name" firstname.lastname@XXXX.com
When I import into Outlook everything seems to come over to the calendar event except the Meeting Organizer and the Required Attendees. Is there a proper way to write in those two cells without having to do Macro or get into the VBA programming. I have tried email format, removing hyper link, adding double quotes, adding single quotes.
Thanks
VRM
Sankalp Das says
Yes, it worked!! Thanks a ton!
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
Sankalp Das says
Just as a shared calendar.
Diane 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))
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 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?
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 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))
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 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 https://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.
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!
Diane Poremsky says
To check for duplicates, you'd need to do a lookup before adding each appt, -a code snippet is here - https://www.slipstick.com/developer/create-appointments-spreadsheet-data/#comment-181794 or if you are only importing from the spreadsheet, you can add a field for 'already imported' and check that field before importing, and set it as you imported. I have a code snippet for that somewhere too, but I have no idea where it is.
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.
Diane Poremsky says
I have a macro that does just that - https://www.slipstick.com/developer/create-appointments-spreadsheet-data/ it works with subfolders and would need to be tweaked if the calendars are not subfolders.
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 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
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
Graham Brown says
I had this problem, I had to comment out optional and resource in order to get it to work.
Diane Poremsky says
Thanks for the update!
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?
Diane Poremsky says
Can you send me a copy of your book to test against? use diane@slipstick.com
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.)
Diane Poremsky says
You need properly formatted addresses - alias@domain.com. They need to be in the correct columns - K & L. Multiple addresses should use semicolon separators.
Ç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.
Tomáš Janata says
Hello Diane,
while trying to run your macro code, I unfortunatelly get the same error (there must be at least one name or distribution list in the To,CC, or BCC box). I think it has something to do with empty cells in columns 3, 11, 12 (no Required/Optional Attendees, Resource). Because if all fields are filled, macro stops a little bit later at following line:
.Start = xlSht.Cells(iRow, 5) + xlSht.Cells(iRow, 7)
-> Run-time error ‘13’: Type mismatch(Before: Set myAttendee = objAppt.Recipients.Add(xlSht.Cells(iRow, 3))
I am using Outlook 365.
I would like not to solve it just by deleting of Optional Attendees/Resource from the macro code because the fields could be filled next time.
Thank you in advance.
Diane Poremsky says
You need to use an If statement - there are different conditions you could check to see if there is a value in the call - null, "", isempty- I think Len should cover all (but did not test it.) On Error Resume next should also work, but it's better to avoid the error.
if len(xlSht.Cells(iRow, 3)) > 1 then ' use 1 incase someone adds a space to the cell
Set myAttendee = objAppt.Recipients.Add(xlSht.Cells(iRow, 3))
end if
if the field will either have a smtp address or nothing, this would work. (if you use names and let outlook resolve them, it won't.)
if instr(1,xlSht.Cells(iRow, 3), "@") > 1 then
Set myAttendee = objAppt.Recipients.Add(xlSht.Cells(iRow, 3))
end if
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.
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.
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
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.
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.
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.