Do Until IsEmpty loop check value condition and then send email

[aagarwal]

Hi all,

I have and excel sheet which has Environment name in column A and it should send email to the respective group until the cell value is empty.
Also in Column D, I have the expiry dates and status and if the expiry date is equal or less than 60 days, it should send email automatically.

My problem is how to set the until loop or any other alternative to enter every cell and check the condition

Code that I have used atm:

Private Sub CommandButton3_Click()

Dim OutlookApp As Object, OutLookMailItem As Object
Dim i As Byte, row_num As Byte
row_num = 2

Set OutlookApp = CreateObject("Outlook.application")
Set OutLookMailItem = OutlookApp.CreateItem(0)

i = 2

Do Until IsEmpty(Cells(i, 1))

    With OutLookMailItem
        .To = "email@gmail.com"
        .Subject = "[RESPONSE REQUIRED] Client's Environment Expiry Alert Notification - " & Sheets(2).Cells(row_num, 2)
        .Body = "Hi Team" & vbNewLine & vbNewLine _
                & "The Client Account : " & Sheets(2).Cells(row_num, 3) & "' with the  environment name - '" & Sheets(2).Cells(row_num, 2) _
                & "' is expiring on " & Sheets(2).Cells(row_num, 4)
                
        .send

    End With

row_num = row_num + 1

Loop

Set OutLookMailItem = Nothing
Set OutlookApp = Nothing

End Sub

 

[6StringJazzer]

You are never incrementing i inside your loop, so if cell A2 is not empty, your loop becomes an infinite loop.

What is the relationship between i and row_num? Are they really supposed to be the same row?

 

[aagarwal]

You are never incrementing i inside your loop, so if cell A2 is not empty, your loop becomes an infinite loop.

What is the relationship between i and row_num? Are they really supposed to be the same row?

I tried giving i=i+1 but still doesn't work as expected. It just sends out email only for the first row.
There is no relation between

i

and

row_num

. Both are separate.

row_num

is used to reference the values from the sheet while formatting the email body.

 

[aagarwal]

Client Name	Environment Name	Account Name	Expiry Date	Status
Hello	Hello	Hello	8/05/2022​	Already Expired 2 Day(s) ago
Hey	Hey	Hey	18/05/2022​	Going to Expire in 8 Days
Hola	Hola	Hola	30/06/2022​	Going to Expire in 51 Days
India	India	India	30/06/2022​	Going to Expire in 51 Days
Aus	Aus	Aus	1/10/2022​	Going to Expire in 144 Days
Canada	Canada	Canada	1/10/2022​	Going to Expire in 144 Days

 

[6StringJazzer]

There is no relation between

i

and

row_num

. Both are separate.

But if they both start as 2, and are both incremented in each pass of the loop, then they are the same. You are processing each row until column A is empty, and using that same row to populate the email. They refer to exactly the same thing.

The problem you are having is that you are reusing the same email object every time. You need to create a new one each time. I have moved that inside your loop.

This code makes those two changes and works perfectly for me:

Private Sub CommandButton3_Click()

   Dim OutlookApp As Object, OutLookMailItem As Object
   Dim row_num As Byte
   row_num = 2
 
   Set OutlookApp = CreateObject("Outlook.application")
 
   Do Until IsEmpty(Cells(row_num, 1))
 
       Set OutLookMailItem = OutlookApp.CreateItem(0)
       With OutLookMailItem
           .To = "email@gmail.com"
           .Subject = "[RESPONSE REQUIRED] Client's Environment Expiry Alert Notification - " & Sheets(2).Cells(row_num, 2)
           .Body = "Hi Team" & vbNewLine & vbNewLine _
                   & "The Client Account : " & Sheets(2).Cells(row_num, 3) & "' with the  environment name - '" & Sheets(2).Cells(row_num, 2) _
                   & "' is expiring on " & Sheets(2).Cells(row_num, 4)
                
           .send
 
       End With
 
       row_num = row_num + 1
 
   Loop
 
   Set OutLookMailItem = Nothing
   Set OutlookApp = Nothing

End Sub

 

[aagarwal]

But if they both start as 2, and are both incremented in each pass of the loop, then they are the same. You are processing each row until column A is empty, and using that same row to populate the email. They refer to exactly the same thing.

