Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 600
  • Last Modified:

how to export excel data into outlook calendar

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
OntarioMedicalAssociatin
Asked:
OntarioMedicalAssociatin
  • 8
  • 5
  • 3
  • +1
1 Solution
 
gbanikCommented:
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
 
gbanikCommented:
One crutial statement is left out. Once the Appointment Object is filled, save it
olAppointment.save
0
 
gbanikCommented:
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
OntarioMedicalAssociatinAuthor Commented:
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
 
TommySzalapskiCommented:
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
 
OntarioMedicalAssociatinAuthor Commented:
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
 
TommySzalapskiCommented:
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
 
gbanikCommented:
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
 
gbanikCommented:
Did it work for u?
0
 
OntarioMedicalAssociatinAuthor Commented:
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
 
TommySzalapskiCommented:
I'll try to answer for him. Should probably be
Set oRange = Sheets("Sheet1").Range("A1").CurrentRegion
0
 
gbanikCommented:
OMA, on which line of code are you getting the error?
0
 
TommySzalapskiCommented:
It's line 10 where it calls Application.Range
0
 
TommySzalapskiCommented:
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
 
gbanikCommented:
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
 
gbanikCommented:
@Tommy ;)
0
 
TracyVBA DeveloperCommented:
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

Industry Leaders: 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!

  • 8
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now