How can I add a condition to only create email when the value is less or equal to 30?

[DarkDrag318]

Hello,
How can I add a condition to only create an email when the value is less or equal to 30? Also can it creating emails to start from row 3 and down instead of row 1?

Here is my macro code that I need help with, thank you:

Sub Create_Email()

Application.ScreenUpdating = False

Dim EmailTo As String
Dim EmailAddress As String
Dim Value As Double
Dim LastRow As Integer
Dim RowCounter As Integer

'Defining outlook variables
Dim OutApp As Object
Dim Outmail As Object

LastRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row

For RowCounter = 2 To LastRow

EmailTo = Sheet1.Range("K" & RowCounter).Value

EmailAddress = Sheet1.Range("L" & RowCounter).Value

Value = Sheet1.Range("I" & RowCounter).Value

Name = Sheet1.Range("A" & RowCounter).Value

'Allocated and created

Set OutApp = CreateObject("Outlook.Application")

Set Outmail = OutApp.CreateItem(0)


With Outmail

.To = EmailAddress

.Subject = Name & ": " & "Loan Maturing in " & Value & " days"

.Body = "Dear " & EmailTo & _

vbNewLine & vbNewLine & _

"Please be advised that this account will be maturing. Proceed to obtain all required items begin to process an extension/renewal, if applicable." & _

vbNewLine & vbNewLine & _

"Kindly Regards," & _

vbNewLine & _

"Me"

'Set .SendUsingAccount = OutApp.Session.Accounts.Item("")

.display

'.Send

End With


Next RowCounter

Application.ScreenUpdating = True


End Sub

 

[6StringJazzer]

when the value is less or equal to 30?

When what value is less or equal to 30?

start from row 3 and down instead of row 1?

You are actually starting from row 2.

For RowCounter = 2 To LastRow

EmailTo = Sheet1.Range("K" & RowCounter).Value

You can change the 2 to 3

 

[DarkDrag318]

Hi, thank you for your response. I will make the update from row 2 to 3.

On values on column I of my sheet:

Value = Sheet1.Range("I" & RowCounter).Value

I want the code to create an email when a cell on Column ‘I’ of my sheet has a number ‘30’ or less. Currently the code is creating emails for all each row (creating 10 emails) but I wanted to add a condition to only create emails to the ones that have a “30” or less than “30”. Let me know if that makes more sense. Thanks for your help.

 

[6StringJazzer]

Please consider using CODE tags next time so your code is more readable.

Note changes below:

Sub Create_Email()

   Application.ScreenUpdating = False
   
   Dim EmailTo As String
   Dim EmailAddress As String
   Dim Value As Double
   Dim LastRow As Integer
   Dim RowCounter As Integer
   
   'Defining outlook variables
   Dim OutApp As Object
   Dim Outmail As Object
   
   LastRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row



   For RowCounter = 3 To LastRow
   
      Value = Sheet1.Range("I" & RowCounter).Value
      
      If Value <= 30 Then
      
         EmailTo = Sheet1.Range("K" & RowCounter).Value
         EmailAddress = Sheet1.Range("L" & RowCounter).Value
   
         Name = Sheet1.Range("A" & RowCounter).Value
         
         'Allocated and created
         
         Set OutApp = CreateObject("Outlook.Application")
         Set Outmail = OutApp.CreateItem(0)
         
         With Outmail
         
            .To = EmailAddress
            
            .Subject = Name & ": " & "Loan Maturing in " & Value & " days"
            
            .Body = "Dear " & EmailTo & _
                    vbNewLine & vbNewLine & _
                    "Please be advised that this account will be maturing. Proceed to obtain all required items begin to process an extension/renewal, if applicable." & _
                    vbNewLine & vbNewLine & _
                    "Kindly Regards," & _
                    vbNewLine & _
                    "Me"
            
            'Set .SendUsingAccount = OutApp.Session.Accounts.Item("")
            
            .display
            
            '.Send
         
         End With
         
      End If
      
   Next RowCounter

   Application.ScreenUpdating = True

End Sub

 

[DarkDrag318]

Noted. Thanks for your help!