The problem you are having is that you are reusing the same email object every time. You need to create a new one each time. I have moved that inside your loop.

This code makes those two changes and works perfectly for me:

Private Sub CommandButton3_Click()

   Dim OutlookApp As Object, OutLookMailItem As Object
   Dim row_num As Byte
   row_num = 2
 
   Set OutlookApp = CreateObject("Outlook.application")
 
   Do Until IsEmpty(Cells(row_num, 1))
 
       Set OutLookMailItem = OutlookApp.CreateItem(0)
       With OutLookMailItem
           .To = "email@gmail.com"
           .Subject = "[RESPONSE REQUIRED] Client's Environment Expiry Alert Notification - " & Sheets(2).Cells(row_num, 2)
           .Body = "Hi Team" & vbNewLine & vbNewLine _
                   & "The Client Account : " & Sheets(2).Cells(row_num, 3) & "' with the  environment name - '" & Sheets(2).Cells(row_num, 2) _
                   & "' is expiring on " & Sheets(2).Cells(row_num, 4)
               
           .send
 
       End With
 
       row_num = row_num + 1
 
   Loop
 
   Set OutLookMailItem = Nothing
   Set OutlookApp = Nothing

End Sub

Thank you so much. It worked.!
Also one last thing how do I automate/use if to send emails only if the expiry is less than 60 days?

 

[aagarwal]

@6StringJazzer Can you please advise on the query to be used here to send emails only when the "Expiry Date" columns has value less than 90 days?

 

[6StringJazzer]

Do you mean less than 60 days in the future? I believe it would be this.

You should also have a column to track whether you have already sent an email, unless you want to continually send emails to the same person.

Private Sub CommandButton3_Click()

   Dim OutlookApp As Object, OutLookMailItem As Object
   Dim row_num As Byte
   row_num = 2
 
   Set OutlookApp = CreateObject("Outlook.application")
 
   Do Until IsEmpty(Cells(row_num, 1))
 
       If Cells(row_num, "D") < Date + 60 Then

          Set OutLookMailItem = OutlookApp.CreateItem(0)
          With OutLookMailItem
              .To = "email@gmail.com"
              .Subject = "[RESPONSE REQUIRED] Client's Environment Expiry Alert Notification - " & Sheets(2).Cells(row_num, 2)
              .Body = "Hi Team" & vbNewLine & vbNewLine _
                      & "The Client Account : " & Sheets(2).Cells(row_num, 3) & "' with the  environment name - '" & Sheets(2).Cells(row_num, 2) _
                      & "' is expiring on " & Sheets(2).Cells(row_num, 4)
                
              .send
 
          End With

       End If
 
       row_num = row_num + 1
 
   Loop
 
   Set OutLookMailItem = Nothing
   Set OutlookApp = Nothing

End Sub

 

[aagarwal]

Do you mean less than 60 days in the future? I believe it would be this.

You should also have a column to track whether you have already sent an email, unless you want to continually send emails to the same person.

Private Sub CommandButton3_Click()

   Dim OutlookApp As Object, OutLookMailItem As Object
   Dim row_num As Byte
   row_num = 2
 
   Set OutlookApp = CreateObject("Outlook.application")
 
   Do Until IsEmpty(Cells(row_num, 1))
 
       If Cells(row_num, "D") < Date + 60 Then

          Set OutLookMailItem = OutlookApp.CreateItem(0)
          With OutLookMailItem
              .To = "email@gmail.com"
              .Subject = "[RESPONSE REQUIRED] Client's Environment Expiry Alert Notification - " & Sheets(2).Cells(row_num, 2)
              .Body = "Hi Team" & vbNewLine & vbNewLine _
                      & "The Client Account : " & Sheets(2).Cells(row_num, 3) & "' with the  environment name - '" & Sheets(2).Cells(row_num, 2) _
                      & "' is expiring on " & Sheets(2).Cells(row_num, 4)
               
              .send
 
          End With

       End If
 
       row_num = row_num + 1
 
   Loop
 
   Set OutLookMailItem = Nothing
   Set OutlookApp = Nothing

End Sub

Thank you @6StringJazzer

 

[aagarwal]

Hi @6StringJazzer
Last one. If I have to send a collated email with the environments that are getting expired instead of sending one by one, how do I do that?