Condition based mass mailing using outlook and vba

 Option Explicit

Sub Send_Email_with_Signature()

Dim Outlook_App As Object
Dim msg As Object
Dim sign As String
Dim i As Integer
Set Outlook_App = CreateObject("Outlook.Application")
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Data")
 
For i = 3 To sh.Range("B" & Application.Rows.Count).End(xlUp).Row

If sh.Range("A" & i).Value = "" Then  ''Check Skip
    
    Set msg = Outlook_App.CreateItem(0)
    
    With msg
       ' .display
    End With
    
    sign = msg.htmlbody
    
    With msg
        .To = sh.Range("C" & i).Value
        .Subject = "Payment Reminder"
        .htmlbody = "Dear <b>" & sh.Range("B" & i).Value & "</b>,<br><br><p>Please pay your bill for below given service(s)- </p>" & _
        "<ul>" & _
        IIf(sh.Range("D" & i).Value <> "", "<li><b style='color:DodgerBlue'><u>" & sh.Range("D2").Value & ":</u></b>  " & Format(sh.Range("D" & i).Value, "0.0") & " is pending.</li>", "") & _
        IIf(sh.Range("E" & i).Value <> "", "<li><b style='color:Tomato;'><u>" & sh.Range("E2").Value & ":</u></b>  " & Format(sh.Range("E" & i).Value, "0.0") & " is pending.</li>", "") & _
        IIf(sh.Range("F" & i).Value <> "", "<li><b style='color:green;'><u>" & sh.Range("F2").Value & ":</u></b>  " & Format(sh.Range("F" & i).Value, "0.0") & " is pending.</li>", "") & _
        IIf(sh.Range("G" & i).Value <> "", "<li><b style='color:Orange;'><u>" & sh.Range("G2").Value & ":</u></b>  " & Format(sh.Range("G" & i).Value, "0.0") & " is pending.</li>", "") & _
        IIf(sh.Range("H" & i).Value <> "", "<li><b style='color:Blue;'><u>" & sh.Range("H2").Value & ":</u></b>  " & Format(sh.Range("H" & i).Value, "0.0") & " is pending.</li>", "") & _
        "</ul>" & _
        sign
    
        If sh.Range("H1").Value = 1 Then  ''' check option button value
            .send
        Else
            .display
        End If
    End With
    
    Set msg = Nothing

End If

Next i


Set Outlook_App = Nothing

If sh.Range("H1").Value = 1 Then MsgBox "Done"

End Sub

Source:https://www.youtube.com/watch?v=A6tggcOV6ts