while loop to check value condition and send email

[razhmo7al]

Hi all,
i have been working on this project for week and couldn't find a way and i hope you can help me please.

i have excel sheet where has equipment with number of running days and if the cell reach certain value, it should send email to the responsible employee. i have about 25 equipments and the while loop has to check every row and check the running days if is equal or less than 60 days, it should send email automatically.

My problem is how to set the while loop to enter every cell and check the condition

i hope it's clear to understand and i have to submit this in 2 days.

 

[razhmo7al]

Thanks Kamolga. but how can i add it in this line
If (cell.Value < 60 And cell.Value <> "") Then

 

[Kamolga]

Sub Email()'!!!In VBA go in Tools menu, references... and tick Microsoft Outlook (16.0) object library
' to be abble to send emails from excel


'Declare Outlook
Dim outlookApp As Outlook.Application
Dim myMail As Outlook.MailItem
Set outlookApp = New Outlook.Application


'Prepare the body of the mail
Dim bdystr As String
'Loop through the cells
For Each cell In Range("c2:C16")
If (cell.Value <= 60 And cell.Value > 57) Then
Set myMail = outlookApp.CreateItem(olMailItem)
'Begining of message
bdystr = "Hi," & vbNewLine & vbNewLine & "Those are the equipments that need your attention:" & vbNewLine
' Put a line with equiment A: x days left
bdystr = bdystr & vbNewLine & cell.Offset(0, -1).Value & " : " & cell.Value & " running days"
'Add something at the end of the message
bdystr = bdystr & vbNewLine & vbNewLine & "Thank you in advance!" & vbNewLine & "Best Regards,"
With myMail
.To = cell.Offset(0, 1).Value
.Subject = "Check out those equipments!"
.body = bdystr
.display 'Replace .display by .send to send without seing it
End With
End If
Next cell
End Sub

 

[razhmo7al]

Thanks much for usual help. Currently, this code will send emails every day if the value between 60 and 57. I need the email to be sent only one time. Is there any line of code to do this.

 

[razhmo7al]

the above code will send reminder emails to employees to do a job, if they did it, still they will receive emails to remind them on. how can i apply a code (if the email sent) don't send email again?

 

[razhmo7al]

any one help me please.