Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel open in outlook if outlook is running | Excel Discussion (Misc queries) | |||
Outlook 11 Outlook 10 Object Library Compatibility Issues | Excel Programming | |||
Send to Outlook 2000 not Outlook Express | Excel Discussion (Misc queries) | |||
Late Binding to Outlook from Excel: Outlook modifies email body | Excel Programming | |||
Display mail application either in Outlook, or Outlook express | Excel Programming |