Improve company productivity with a Business Account.Sign Up

x
?
Solved

how to export excel data into outlook calendar

Posted on 2010-11-26
18
Medium Priority
?
612 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
17 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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 

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 2000 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:Tracy
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Usually, rounding is performed by some power of 10 - to thousands, hundreds, tens, or integer - or to one, two, or more decimals. But rounding can also be done to a power of two, say, 16 or 64, or 1/32 or 1/1024, even for extreme values.
Importing Outlook PST contacts to Exchange Server can become a complicated task. Situations arise where an Exchange user is not able to import contacts from PST to Exchange Mailboxes in an efficient manner. Try SysTools Exchange Import to move conta…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

585 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