Solved

how to export excel data into outlook calendar

Posted on 2010-11-26
18
570 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
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

832 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