如何通过VBA实现Outlook邮件中Excel单元格内容逐行显示？

要让Excel单元格内容在邮件中单独占一行，只需调整build_body函数的分隔符和拼接逻辑，修改后的完整代码如下：

Sub Mail_small_Text_Outlook()
    '适用于Office 2000-2016
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
        
    strbody = build_body(ActiveWorkbook.Sheets("Sheet2").Range("B45:C65")) & vbNewLine & _
      "2nd Shift Trippers" & vbNewLine & _
      build_body(ActiveWorkbook.Sheets("Sheet2").Range("f45:g65"))
    
    On Error Resume Next
    With OutMail
        .To = "namen@somewhere.org"
        .CC = ""
        .BCC = ""
        .Subject = Date & " Trippers" 'Outlook主题不支持换行，移除原代码中的vbCrLf
        .Body = strbody
        '如需添加附件可取消下方注释
        '.Attachments.Add ("C:\test.txt")
        .Send   '替换为.Display可先预览邮件
    End With
    On Error GoTo 0
        
    Set OutMail = Nothing
    Set OutApp = Nothing
        
End Sub

Function build_body(rng As Range, Optional delimiter As String = vbNewLine) As String
    Dim cel As Range
    Dim tmpStr As String
    For Each cel In rng.Cells
        '跳过空单元格，避免邮件出现连续空行
        If cel.Value <> "" Then
            If tmpStr <> "" Then
                tmpStr = tmpStr & delimiter & cel.Value
            Else
                tmpStr = cel.Value
            End If
        End If
    Next cel
    build_body = tmpStr
End Function

关键修改说明 #

• 将build_body函数的默认分隔符从空格改为vbNewLine，确保每个单元格内容后自动换行
• 新增空单元格判断，跳过无内容的单元格，解决此前出现的连续空行问题
• 移除邮件主题中的换行符，因为Outlook主题不支持换行，原代码中的vbCrLf会被无效化

内容的提问来源于stack exchange，提问作者brendan rayburn