2

I have the following simple Excel spreadsheet:

          A                  B           C
1   25,000,000.50
2
3

And the following VBA code to send an E-Mail with the content of Cell A1:

Sub Separators_in_Email()
    If ExitAll = False Then
        Dim OApp As Object, OMail As Object, signature As String
        Set OApp = CreateObject("Outlook.Application")
        Set OMail = OApp.CreateItem(0)
            With OMail
            .display
            End With
            signature = OMail.HTMLBody
            With OMail
            .To = "test@test.de"
            .Subject = "test"
            .HTMLBody = "<p> I want this number " & Sheet1.Range("A1") & " to be shown with its decimal separators as in the Excel sheet</p>"
            End With
        Set OMail = Nothing
        Set OApp = Nothing
    Else
    End If
End Sub

The E-Mail itself works perfectly.

However, as you can see in the Excel spreadsheet I use "," as decimal separators. In the E-Mail this decimal separators are not used. Instead the number is written as 25000000.5 without the ",".

Is there a way in VBA or HTML/CSS to show the number from the sheet with the correct decimal separators?

Community
  • 1
  • 1
Michi
  • 4,663
  • 6
  • 33
  • 83

1 Answers1

1

With your code line:

.HTMLBody = "<p> I want this number " & Sheet1.Range("A1") & " to be shown with its decimal separators as in the Excel sheet</p>"

You should replace Sheet1.Range("A1") with Sheet1.Range("A1").Text which will return the formatted value of the cell.

When you just use Sheet1.Range("A1") it returns the default property of the cell - Value - which contains an unformatted value. See here

CSS/ HTML are not a factor for this problem.

Robin Mackenzie
  • 18,801
  • 7
  • 38
  • 56