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.

 

[Kamolga]

Hi,

I put equipments in columnA and amount of days left in Column B.

With this macro,

Sub Email()
[COLOR=#008000]'!!!In VBA go in Tools menu, references... and tick Microsoft Outlook (16.0) object library
' to be abble to send emails from excel
[/COLOR]
[COLOR=#008000]
'Declare Outlook[/COLOR]
Dim outlookApp As Outlook.Application
Dim myMail As Outlook.MailItem
Set outlookApp = New Outlook.Application
Set myMail = outlookApp.CreateItem(olMailItem)

Dim i As Long
[COLOR=#008000]'i is amount of columns to the left from the column of amounts of days to have the equipment name, in my example column A[/COLOR]
i = 1
[COLOR=#008000]
'Prepare the body of the mail[/COLOR]
Dim bdystr As String
[COLOR=#008000]'Begining of message[/COLOR]
bdystr = "Hi," & vbNewLine & vbNewLine & "Those are the equipments that need your attention:" & vbNewLine
[COLOR=#008000]'Loop through the cells
'Adapt B2:B16 to range with amounts of days[/COLOR]
For Each cell In Range("B2:B16")
    If cell.Value < 60 Then
    [COLOR=#008000]    ' Put a line with equiment A: x days left[/COLOR]
        bdystr = bdystr & vbNewLine & cell.Offset(0, -i).Value & " : " & cell.Value & " days left"
    End If
Next cell

[COLOR=#008000]'Add something at the end of the message[/COLOR]
bdystr = bdystr & vbNewLine & vbNewLine & "Thank you in advance!" & vbNewLine & "Best Regards,"

With myMail
    .To = "employee@mail.com"
    .Subject = "Check out those equipments!"
    .body = bdystr
    .display 'Replace .display by .send to send without seing it
End With
End Sub

I have an automatic mail looking like this

[/URL][/IMG]

 

[razhmo7al]

Thanks Kamolga for your Help.

The emails should be sent seperated for every equipment to different email address.

 

[razhmo7al]

Hi,

I put equipments in columnA and amount of days left in Column B.

With this macro,

Sub Email()
[COLOR=#008000]'!!!In VBA go in Tools menu, references... and tick Microsoft Outlook (16.0) object library
' to be abble to send emails from excel
[/COLOR]
[COLOR=#008000]
'Declare Outlook[/COLOR]
Dim outlookApp As Outlook.Application
Dim myMail As Outlook.MailItem
Set outlookApp = New Outlook.Application
Set myMail = outlookApp.CreateItem(olMailItem)

Dim i As Long
[COLOR=#008000]'i is amount of columns to the left from the column of amounts of days to have the equipment name, in my example column A[/COLOR]
i = 1
[COLOR=#008000]
'Prepare the body of the mail[/COLOR]
Dim bdystr As String
[COLOR=#008000]'Begining of message[/COLOR]
bdystr = "Hi," & vbNewLine & vbNewLine & "Those are the equipments that need your attention:" & vbNewLine
[COLOR=#008000]'Loop through the cells
'Adapt B2:B16 to range with amounts of days[/COLOR]
For Each cell In Range("B2:B16")
    If cell.Value < 60 Then
    [COLOR=#008000]    ' Put a line with equiment A: x days left[/COLOR]
        bdystr = bdystr & vbNewLine & cell.Offset(0, -i).Value & " : " & cell.Value & " days left"
    End If
Next cell

[COLOR=#008000]'Add something at the end of the message[/COLOR]
bdystr = bdystr & vbNewLine & vbNewLine & "Thank you in advance!" & vbNewLine & "Best Regards,"

With myMail
    .To = "employee@mail.com"
    .Subject = "Check out those equipments!"
    .body = bdystr
    .display 'Replace .display by .send to send without seing it
End With
End Sub

I have an automatic mail looking like this

[/URL][/IMG]

i have attached image of excel.

the loop should enter the cells and check if the running days equel or less than 60 should send email to responsible employee in the same row.

 

[razhmo7al]

https://ibb.co/cPdk20

 

[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 <> "") 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]

The code is working. Thanks, gentlemen for help. Thanks a lot Kamolga

 

[Kamolga]

The code is working. Thanks, gentlemen for help. Thanks a lot Kamolga

You are welcome, thank you for the feedback

 

[razhmo7al]

Hi

Need your help again.
in the below code, how can i add between 2 values. Exampl: if the value <=60 and >57.

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 <> "") 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

 

[Kamolga]

[COLOR=#333333]If (cell.Value <= 60 And [/COLOR][COLOR=#333333]cell.Value>57[/COLOR][COLOR=#333333]) then [/COLOR]