Import Excel file to Outlook Calendar

[willhh3]

Hi,

I glanced through the threads and didn't see this covered. So apologies if I missed it.

I'm looking to take data from an Excel spreadsheet (xls or csv) and import it into individual appointments on an Outlook calendar. The fields my file has need to be mapped to the Outlook fields of Subject, Start_Date, Start_Time, End_Date, End_Time, Categories, and Description. I'd like to accomplish this with a macro and I'm not sure of the code I need to do that. Not sure if I'd also have to identify the actual calendar name or not. If so, for the sake of testing I'll call it "Test Calendar." Attaching the sample .xls file, however the file could be csv; doesn't really matter because I can gather my data in either format. .xls would probably save a step, though. I'm using Outlook and Excel 2013.

Thanks in advance for the help with this Outlook macro. Let me know if there are questions, or something isn't clear.

Whh3

[gmayor]

Yes you would need to identify the calendar. The following Excel macro will work with your data and a calendar called 'Test Calendar'


Option Explicit
Sub AddAppointments()
Dim olApp As Object
Dim olNs As Object
Dim olStore As Object
Dim olCal As Object
Dim objAppt As Object
Dim lastrow As Long
Dim xlSheet As Worksheet
Dim i As Long
Dim strStart As String
Dim strEnd As String
Const strCalendar As String = "Test Calendar"

On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
If olApp Is Nothing Then Set olApp = CreateObject("Outlook.Application")
On Error GoTo 0

If Not olApp Is Nothing Then
Set olNs = olApp.GetNamespace("MAPI")
olNs.logon
For Each olStore In olNs.Folders
For Each olCal In olStore.Folders
If olCal.Name = strCalendar Then
Set xlSheet = Sheets(1)
With xlSheet
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To lastrow
strStart = CDate(xlSheet.Range("B" & i)) & Chr(32) & CDate(xlSheet.Range("C" & i))
strEnd = CDate(xlSheet.Range("D" & i) & Chr(32) & CDate(xlSheet.Range("E" & i)))
Set objAppt = olCal.Items.Add(1)
With objAppt
.Subject = xlSheet.Range("A" & i)
.Start = strStart
.End = strEnd
.Body = xlSheet.Range("G" & i)
.Categories = xlSheet.Range("F" & i)
.ReminderSet = True
.AllDayEvent = False
.BusyStatus = 1
.Save
End With
Next i
End With
Exit For
Exit For
End If
Next olCal
Next olStore
End If
lbl_Exit:
Set olApp = Nothing
Set olNs = Nothing
Set olStore = Nothing
Set olCal = Nothing
Set objAppt = Nothing
Set xlSheet = Nothing
Exit Sub
End Sub

[willhh3]

Wow, thank you GM! Needless to say this is perfect and in my first tests it works well.

I've tested it when logged into my normal Outlook account. However, where I really want to use this is on a team mailbox/calendar. The team account has it's own id/pw so to my thinking operates the same as my personal account. I log into the team mailbox account, go to the "Test Calendar" and then I open Excel. In Excel, I open the workbook with the data (the same one I attached), and execute the macro....nothing happens. I don't get an error or any indication that something is wrong. Again, it seems to work fine in my account. Any ideas? Could it be a setting or something?

P.S. I should add that manually importing it as a .csv file works fine in team mailbox.

[skatonni]

To debug, mouse-click anywhere in the code. Press F8 repeatedly to step through the code. http://www.cpearson.com/excel/DebuggingVBA.aspx

[willhh3]

Thanks Skatonni!

I'd did step through the code, but it isn't an issue with the code (I think). It work perfectly in my normal, day-to-day mailbox. However, when I log out and then into the team mailbox the macro doesn't create the calendar items. Stepping through it runs through everything, doesn't throw any error, but nothing is populated on the calendar. I can manually import the .xls data w/no issue and I can run the macro in my account with no issue. This has me stumped. Maybe something in the team account is blocking the marco, but I'd expect a message of some type.

[gmayor]

Locate the two lines
For Each olStore In olNs.Folders
For Each olCal In olStore.Folders

and add a couple of message boxes
For Each olStore In olNs.Folders
MsgBox olStore.Name
For Each olCal In olStore.Folders
MsgBox oCal.name

This should indicate where the macro is looking.

[willhh3]

Thanks GM. I think I might be okay. I was just doing some more testing and got it to work. So in my team mailbox the main calendar is called "Calendar." I created a "Test Calendar" to run the macro against and this is where it wasn't working. When I changed the macro to point to "Calendar" it worked perfectly. Not sure why it wasn't finding the "Test Calendar" (it did in my personal mailbox), but since I utlimately want it to go to the Team mailbox, and the "Calendar" location, I'm not going to worry about why "Test Calendar" didn't work. Hope that makes sense.

Marking the thread as Solved! And many thanks!!!!