Creating Appointment
in [General]
|
Prev: unable to cast ..Excel.ApplicationClass to ..Excel._Application
Next: very urgent : to write the value of next column of the same row
From: Jacob Skaria on 27 Apr 2010 07:24 Cell B1 of activesheet contains a valid date in excel date format. -- Jacob (MVP - Excel) "Dan Wood" wrote: > There is no error this time but i cannot find them in the calendar. Is it > anything to do with formatiing the date cell? > Sorry to be a pain!
From: Dan Wood on 27 Apr 2010 08:21 How stupid can i be!! The date wasn't formatted correctly. Thank you for that. Next question is:- How can i set this macro to perform the same actions for multiple cells? So if coloum A had a list of system names, and coloum B has a list of various dates, how can the macro scroll down and create the appointments?
From: Jacob Skaria on 27 Apr 2010 08:28 Dan, try the below......... Sub OLApp() Dim objOL As Object, objApp As Object, lngRow As Long Set objOL = CreateObject("Outlook.Application") For lngRow = 1 To Cells(Rows.Count, "A").End(xlUp).Row Set objApp = objOL.CreateItem(1) With objApp .Subject = "Change Password for system" & Range("A" & lngRow) .Start = Range("B" & lngRow) .ReminderPlaySound = True .Save End With Next Set objOL = Nothing End Sub -- Jacob (MVP - Excel) "Dan Wood" wrote: > How stupid can i be!! The date wasn't formatted correctly. > > Thank you for that. Next question is:- > > How can i set this macro to perform the same actions for multiple cells? So > if coloum A had a list of system names, and coloum B has a list of various > dates, how can the macro scroll down and create the appointments?
From: Dan Wood on 27 Apr 2010 09:59 That works perfectly. Thanks Is there a way to stop this creating duplicate appointments? Prehaps a seperate macro to either check if the appointment is there, or prehaps a new field to say the appointment has been added, and the macro to skip it, eg if field c1 says 'Done' move onto the next field
From: Jacob Skaria on 27 Apr 2010 10:13
Modified... Sub OLApp() Dim objOL As Object, objApp As Object, lngRow As Long Set objOL = CreateObject("Outlook.Application") For lngRow = 1 To Cells(Rows.Count, "A").End(xlUp).Row If Range("C" & lngRow) = "" Then Set objApp = objOL.CreateItem(1) With objApp .Subject = "Change Password for system" & Range("A" & lngRow) .Start = Range("B" & lngRow) .ReminderPlaySound = True .Save End With Range("C" & lngRow) = "Done" End If Next Set objOL = Nothing End Sub -- Jacob (MVP - Excel) "Dan Wood" wrote: > That works perfectly. Thanks > > Is there a way to stop this creating duplicate appointments? Prehaps a > seperate macro to either check if the appointment is there, or prehaps a new > field to say the appointment has been added, and the macro to skip it, eg if > field c1 says 'Done' move onto the next field |