Consulting

Results 1 to 7 of 7

Thread: Import Excel file to Outlook Calendar

  1. #1
    VBAX Regular
    Joined
    May 2010
    Posts
    37
    Location

    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
    Attached Files Attached Files

  2. #2
    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
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  3. #3
    VBAX Regular
    Joined
    May 2010
    Posts
    37
    Location
    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.
    Last edited by willhh3; 07-29-2015 at 12:37 PM.

  4. #4
    VBAX Mentor skatonni's Avatar
    Joined
    Jun 2006
    Posts
    347
    Location
    To debug, mouse-click anywhere in the code. Press F8 repeatedly to step through the code. http://www.cpearson.com/excel/DebuggingVBA.aspx
    To debug, mouse-click anywhere in the code. Press F8 repeatedly to step through the code. http://www.cpearson.com/excel/DebuggingVBA.aspx

    If your problem has been solved in your thread, mark the thread "Solved" by going to the "Thread Tools" dropdown at the top of the thread. You might also consider rating the thread by going to the "Rate Thread" dropdown.

  5. #5
    VBAX Regular
    Joined
    May 2010
    Posts
    37
    Location
    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.

  6. #6
    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.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  7. #7
    VBAX Regular
    Joined
    May 2010
    Posts
    37
    Location
    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!!!!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •