VBA code to send emails if column i = >5

[Reecenorman1996]

Below is the code i use i need a check to see if column i is greater than 5, if it is then send the email if not do not send the email


Sub Send_Bulk_Mails()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("sheet1")
Dim i As Integer

Dim OA As Object
Dim msg As Object

Set OA = CreateObject("outlook.application")

Dim last_row As Integer
last_row = Application.CountA(sh.Range("c:c"))

For i = 2 To last_row
Set msg = OA.createitem(0)
msg.to = sh.Range("i" & i).Value
msg.cc = sh.Range("j" & i).Value & ";" & sh.Range("k" & i).Value
msg.Subject = sh.Range("l" & i).Value
msg.Body = sh.Range("m" & i).Value

msg.send

sh.Range("n" & i).Value = "1"

Next i

MsgBox "All emails have been sent"

End Sub

 

[Joe4]

If you only want to send emails when i>5, why are you starting your loop at 2?

And do you want to send an email for EACH value over 5, or just one email total?

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: VBA code to send emails if column i = >5
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Reecenorman1996]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: VBA code to send emails if column i = >5
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Sorry wont happen again

 

[Reecenorman1996]

If you only want to send emails when i>5, why are you starting your loop at 2?

And do you want to send an email for EACH value over 5, or just one email total?

Whats starting the loop ? and i want it to go through every row and send individual emails for each cell that has more than 5 characters( sorry i put the wrong column above)

 

[Joe4]

Whats starting the loop ? and i want it to go through every row and send individual emails for each cell that has more than 5 characters( sorry i put the wrong column above)

Ah, you are talking about column i. It is very confusing because you also used the letter "i" for your loop counter!

Try this:

Sub Send_Bulk_Mails()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("sheet1")
Dim i As Integer

Dim OA As Object
Dim msg As Object

Set OA = CreateObject("outlook.application")

Dim last_row As Integer
last_row = Application.CountA(sh.Range("c:c"))

For i = 2 To last_row
    If Len(sh.Range("i" & i).Value) > 5 Then
        Set msg = OA.createitem(0)
        msg.to = sh.Range("i" & i).Value
        msg.cc = sh.Range("j" & i).Value & ";" & sh.Range("k" & i).Value
        msg.Subject = sh.Range("l" & i).Value
        msg.Body = sh.Range("m" & i).Value

        msg.send
    End If

    sh.Range("n" & i).Value = "1"

Next i

MsgBox "All emails have been sent"

End Sub

Note: Regarding Cross-Posting, as our rules say, it is OK to do as long as you mention you are doing so and provide links to the other posts.

 

[Reecenorman1996]

Ah, you are talking about column i. It is very confusing because you also used the letter "i" for your loop counter!

Try this:

Sub Send_Bulk_Mails()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("sheet1")
Dim i As Integer

Dim OA As Object
Dim msg As Object

Set OA = CreateObject("outlook.application")

Dim last_row As Integer
last_row = Application.CountA(sh.Range("c:c"))

For i = 2 To last_row
    If Len(sh.Range("i" & i).Value) > 5 Then
        Set msg = OA.createitem(0)
        msg.to = sh.Range("i" & i).Value
        msg.cc = sh.Range("j" & i).Value & ";" & sh.Range("k" & i).Value
        msg.Subject = sh.Range("l" & i).Value
        msg.Body = sh.Range("m" & i).Value

        msg.send
    End If

    sh.Range("n" & i).Value = "1"

Next i

MsgBox "All emails have been sent"

End Sub

Note: Regarding Cross-Posting, as our rules say, it is OK to do as long as you mention you are doing so and provide links to the other posts.

Works perfectly thanks so much

 

[Joe4]

You are welcome.
Glad I was able to help!