Hi all,
I have a multisheet workbook that is used as timesheets (with each sheet being a different employee). What I would like is for whenever an employee has not completed their time sheet from the day previously, an email reminder to be sent to them. Now, I am a novice when it comes to this stuff, however I have got the code to this stage and linked it to a button:
Sub Test1()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
On Error GoTo cleanup
For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And _
LCase(Cells(cell.Row, "C").Value) = "send" _
And LCase(Cells(cell.Row, "D").Value) <> "Sent" Then
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "Time Sheet"
.Body = "Dear " & Cells(cell.Row, "A").Value _
& vbNewLine & vbNewLine & _
"Please update your timesheet"
.send
End With
On Error GoTo 0
Cells(cell.Row, "D").Value = "Sent"
Set OutMail = Nothing
End If
Next cell
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub
The issue I have is that it only searches that one worksheet, where ideally I would like the one button to run a loop on all of the worksheets (except the first, ideally will be where the button is placed). All of the fields are the same in each worksheet. Column A is the employees name, column B is the email address, column C is an IF function that checks whether the email should be sent, and column D is the field that will state SENT after the email has been sent to repeat repetitive emails being distributed unnecessarily.
Any help would be greatly appreciated, especially if you could give me some sort of an explanation of what you have done.
Cheers.
I have a multisheet workbook that is used as timesheets (with each sheet being a different employee). What I would like is for whenever an employee has not completed their time sheet from the day previously, an email reminder to be sent to them. Now, I am a novice when it comes to this stuff, however I have got the code to this stage and linked it to a button:
Sub Test1()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
On Error GoTo cleanup
For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And _
LCase(Cells(cell.Row, "C").Value) = "send" _
And LCase(Cells(cell.Row, "D").Value) <> "Sent" Then
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "Time Sheet"
.Body = "Dear " & Cells(cell.Row, "A").Value _
& vbNewLine & vbNewLine & _
"Please update your timesheet"
.send
End With
On Error GoTo 0
Cells(cell.Row, "D").Value = "Sent"
Set OutMail = Nothing
End If
Next cell
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub
The issue I have is that it only searches that one worksheet, where ideally I would like the one button to run a loop on all of the worksheets (except the first, ideally will be where the button is placed). All of the fields are the same in each worksheet. Column A is the employees name, column B is the email address, column C is an IF function that checks whether the email should be sent, and column D is the field that will state SENT after the email has been sent to repeat repetitive emails being distributed unnecessarily.
Any help would be greatly appreciated, especially if you could give me some sort of an explanation of what you have done.
Cheers.