Send emails using VBA from Excel sheet with multiple recipients - pull in date and attachment

Question

I am using VBA code to send emails from an Excel sheet:

I use code to generate a separate email for each email address in Column C with a file attached.

I am looking to add code to:

1. Pull in the date from column D to the body of the email.
   I have put in the code for the body of the email where I want the date to go.
   How do I get the date for each email row by row.
2. Attach an additional file to the email IF there is a link in column E.
   This would be in addition to the one already in the code.

Each row in the Excel sheet should generate a separate email using the email address in column C, date in column D and attachment in column E.

Answer 1

assume that the rest of the code works, just update the with-block to:

With MItem
    .To = sourceWorksheet.Cells(RowIndex, 3).Value
    .Subject = "Subject Here"
    If Cells(RowIndex, 5) <> "" Then .Attachments.Add Cells(RowIndex, 5).Value
    If Cells(RowIndex, 6) <> "" Then .Attachments.Add Cells(RowIndex, 6).Value
    .HTMLBody = "<font face = ""Calibri(Body)"" font size=""3"" color=""black""><p>Good afternoon, </p>" & _
    "<p><strong>Please review the attached and return by " & Cells(RowIndex, 4) & ". </strong>  </p></font>" & .HTMLBody
     .Display
End With