Excel VBA with outlook

[MRusty]

Help needed,

My team and I use a shared excel workbook for scheduling crews and also for overtime work. We currently use a sheet that sorts by overtime hours for calling in employees with the highest amount of overtime hours. I have a sheet that is used enter the employees overtime hours from the previous week that gets sorted and listed on another sheet from highest to lowest hours. I have assigned a macro to do this via a button. When we use this list to call employees for overtime needed to work we print a sheet for the week and call employees, BUT we also use outlook calendar to schedule when an employee is going to be "Off" and is colored either red or yellow, depending on if it is pre-approved or not.

When we print off this sheet we have to manual look at outlook and see who will be off for the week. Is there a code that I can add to this macro to look at our calendar and see when a certain employee is off and fill in a cell with "Not Available" ? Here is our current macro.

*** we hide columns that have employees contact info for a sheet that we post on our board for the employees to see their hours.

Sub Emergency_Response_Summary()
'
' Emergency_Response_Summary Macro
'

'
Range("A3:BH30").Select
ActiveWorkbook.Worksheets("Weekly Input").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Weekly Input").Sort.SortFields.Add2 Key:=Range( _
"F3:F30"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Weekly Input").Sort.SortFields.Add2 Key:=Range( _
"A3:A30"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Weekly Input").Sort
.SetRange Range("A3:BH30")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A33:BH35").Select
ActiveWorkbook.Worksheets("Weekly Input").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Weekly Input").Sort.SortFields.Add2 Key:=Range( _
"F33:F35"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Weekly Input").Sort.SortFields.Add2 Key:=Range( _
"A33:A35"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Weekly Input").Sort
.SetRange Range("A33:BH35")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Sheets("Emergency Response").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=False, _
IgnorePrintAreas:=False
Range("M1:M35").Select
Selection.EntireColumn.Hidden = True
Range("L1:L35").Select
Selection.EntireColumn.Hidden = True
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=False, _
IgnorePrintAreas:=False
Selection.EntireColumn.Hidden = False
Range("M1:M35").Select
Selection.EntireColumn.Hidden = False
Range("L1:L35").Select
End Sub

 

[MRusty]

Help needed,

My team and I use a shared excel workbook for scheduling crews and also for overtime work. We currently use a sheet that sorts by overtime hours for calling in employees with the highest amount of overtime hours. I have a sheet that is used enter the employees overtime hours from the previous week that gets sorted and listed on another sheet from highest to lowest hours. I have assigned a macro to do this via a button. When we use this list to call employees for overtime needed to work we print a sheet for the week and call employees, BUT we also use outlook calendar to schedule when an employee is going to be "Off" and is colored either red or yellow, depending on if it is pre-approved or not.

When we print off this sheet we have to manual look at outlook and see who will be off for the week. Is there a code that I can add to this macro to look at our calendar and see when a certain employee is off and fill in a cell with "Not Available" ? Here is our current macro.

*** we hide columns that have employees contact info for a sheet that we post on our board for the employees to see their hours.

Sub Emergency_Response_Summary()
'
' Emergency_Response_Summary Macro
'

'
Range("A3:BH30").Select
ActiveWorkbook.Worksheets("Weekly Input").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Weekly Input").Sort.SortFields.Add2 Key:=Range( _
"F3:F30"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Weekly Input").Sort.SortFields.Add2 Key:=Range( _
"A3:A30"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Weekly Input").Sort
.SetRange Range("A3:BH30")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A33:BH35").Select
ActiveWorkbook.Worksheets("Weekly Input").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Weekly Input").Sort.SortFields.Add2 Key:=Range( _
"F33:F35"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Weekly Input").Sort.SortFields.Add2 Key:=Range( _
"A33:A35"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Weekly Input").Sort
.SetRange Range("A33:BH35")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Sheets("Emergency Response").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=False, _
IgnorePrintAreas:=False
Range("M1:M35").Select
Selection.EntireColumn.Hidden = True
Range("L1:L35").Select
Selection.EntireColumn.Hidden = True
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=False, _
IgnorePrintAreas:=False
Selection.EntireColumn.Hidden = False
Range("M1:M35").Select
Selection.EntireColumn.Hidden = False
Range("L1:L35").Select
End Sub

I should also note that the list of employees changes weekly in regards to who is where and in what order...