I was wanting to create appointments in outlook based on cell's that have a yellow background. Next to each of these cells is a time that these appointments have to be created. I currently have this setup, but the specific cells are hard coded into the macro. This is a very tedious process to setup if i am editing the document and have to move cells around etc..
Is there anyway to create a macro that will look at the activesheet and create the appointments based off the yellow cells and then set the time value that is exactly left of the cell?
here is what i am currently doing...
Sub FE_WEDNESDAY() Dim objOL As Object Dim objItem As Object Dim tzCentral As Object Set objOL = GetObject(, "Outlook.Application") Set tzCentral = objOL.TimeZones.Item("Central Standard Time") Set objItem = objOL.CreateItem(1) intresponse = MsgBox("Do you want to setup " & ActiveSheet.Range("B6") & " reminders?", vbYesNo, "Custom Reminder") If intresponse = vbYes Then '7AM Set objItem = objOL.CreateItem(1) With objItem .Start = (Date) + ActiveSheet.Range("A8").Value .StartTimeZone = tzCentral .Duration = 0 .BusyStatus = olFree .AllDayEvent = False .Subject = ActiveSheet.Range("B8").Value .ReminderMinutesBeforeStart = 10 .ReminderSet = True .Save End With MsgBox "Your strategy reminders have been created for " & Sheets("Admin Menu").Range("H4"), vbOKOnly, "Strategy Reminder" ElseIf intresponse = vbNo Then Exit Sub Set objItem = Nothing Set objOL = Nothing Set tzEastern = Nothing End If End Sub
If i need to elaborate or say further detail, i am more than happy too..
Thank you for the help!