Hi. I have a worksheet measuring staff attendance on various courses. Using date-today, a 'days lapsed' value is calculated in column D. If the value of the cell in column D exceeds 365 days, an email is generated to that member of staff using the following code in a module;
Sub TestFile()
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 _
Cells(cell.Row, "D").Value > 365 Then
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "Fire Course Reminder"
.Body = "Dear " & Cells(cell.Row, "A").Value _
& vbNewLine & vbNewLine & _
"course update is due. Please contact E-learning to update this course"
.Display
End With
On Error GoTo 0
Set OutMail = Nothing
End If
Next cell
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub
This works fine on a single column, but if I wanted to add another set of columns e.g. if I had columns F, I and J set to measure different lapses e.g.100, 200 or 300 days respectively, could I incorporate this code into the same module? I have tried using a new module for each column, but come up with
Ambiguous name detected; Testfile
warnings. Any info gratefully received,
Mattbro
Bookmarks