CODEWITHSUNDEEP
vbaexceloutlook
Fabrizio Martinez
Fabrizio Martinez

Reputation: 61

Format Excel value output to Outlook

I'm trying to create a tool that grabs information from Excel and outputs it to Outlook.

So far so good, but I'm unable to get a numerical value to show up formatted in the email. It shows it as plain numbers, e.g. 203486442, but I want it to be $203,486,441.93 instead. I get an "Invalid Qualifier" error when trying to format it. This number is pulled from the "Total" row of a Table in my worksheet. 

Sub Test1()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range

Set OutApp = CreateObject("Outlook.Application")

Dim LastRow As Long
Dim LastValue As Long
LastRow = Sheets("Detail Aging").Range("E" & Rows.Count).End(xlUp).Row
LastValue = Sheets("Detail Aging").Range("E" & LastRow).Value

Dim strbody As String
For Each cell In Worksheets("Body").Range("B2:B6")
    strbody = strbody & cell.Value & vbNewLine
Next

On Error GoTo cleanup
For Each cell In Columns("D").Cells.SpecialCells(xlCellTypeConstants)
    If cell.Value Like "?*@?*.?*" And _
       LCase(Cells(cell.Row, "E").Value) = "yes" Then

        Set OutMail = OutApp.CreateItem(0)
        On Error Resume Next
        With OutMail
            .To = cell.Value
            .CC = Cells(cell.Row, "G").Value
            .Subject = "CompuCom Reminder Letter | " & Cells(cell.Row, "B").Value
            .Body = "Dear " & Cells(cell.Row, "C").Value & "," _
                & vbNewLine & vbNewLine & _
                "This is a friendly reminder that the " & _
                "total balance due amount on your account is " & LastValue _
                & vbNewLine & vbNewLine & _
                "Here is the list of outstanding transactions:" _
                & vbNewLine & vbNewLine & _
                "Please let us know what the payment status is and if you require additional " & _
                "information or documentation to process the invoice(s) for payment." _
                & vbNewLine & vbNewLine & _
                "Thank you." _
                & vbNewLine & vbNewLine & _
                Cells(cell.Row, "F").Value & vbNewLine & _
                Cells(cell.Row, "G").Value & vbNewLine & _
                Cells(cell.Row, "H").Value & vbNewLine & _
                "Regards,"
            .Display
        End With

        On Error GoTo 0
        Set OutMail = Nothing

    End If

Next cell

Upvotes: 1

Views: 72

Answers (1)

David Zemens
David Zemens

Reputation: 53663

Try using the cell's .Text property instead of the .Value

You will need to change your declaration of Lastvalue, try:

Dim lastValue As String ' or As Variant

Upvotes: 1

Related Questions