VBA sending an email of two conditions are met

[Steve N]

I'd be really grateful if some one could help me with this, I've been trying to work it out for a while but I'm not getting anywhere. I have a VBA to send an email based on individual rows and the data they contain.
I'd like a way of sending individual emails to all recipients and the data on their line if two conditions in their line are met - that Its been confirmed in column G and that no email has been sent; column H is empty. I'd like to do this with one button rather than creating a button for each line.

My VBA:
Sub SendEmailFromExcel()

Dim OutApp As Object, OutMail As Object

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

With OutMail
if
.To = Range("E1").Value
.CC = Range("R1").Value
.Subject = Range("R1").Value
.Body = Range("J3") & vbNewLine & vbNewLine & _
Range("K2") & vbNewLine & _
Range("L2") & vbNewLine & _
Range("M2") & vbNewLine & _
Range("N2") & vbNewLine & _
Range("O2") & vbNewLine & vbNewLine & _
"Many Thanks,"
.Display
'.Send
End With

Set OutApp = Nothing
Set OutMail = Nothing

End Sub

 

[mumps]

Try:

Sub SendEmailFromExcel()
    Application.ScreenUpdating = False
    Dim OutApp As Object, OutMail As Object, v As Variant, i As Long
    v = Range("E2", Range("E" & Rows.Count).End(xlUp)).Resize(, 11).Value
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    For i = LBound(v) To UBound(v)
        If v(i, 3) <> "" And v(i, 4) = "" Then
            With OutMail
                .To = v(i, 1)
                .cc = Range("Q1").Value
                .Subject = Range("R1").Value
                .HTMLBody = v(i, 6) & "<br><br>" & v(i, 7) & "<br>" & v(i, 8) & "<br>" & v(i, 9) & "<br>" & v(i, 10) & "<br>" & v(i, 11) _
                & "<br><br>" & "Many Thanks,"
                .Display
            End With
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[Steve N]

Try:

Sub SendEmailFromExcel()
    Application.ScreenUpdating = False
    Dim OutApp As Object, OutMail As Object, v As Variant, i As Long
    v = Range("E2", Range("E" & Rows.Count).End(xlUp)).Resize(, 11).Value
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    For i = LBound(v) To UBound(v)
        If v(i, 3) <> "" And v(i, 4) = "" Then
            With OutMail
                .To = v(i, 1)
                .cc = Range("Q1").Value
                .Subject = Range("R1").Value
                .HTMLBody = v(i, 6) & "<br><br>" & v(i, 7) & "<br>" & v(i, 8) & "<br>" & v(i, 9) & "<br>" & v(i, 10) & "<br>" & v(i, 11) _
                & "<br><br>" & "Many Thanks,"
                .Display
            End With
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

Hi Mumps, thank you so much for this I tweaked it to fit my actual sheet and it works perfectly, very much appreciated!

 

[mumps]

You are very welcome.

 

[Sequoyah]

Hi @mumps,, in this case it works the same, but I think the line

Set OutMail = OutApp.CreateItem(0)

should be placed inside the loop after the If condition check (as in your response in this thread Add a table to e-mail to be sent to multiple addreses). Try to see what would happen if you add an attachment.

Sub SendEmailFromExcel()
    Application.ScreenUpdating = False
    Dim OutApp As Object, OutMail As Object, v As Variant, i As Long
    v = Range("E2", Range("E" & Rows.Count).End(xlUp)).Resize(, 11).Value
    Set OutApp = CreateObject("Outlook.Application")
    
    For i = LBound(v) To UBound(v)
        If v(i, 3) <> "" And v(i, 4) = "" Then
            Set OutMail = OutApp.CreateItem(0)
......

 

[mumps]

@Sequoyah
You are correct. Thanks for picking up on that.