chm20
New Member
- Joined
- Jul 6, 2022
- Messages
- 2
- Office Version
- 365
- 2021
- 2019
- Platform
- Windows
- MacOS
- Mobile
- Web
I have a simple code to draft emails to different sub-recipients. This is a draft closeout email and I would like to include the dollar amount of the check they need to send back. The $$ check amounts are in column B, rows 2-43. Column A has the sub-recipient name. The code below works great, but I can't figure out the code to add the respective check amount into the body of the email.
VBA Code:
Sub SendEmailfromOutlook()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Dim Path As String
Path = Application.ActiveWorkbook.Path
Set OutApp = CreateObject("Outlook.Application")
For Each cell In Range("D2:D43")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = cell.Value
.Cc = Cells(cell.Row, "E").Value
.Subject = "Close Out - Awaiting Payment - " & Cells(cell.Row, "A").Value
.HTMLBody = "Good afternoon," & "<p></p>" _
& "<p>As a part of the Program close out, we require (1) completion of a final certification form and (2) repayment of unspent funds. Thank you for submitting your final certification form.</p>" _
& "<p><b>I am following up as we have not yet received a mailed check for unspent funds</b>. We are on a tight deadline to close this program out. Checks should be made out to the <b>CM</b> and sent to: </p>" _
& "<br>" & "CFO" _
& "<p>Please mail checks as soon as possible. If you have any questions or concerns, please contact me.</p>" _
& "<p>Thank you,</p>" _
& "<br>" & "CHM" & "<br>"
'.Send
.Save
End With
Next cell
End Sub