Excel and Outlook
 

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

Excel and Outlook
 

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

Excel and Outlook
 

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

Excel and Outlook
 

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

Excel and Outlook
 

I forgot to set up the date part. Thanks Steve. I appreciate it.

"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

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

Excel and Outlook
 

If you're starting in the 11th row, you should substitute "10 + X" where you
now show just X. R is going to be the row number for the last row in column
A that has a value so that stays the same but you don't want the routine
trying to make appointments and looking to A1:A10 for date/time values that
are not present.

Steve



"Steve" wrote in message
...
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

Excel and Outlook
 

Correction on my last message (if it shows up).

Change "For X = 1 To R" to "For X = 11 to R"

Steve


"Steve" wrote in message
...
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