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

Send email in outlook with excel sheet snapshot using VBA

 Option Explicit

Sub Send_Email_With_snapshot()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet1")

Dim lr As Integer
lr = sh.Range("A" & Application.Rows.Count).End(xlUp).Row

sh.Range("A1:H" & lr).Select

With Selection.Parent.MailEnvelope.Item
    .to = sh.Range("L6").Value
    .cc = sh.Range("L7").Value
    .Subject = sh.Range("L8").Value
    .attachments.Add "C:\Users\allso\Desktop\new vba projects\arnab first class.xlsx"
    .send
End With

MsgBox "Done"


End Sub

 

 

 

Source:https://www.pk-anexcelexpert.com/send-email-with-snapshot/

 Source:https://www.youtube.com/watch?v=aD-lo81I5C4