At work there is a common task which often is not completed on time. It’s my responsibility to remind colleagues to complete this thing if the deadline passes. Rather than continually write out individual emails, I decided I could use Excel and VBA to automate generation of emails that go out to relevant colleagues. I needed to use vba to create the personalised emails thanks to the limitations of the hyperlink function.

This is fine, however there are some circumstances in which it’s acceptable for the deadline to pass and no email to be sent. So, in my workbook I have column I labelled as “Extenuating circumstances”. The values in this column will be defined by data validation, so I basically need the macro to recognise that if a cell in column I is not blank, it should not produce an email for that individual.

The code I have written is here:

Sub Send_Email_to_List()
'


Dim OL As Object, MailSendItem As Object
Dim user_msg As String
Dim MsgTxt As String

Set OL = CreateObject("Outlook.Application")

For Each xCell In ActiveSheet.Range(Range("H4"), Range("H" & Rows.Count).End(xlUp))

If Cells(xCell.Row, 12).Value = "" Then
    Exit Sub
End If

    Body_1 = Cells(xCell.Row, 12).Value
    Body_2 = Cells(xCell.Row, 13).Value

    user_email = xCell.Value
    user_subject = Cells(xCell.Row, 11).Value
    user_msg = Body_1 & Body_2

    Set MailSendItem = OL.CreateItem(olMailItem)

    With MailSendItem
        .Subject = user_subject
        .Body = user_msg
        .To = user_email
        .Display
    End With

Next xCell

Set OL = Nothing

End Sub

I’ve tried myself so far to figure this out but am unable to. I believe I need to insert some variant of

If IsEmpty(Cells(xCell.Row, 9)) Then
    Exit Sub

but I can’t get it to work properly. Any help would be greatly appreciated! Thank you in advance.