Import Excel file to Outlook Calendar

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

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

Code:

---

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

---

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.

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

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.

Locate the two lines

Code:

---

For Each olStore In olNs.Folders
            For Each olCal In olStore.Folders

---

and add a couple of message boxes

Code:

---

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.

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!!!!