Sending Automated emails - WITH GENERIC AND CUSTOMIZED CONTENT Via Outlook

Hello Everyone,


Long time I have written a blog on Excel VBA.

This blog contains details regarding sending automated emails from an excel template, which is a common thing and you'll find many other blogs for the same.


But What's different in this is:

1. You can use customized names for few user and the generic name for the rest.
2. Send a copy of the mail to an individual person or just CC to the generic address.
3. You can use customized subject for few user and the generic subject for the rest.
4. You can have customized message body for few user and the generic content for the rest.
5. You can use customized signatures for few user and the generic signature for the rest.
6. You can also have attachments for few users.

Here is the code for the same :

Sub automated_email()
    
    Dim counter As Long
    
    Dim outlookApp As Outlook.Application
    Dim outlookMail As MailItem
    Set outlookApp = New Outlook.Application
    
    Dim bodyString As String
    
    For counter = 5 To formatEmail.Cells(formatEmail.Rows.Count, "A").End(xlUp).Row

        Set outlookMail = outlookApp.CreateItem(outlookMailItem)
        outlookMail.To = formatEmail.Range("B" & counter).Value
        
        If formatEmail.Range("C" & counter).Value <> "" Then
            outlookMail.CC = formatEmail.Range("C" & counter).Value
        Else
            outlookMail.CC = formatEmail.Range("C2").Value
        End If
        
        outlookMail.BCC = formatEmail.Range("D" & counter).Value
        
        If formatEmail.Range("E" & counter).Value <> "" Then
            outlookMail.Subject = formatEmail.Range("E" & counter).Value
        Else
            outlookMail.Subject = formatEmail.Range("E2").Value
        End If
        
        bodyString = "Hi "
        
        If formatEmail.Range("A" & counter).Value <> "" Then
            bodyString = bodyString & formatEmail.Range("A" & counter).Value & "," & vbNewLine & vbNewLine
        Else
            bodyString = bodyString & formatEmail.Range("A2").Value & "," & vbNewLine & vbNewLine
        End If
        If formatEmail.Range("G" & counter).Value <> "" Then
            bodyString = bodyString & formatEmail.Range("G" & counter).Value & vbNewLine & vbNewLine
        Else
            bodyString = bodyString & formatEmail.Range("G2").Value & vbNewLine & vbNewLine
        End If
        If formatEmail.Range("H" & counter).Value <> "" Then
            bodyString = bodyString & formatEmail.Range("H" & counter).Value & vbNewLine & vbNewLine
        Else
            bodyString = bodyString & formatEmail.Range("H2").Value & vbNewLine & vbNewLine
        End If
        
        outlookMail.Body = bodyString
        
        If formatEmail.Range("F" & counter).Value <> "" Then
            If Dir(formatEmail.Range("F" & counter).Value) <> "" Then
                outlookMail.Attachments.Add formatEmail.Range("F" & counter).Value
            End If
        End If
        
'        Use outlookMail.Display to review before sending
        outlookMail.Display
        
'        Use outlookMail.Send to directly send without reviewing
'        outlookMail.Send
        
    
        Set outlookMail = Nothing
    Next
       
    Set outlookApp = Nothing
    
End Sub

Please put your thoughts and comments below so that I can improve.

THANKS EVERYONE.
SUBHAJIT.
.
To Download the example file:
Please click this Google Drive Link