Solved

how to export excel data into outlook calendar

Posted on 2010-11-26
18
587 Views
Last Modified: 2012-05-11
Hi there,

I am working on a spreadsheet in excel that is for events. I would like to export the event info and dates into an outlook calendar. Can you please tell me how I would do that? I have some knowledge of macros but it is pretty basic.

Thanks
0
Comment
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 3
  • +1
18 Comments
 
LVL 13

Expert Comment

by:gbanik
ID: 34219195
Please add the Microsoft Outlook Reference before you run the code below.
Option Explicit

Public Sub CreateAppointments()
Dim olApp As Outlook.Application, iCtr As Integer, oRange As Range, olAppointment As AppointmentItem

Set olApp = CreateObject("Outlook.Application")
Set oRange = Application.Range("Sheet1!A1").CurrentRegion
    
    For iCtr = 1 To oRange.Rows.Count
        Set olAppointment = olApp.CreateItem(olAppointmentItem)
        With olAppointment
            .Subject = oRange.Rows.Cells(1).Value
            .Location = oRange.Rows.Cells(2).Value
            .Start = oRange.Rows.Cells(3).Value
            .End = oRange.Rows.Cells(4).Value
            .Body = oRange.Rows.Cells(5).Value
        End With
    Next
End Sub

Open in new window

0
 
LVL 13

Expert Comment

by:gbanik
ID: 34219203
One crutial statement is left out. Once the Appointment Object is filled, save it
olAppointment.save
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34219240
Sorry ... reposting the code... did not test it thoroughly earlier
Option Explicit

Public Sub CreateAppointments()
Dim olApp As Outlook.Application, objName As Namespace, iCtr As Integer, oRange As Range, olAppointment As AppointmentItem
Dim oFolder As Folder

Set olApp = CreateObject("Outlook.Application")
Set objName = olApp.GetNamespace("MAPI")
Set oFolder = objName.GetDefaultFolder(olFolderCalendar)
Set oRange = Application.Range("Sheet1!A1").CurrentRegion
    
    For iCtr = 1 To oRange.Rows.Count
        Set olAppointment = oFolder.Items.Add
        With olAppointment
            .Subject = oRange.Rows.Cells(1).Value
            .Location = oRange.Rows.Cells(2).Value
            .Start = oRange.Rows.Cells(3).Value
            .End = oRange.Rows.Cells(4).Value
            .Body = oRange.Rows.Cells(5).Value
            .Save
        End With
    Next
End Sub

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:OntarioMedicalAssociatin
ID: 34219394
I am getting an error message when I try to step through the code

"olApp As Outlook.Application" Compile error user defined type not defined
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34219412
Either set a reference to the Outlook interop or just do
Dim olApp
It will work just fine. When line 7 runs it will figure out the type, you don't really need to tell it ahead of time.
0
 

Author Comment

by:OntarioMedicalAssociatin
ID: 34219423
I know next to nothing about code so I've copied and pasted what is above into the VB and tried stepping into the marco. How do I go about fixing the error? is there something that I have to edit in the code above?
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34219452
This is the best way. In the VBA editor (the place you type code) Click tools->references. Scroll down until you find 'Microsoft Outlook 11.0 object library' (Or something like that, the number may be different, if there are more than one, pick the biggest)
Check the box next to it and hit 'okay' The code should work after that.
0
 
LVL 13

Accepted Solution

by:
gbanik earned 500 total points
ID: 34221641
Here is the new code WITHOUT any references.. Please add it to your project
Option Explicit

Public Sub CreateAppointments()
Dim olApp As Object, objName As Object, iCtr As Integer, oRange As Range, olAppointment As Object
Dim oFolder As Object

Set olApp = CreateObject("Outlook.Application")
Set objName = olApp.GetNamespace("MAPI")
Set oFolder = objName.GetDefaultFolder(9)
Set oRange = Application.Range("Sheet1!A1").CurrentRegion
    
    For iCtr = 2 To oRange.Rows.Count
        Set olAppointment = oFolder.Items.Add
        With olAppointment
            .Subject = oRange.Rows.Cells(1).Value 'Assign the subject column
            .Location = oRange.Rows.Cells(2).Value 'Assign the location column
            .Start = oRange.Rows.Cells(3).Value 'Assign the Start Date Time column
            .End = oRange.Rows.Cells(4).Value 'Assign the End Date Time column
            .Body = oRange.Rows.Cells(5).Value 'Assign the Body of the event
            .Save
        End With
    Next
End Sub

Open in new window

0
 
LVL 13

Expert Comment

by:gbanik
ID: 34225508
Did it work for u?
0
 

Author Comment

by:OntarioMedicalAssociatin
ID: 34233505
Sorry I haven't been able to work on this until now. I tried to run the macro and I got another error message "Method 'Range' of object'_Application'failed
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34233589
I'll try to answer for him. Should probably be
Set oRange = Sheets("Sheet1").Range("A1").CurrentRegion
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34233619
OMA, on which line of code are you getting the error?
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34233636
It's line 10 where it calls Application.Range
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34233649
Oh, I got it. The sheet isn't named "Sheet1" in OMA's workbook.
OMA, change Sheet1 to the name of your sheet where the data is stored.
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34233726
What is the name of your sheet? Is it "Sheet1"? If not... please replace the code with your sheet name

Set oRange = Application.Range("Sheet1!A1").CurrentRegion
to
Set oRange = Application.Range("YourSheetName!A1").CurrentRegion

If the sheet name contains spaces, change the code to
Set oRange = Application.Range("'Your Sheet Name'!A1").CurrentRegion
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34233738
@Tommy ;)
0
 
LVL 24

Expert Comment

by:broomee9
ID: 34428880
This question has been classified as abandoned and is being closed as part of the Cleanup Program.  See my comment at the end of the question for more details.
0

Featured Post

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to resolve IMCEAEX NDRs in Exchange or Exchange Online related to invalid X500 addresses.
Mailbox Overload?
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question