VBA HTML Email Body Format

[XfortunaX]

Hello,

I am using Excel 2010 and have a question regarding the format of the body of an email when using HTML.

I am looking for the Commission Amount to appear in the body of the email with a "," format. Right now, $10,000.00 shows up as $10000.00.

Email Body Example:
An ACH wire for the June 2015 Bel commissions in the amount of $17042.15 wassent today, 7/27/2015, for credit to your account 7/28/2015.<o></o>

[TABLE="width: 746"]
<tbody>[TR]
[TD]What
Column A
Commission Sheet Name[/TD]
[TD]Column B
Regional Company[/TD]
[TD]Column C
Commission Amount[/TD]
[/TR]
[TR]
[TD]Willis Marketing Commission Breakout June 2015 - Bel[/TD]
[TD]Willis Marketing[/TD]
[TD]$17,042.15[/TD]
[/TR]
</tbody>[/TABLE]

The code is as follows:

Sub Mail_workbook_Outlook()


Dim olApp As Outlook.Application
Dim olMail As Outlook.MailItem
Dim EmailBody As String
Dim NumNewComm As Integer
Dim X As Integer

X = 2
NumNewComm = Application.CountA(Range("A:A"))


Do While X <= NumNewComm


Set olApp = CreateObject("Outlook.Application")
Set olMail = olApp.CreateItem(Outmailitem)


Sheets("Filename").Select
CommissionDate = Sheets("Email").Range("G8").Value
Todaydate = Sheets("Email").Range("G2").Value
TOMMORROWDATE = Sheets("Email").Range("G5").Value
CompanyComm = Sheets("Email").Range("G14").Value
Commissionmonth = Sheets("Email").Range("G11").Value
CommissionAmount = Sheets("Filename").Cells(X, 3).Value
CheckNumber = Sheets("Filename").Cells(X, 7).Value
EmployeeName = Sheets("Filename").Cells(X, 8).Value
EmployeeEmail = Sheets("Filename").Cells(X, 6).Value
RegionalPartner = Sheets("Filename").Cells(X, 2).Value
Filepath = Sheets("Filename").Cells(X, 4).Value
EmailBody = Sheets("Filename").Cells(X, 5).Value
EmailBody = Replace(EmailBody, "Employeename", EmployeeName)
EmailBody = Replace(EmailBody, "COMMISSIONDATE", CommissionDate)
EmailBody = Replace(EmailBody, "TODAYDATE", Todaydate)
EmailBody = Replace(EmailBody, "TOMMORROWDATE", TOMMORROWDATE)
EmailBody = Replace(EmailBody, "COMMISSIONAMOUNT", CommissionAmount)
EmailBody = Replace(EmailBody, "CompanyComm", CompanyComm)
EmailBody = Replace(EmailBody, "CHECKNUMBER", CheckNumber)
Subject = RegionalPartner & " " & CompanyComm & " " & "Commission Breakout" & " " & "-" & " " & Commissionmonth


On Error Resume Next

With olMail
.To = EmployeeEmail
.CC = ""
.BCC = ""
.Subject = Subject
.BodyFormat = olFormatHTML
.HTMLBody = EmailBody
.Attachments.Add Filepath
'You can add other files also like this .Attachments.Add ("C:\test.txt")
'Change Item(1)to the account number that you want to use HS. is (1) and Finance. is (2)
.SendUsingAccount = olApp.Session.Accounts.Item(2)
.Display 'or use .Send


End With
On Error GoTo 0


Set olMail = Nothing
Set olApp = Nothing


X = X + 1
Loop

Sheets("Email").Range("I6").Delete


End Sub

Any help would be greatly appreciated.

Thank you,

Tuna

 

[Micron]

Are the cells where the data comes from formatted as currency? If so, I would have thought the number would have come across that way. In that case, try
CommissionAmount = Format(Sheets("Filename").Cells(X, 3).Value,"currency")

 

[XfortunaX]

Micron, that did the trick. Thank you for taking the time to respond. I really appreciate it.

 

[Micron]

You're welcome. It's nice to know when a solution works!