Send query result in Outlook Email with Number formatted table-CodePudding

I want to send an email in Outlook that includes my query result from Access. The body of the email includes a table (columns/rows) with the results. I want to use a Number format with commas xx,xxx when value is a number.

I recycled this code I found here. How do I format the table output?

Public Sub NewEmail()

    Dim olApp As Object
    Dim olItem As Variant
    Dim db As DAO.Database
    Dim rec As DAO.Recordset
    Dim strQry As String
    Dim aHead(1 To 3) As String
    Dim aRow(1 To 3) As String
    Dim aBody() As String
    Dim lCnt As Long

    'Create the header row
    aHead(1) = "Date"
    aHead(2) = "Request Type"
    aHead(3) = "Total" 'I want this to be comma separate number format?

    lCnt = 1
    ReDim aBody(1 To lCnt)
    aBody(lCnt) = "<HTML><body><table border='2'><tr><th>" & Join(aHead, "</th><th>") & "</th></tr>"

    'Create each body row
    strQry = "SELECT * From Email_Query"
    Set db = CurrentDb
    Set rec = CurrentDb.OpenRecordset(strQry)

    If Not (rec.BOF And rec.EOF) Then
        Do While Not rec.EOF
            lCnt = lCnt   1
            ReDim Preserve aBody(1 To lCnt)
            aRow(1) = rec("Test1")
            aRow(2) = rec("Test2")
            aRow(3) = rec("Test3")
            aBody(lCnt) = "<tr><td>" & Join(aRow, "</td><td>") & "</td></tr>"
            rec.MoveNext
        Loop
    End If

    aBody(lCnt) = aBody(lCnt) & "</table></body></html>"

    'create the email
    Set olApp = CreateObject("Outlook.application")
    Set olItem = olApp.CreateItem(0)

    olItem.display
    olItem.To = "[email protected]"
    olItem.Subject = "Test E-mail"
    olItem.htmlbody = Join(aBody, vbNewLine)
    olItem.display

End Sub

CodePudding user response：

Try something like this:

            aRow(1) = Format(rec("YourDateField"), "yyyy-mm-dd")
            aRow(2) = rec("YourRequestType")
            aRow(3) = Format(rec("YourTotal"), "0.000")

If that leaves a dot as the decimal separator, try:

            aRow(3) = Replace(LTrim(Str(rec("YourTotal"))), ".", ",")

If comma is your thousand separator, try:

            aRow(3) = Format(rec("YourTotal"), "00,000")