Send emails using VBA from Excel sheet with multiple recipients

I am using VBA code to send multiple emails from an Excel sheet that looks like the example below:

Example Excel Sheet

So far I am able to use code to generate a separate email for each email address in Column C with a file attached. My code is below - I am looking to add code that would allow me to do the following:

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, but don't know how to get the date to pull in for each email row by row.
Attach an additional file to the email IF there is a link in column E. This would be a second attachment, in addition to the one that is already in the code.

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

    Sub CreateEmails()
    
        Dim sourceWorksheet As Worksheet
        Set sourceWorksheet = Worksheets("Sheet1")
    
        Dim lastRow As Long
        With sourceWorksheet
             lastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
        End With
    
        Dim OutlookApp As Object
        Set OutlookApp = CreateObject("Outlook.Application")
    
        Dim rowIndex As Long
    
        For rowIndex = 2 To lastRow 'start at the second row
    
            Dim MItem As Object
            Set MItem = OutlookApp.CreateItem(0)
    
            With MItem
                .To = sourceWorksheet.Cells(rowIndex, "C").Value
                'Will pull in all email address in Row C as separate emails
                .Subject = "Subject Here"
                .Attachments.Add “first attachment link goes here"
                .htmlBody = "<p><font face = ""Calibri(Body)"" font size=""3"" color=""black"">Good afternoon, </p>" & _
               "<p><font face = ""Calibri(Body)"" font size=""3"" color=""black""><strong>Please review the attached and return by" & DueDate & ". </strong>  </p>"& .htmlBody
    
                 .display
    
            End With
        Next rowIndex
    End Sub

CodePudding user response：

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