带有Excel的Outlook Mail:带有正文和变量的问题

Question

我在让.body文字工作时遇到麻烦。

我可以在Outlook中获得它来创建该项目。" TO："one_answers"主题"是正确的，但身体文本不是。
我可以将其包括在" Hello"到"以下服务行："：" y = 2 to lr1"到下一个y。我希望它两者都包括在内。如何连接两个部分？- 解决了！

if匹配函数应与sht3.range（" c"＆amp; i）匹配sht1.range（" b"＆amp; y）。在" Y"循环中，Y从2到49，而变量我应该是恒定的，直到循环完成为止。然后，它应该移至下一个我，然后再做一次。这是不起作用的，因为主体文本包含所有可变的行。 - 解决！

Sub Test()
Dim OutApp As Object
Dim OutMail As Object
Dim Sht1 As Worksheet
Dim Sht2 As Worksheet
Dim Sht3 As Worksheet
Dim i As Long
Dim y As Long
Dim x As Long
Dim LR1 As Long
Dim LR2 As Long
Set Sht1 = ThisWorkbook.Sheets("Sheet1")
Set Sht2 = ThisWorkbook.Sheets("Sheet2")
Set Sht3 = ThisWorkbook.Sheets("Sheet3")
LR1 = Sht1.Cells(Sht1.Rows.Count, "B").End(xlUp).Row
LR2 = Sht3.Cells(Sht3.Rows.Count, "C").End(xlUp).Row
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
On Error GoTo Cleanup
For i = 2 To LR2
If Not IsEmpty(Sht3.Range("C" & i)) Then
Sht3.Activate
Sht3.Range("C" & i).Select
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
        With OutMail
            .To = Sht3.Range("C" & i).Offset(ColumnOffset:=1)
            .Subject = "OE input sheet " & Sht3.Range("C" & i) & ": Service Delivered = NO"
            .body = "Hello " & ActiveCell.Offset(ColumnOffset:=-1).Value & "." _
                & vbNewLine & vbNewLine & _
                "We have noticed, that you have indicated Service Deliverd = NO, while the service line still contains a value. " _
                & vbNewLine & vbNewLine & _
                "We are referring to the following service lines:" _
                For y = 2 To LR1
                Sht1.Activate
                Sht1.Range("B" & y).Select
                    If Sht1.Range("B" & y) = Sht3.Range("C" & i) Then
                        .body = .body & vbNewLine & ActiveCell.Offset(ColumnOffset:=1).Value
                    End If
                Next y
            .Save
        End With
        On Error GoTo 0
        Set OutMail = Nothing
    End If
Next i
Cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub

预先感谢

Answer 1

mailItem的.body属性不会附加到身体中的其他信息，它完全替换了身体中的信息。

因此，当您第一次将其设置为" Hello"部分时，电子邮件主体包含此信息。当您再次将其设置在循环中时，它现在仅包含您将其设置为循环的信息。

更新循环中的行：

    .body = .body & vbNewLine & ActiveCell.Offset(ColumnOffset:=1).Value

它将将新信息附加到电子邮件主体。