Format number to fixed decimal place

[WembleyBear]

Hi

I have modified Ron de Bruin's SendMail code example to send an email when a
certain procedure is complete, and all works fine. However, as part of the
message body I want the value from a cell (TotalD) on the worksheet to be
included. This also works fine but if the value has a trailing zero, it shows
as 123456.8 in the body text of the email instead of 123456.80 which is how I
want it. I'm having trouble getting the code to format the number correctly -
can anyone point me in the right direction as I'm sure it's something simple.
The portion of code is below:

Dim OutApp As Object
Dim OutMail As Object
Dim TotalD As Double
TotalD = Range("H3")


Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon

On Error GoTo cleanup

Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = "(e-mail address removed)"
.Subject = "URGENT: Direct Debit File Awaiting Submission"
.Body = "A new Direct Debit submission file has been created
and is awaiting pickup and transmission in Bottomline PayBase. The total
value for the submission is: £" & TotalD
.Send
End With


Thanks very much
Martyn

Excel 2000, Windows 2003 server over Citrix PS4

 

[Shasur]

Hi

You can try setting the formula in Excel

ActiveCell.NumberFormat = "0.00"

Cheers
Shasur

 

[WembleyBear]

Hi

I did already try setting that (the cell's format property in the Workbook
is also set to 2 decimal places) but I still end up with any trailing zeroes
after the decimal point being removed.

Martyn

 

[Shasur]

Hi

Since you are concatenating the value with the string, can you have the
value as string like the one below

Sub NumberFormat()

Dim TotString

TotString = Format(Range("H3"), "###0.00")


End Sub

Cheers

 

[WembleyBear]

Thanks - that did the trick!

Martyn

Hi

Since you are concatenating the value with the string, can you have the
value as string like the one below

Sub NumberFormat()

Dim TotString

TotString = Format(Range("H3"), "###0.00")


End Sub

Cheers