Excel and Outlook
I adjusted the code to the following:
Sub ScheduleAppts2()
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, 4).Value
.Save
End With
Next X
Set ol = Nothing
Set ns = Nothing
Set appt = Nothing
End Sub
the times start at a11 and the locations start at d11. I am getting a
runtime 13 at the line " .Start = Sheets("Sheet1").Cells(X, 1).Value".
Thanks for any help.
Steve
"Steve Yandl" wrote:
The subroutine should work fine with Office 2000; you just needed to put a
check mark next to "Outlook 9.0 object library" and click OK.
The time values in column A need to be both a date and time of day for an
appointment to show up where you want. For example, "9:30am 1/5/2005".
After running the macro from Excel, you should be able to open Outlook, pull
up the calendar and check the appointments.
Steve
"Steve" wrote in message
...
Perhaps I should mention that I am working in Office 2000. I am not sure
if
that makes a difference. The only Outlook reference i could locate was
outlook 9.0 object library. When I run the macro it seems to run just
find.
Meaning, I get no errors. However, I cannot track down where the
appointments
went.
"Steve Yandl" wrote:
Suppose I have start times in Column A and locations for appointments in
Column B on Sheet 1. The following Macro will find all the times and
locations and set appointment accordingly. You need to set a reference
to
Outlook from Tools References. You might want "Subject", "Finish Time"
etc. this is just a start.
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
"Steve" wrote in message
...
Is there a way to link a row or cell of info in excel to an
appointment
pop-up in Outlook. For example, If I have a row of cells with a times
in
them. Could I get those time to automatically plug in to my outlook
calendar?
Thanks for your help,
Steve
|