VBA Email Code to include cell $ value in body of email HELP

chm20

New Member
Joined
Jul 6, 2022
Messages
2
Office Version
  1. 365
  2. 2021
  3. 2019
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. 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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Try adding the line in bold:
Rich (BB code):
                      & "<br>" & "CFO" _
                      & "<p>The amount due is $ " & Format(cell.Offset(0, -2).Value, "0.00") _
                      & "<p>Please mail checks as soon as possible. If you have any questions or concerns, please contact me.</p>" _
 
Upvote 0
That works! Thanks so much for your help!
 
Upvote 0
Thank you for the feedback
If you think the problem is solved then it would be better if you mark the thread as closed, see Mark as Solution
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,206
Members
452,618
Latest member
Tam84

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top