appointments in Outlook from Excel

**each** · 02-22-2007, 01:51 PM

Most important bit first
I picked up the following code at the bottom of this post (and changed it a bit) from another request for help which had been kindly answered by VBA Noob (Thank you VBA). I have got this code working but am struggling to adapt it to my needs - what I would like it to do is pick up a whole list of appointments and put them into outlook for me. Ideally from a named range on an excel spreadsheet?

I Would also like to know this but less important than above
Can anyone also tell me where I can find a list of all the different (sorry dont know terminology) dot commands which work with outlook eg .start or .duration .reminderminutesbeforestart? I am particularly looking for the command that is an all day appointment flag (rather than a set time).

Can someone also tell me whether it is possible to split the .start command into date and time from separate cells in the spreadsheet?

I know this is fairly basic but would appreciate some help - sadly I'm just a cut and paster rather than a programmer so have really apprciated all the help out there over the last year.

Thanks

EACH

 
Sub MkeAppt() 

Dim ol As Outlook.Application 
Dim ai As AppointmentItem 

Set ol = New Outlook.Application 
Set ai = ol.CreateItem(olAppointmentItem) 

With ai 
.Subject = "Remind me" 
.Start = Range("A1").Value  
.Duration = Range("b1").value
.ReminderMinutesBeforeStart = range("c1").value 
.BusyStatus = olBusy 
.Save 
End With 


Set ai = Nothing 
Set ol = Nothing 


End Sub

**Carim** · 02-22-2007, 01:59 PM

Hi,
To create a named range:
Select the cells that contain the information you want to import into
Outlook.
Click in the name box (the white box to the left of the formula bar).
Type a one-word name for the selected cells, e.g. MyAppointments.
Press Enter.
From Outlook, you can now import your named range ...

**each** · 02-22-2007, 02:21 PM

Thanks for the help carim but not quite what I am looking for.

I have a spreadsheet listing procedures and policies that need reviewing at regular intervals. The spreadsheet contains list of dates of when the policies need to be reviewed (more complex than it sounds and subject to frequent additions and updates plus dates are dependent on other criteria)

I have got as far as having some code that will detect when a date changes on the spreadsheet which then runs the apointmenter code (as in my original posting) and will place the entry in an outlook calendar automatically - this works brilliantly well and I am really pleased with it (it avoids the need to remember to import into outlook each time a revision is made which may be several times a day.)

However with my very limited coding skills I cannot adapt the code I have borrowed from others to work with a list of appointments rather than a single appointment - it was this that I was hoping for some help on?