Copy Outlook Calendar to Excel.

[Simi]

I am curious if I can copy my entire Outlook calendar to Excel, so I can do some calculations.
Also, I would like to copy the calendars from other computers as well.

[Mike22]

Depending on your Outlook version you can 'Save As' text (You may need to Change the Calendar view to List before Outlook lets you 'Save As').

If this still does not meet your needs you may want to consider using a Office Addin like this one...
wincalendar.com/Outlook-Calendar-Import.htm

[Ken Puls]

Hi Simi,

Absolutely you can. I've attached a workbook that will show you it can be done.

When you try to modify this for your own use, you'll need to be aware of the following:

• The code is looking for the following, so they'll either need to exist in your workbook, or you'll need to modify the code to your enviroment:
  
  • Worksheet named "Calendar"
  • Table named "tblCalendar"
  • Range named "dtFrom"
  • Range named "dtTo"
  • Range named "mailbox"

To work with this, fill in the date you want to start, and your mailbox name as it appears in Outlook. (This may not be your email address. In my case my mailbox is Ken Puls, not kpuls@mydomain)

For working with others calendars, they must be shared and available to you first. Then you can just put in their mailbox name instead of yours and you should be good to go.

I've attempted to put this back to a late bind so that you don't have to set any references to code libraries. If you do run into any issues with missing references though, inside the VBE go to Tools-->References. Make sure you have a reference set to the Microsof Office xx.x Library. (Mine is 14.0 as I'm running Office 2010, but yours may be lower.)

[Ken Puls]

Oh, and I should mention too... the code in that file was adapated from my friend Jimmy Pena's article. (Thanks Jimmy!)

[Simi]

I was trying to use the sheet you supplied, to try and understand how it works.
However I keep getting an error on this line.

Set myCalItems = olNS.GetSharedDefaultFolder(objRecipient, 9).Items '9=olFolderCalendar

I am guessing it is an error on my part not putting the right name for my mailbox.
So to help me understand what my mailbox is called, is it the name of the .pst file?
Where can I check what the name of the mailbox is?

As a side note, I am using 2007.

Thank you for your help and I hope you have fun at the Microsoft MVP Summit.

[Ken Puls]

Hmm... this is weird... I'm actually able to use:

• kpuls@mydomain.com
• kpuls
• Ken Puls

So it looks like it's pretty robust here... Do any of those formats work for you or none?

[Ken Puls]

Here... this gives me a listing of the versions that work for me:

Sub GetMailboxNames()
    Dim olApp As Object
    Dim olNS As Object
    
    Set olApp = CreateObject("Outlook.Application")
    Set olNS = olApp.GetNamespace("MAPI")
    
    With olNS.Accounts.Item(1)
        Debug.Print "Username: " & vbTab & vbTab & .UserName
        Debug.Print "DisplayName: " & vbTab & .DisplayName
        Debug.Print "Name: " & vbTab & vbTab & vbTab & .CurrentUser.Name
    End With
End Sub

[Simi]

Well none of those formats work for me.
I should mention I am also using Google Apps, for my email.
So in the navigation pane of outlook it looks like. Google Apps - name@mydomain.com
I have also tried putting the name of my pst file: GMS-name@mydomain.com-01cb42d7-1e5fb217

When entering any value in the shared mailbox name, I get a run-time error'-214722133(8004010f)':
The operation failed. An object cannot be found.

[Ken Puls]

Oh... Sorry, I assumed you were connecting to an exchange server. Well need to look at this one later, I'm afraid.

[Zack Barresse]

You'll need to reference your inbox as a subfolder, as it goes Gmail -> Inbox, so either reference a folder of a folder, or reference two separate folder one of them being a subfolder. I'm not on a machine, so i can't give you sample code, but if you've got your Gmail account in Outlook, and I'm assuming its IMAP, you can code to it just fine. You can't, however, code to it on the web. Google may have an api for it, but I'm not familiar with it if they do.

Zack
Sent from my mobile using Tapatalk