如何在Outlook邮件中发送带千分位数字格式表格的Access查询结果

修改核心逻辑 #

你只需要调整数值列的赋值语句，用VBA内置的Format函数对数值字段做格式化即可：

• 千分位无小数使用格式符 #,##0
• 千分位保留2位小数使用格式符 #,##0.00

调整后的完整代码 #

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

    '创建表头行
    aHead(1) = "日期"
    aHead(2) = "申请类型"
    aHead(3) = "总额" ' 该列需要设置千分位逗号格式

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

    '创建表格内容行
    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) = Format(rec("Test3"), "#,##0")
            aBody(lCnt) = "<tr><td>" & Join(aRow, "</td><td>") & "</td></tr>"
            rec.MoveNext
        Loop
    End If

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

    '创建邮件
    Set olApp = CreateObject("Outlook.application")
    Set olItem = olApp.CreateItem(0)

    olItem.display
    olItem.To = "example@example.com"
    olItem.Subject = "测试邮件"
    olItem.htmlbody = Join(aBody, vbNewLine)
    olItem.display

End Sub

内容的提问来源于stack exchange，提问作者Bruce Pavkov