Using Excel to generate an Outlook Calendar Event

[Michael]

Hello,

I was wondering if anyone knows how to generate an outlook calendar
event from Excel. I have an Excel timeline for opening a new
restaurant and there are certain things I need to do each day, and I
would like to automatically add those dates into my Outlook Calendar
somehow, then when I need to do a certain thing, it pops up on my
Outlook calendar.

Any ideas would be helpful, not sure if this can even be done,

Thanks for your time,

-Michael

 

[Steve Yandl]

Michael,

It can be done. Below is an example where I draw from sheet1 which was set
up with Start times in Column A and locations in Column B. I suspect you
will have other aspects of appointments you want recorded but this example
should get you started. Note that once you open the VBE window, you want to
select 'Tools > References' and put a check in the box for "Microsoft
Outlook 11.0 Object Library" (number may vary depending on your version of
Office)

Sub ScheduleAppts()
Dim ol As New Outlook.Application
Dim ns As Outlook.Namespace
Dim olFolder As Outlook.MAPIFolder
Dim appt As Outlook.AppointmentItem
Dim R As Integer
Dim X As Integer

R = Range("A65536").End(xlUp).Row

Set ns = ol.GetNamespace("MAPI")
Set olFolder = ns.GetDefaultFolder(olFolderCalendar)

For X = 1 To R
Set appt = olFolder.Items.Add
With appt
.Start = Sheets("Sheet1").Cells(X, 1).Value
.Location = Sheets("Sheet1").Cells(X, 2).Value
.Save
End With
Next X

Set ol = Nothing
Set ns = Nothing
Set appt = Nothing
End Sub

Steve Yandl

 

[kutuka]

hi
I added the last line in the file transfer is the only.
How to transfer all the rows.
best Regards
Kutuka