根据每封电子邮件的 Excel 单元格值确定 Outlook 收件人

Question

获取 .To = 在列中填写信息。我正在尝试设置"已解决"在E列中的位置，然后设置为.To = 将在宏运行时填充 F 列中的相邻员工 ID。以下是我能够研究和工作的内容，但没有任何运气根据单元格值填充 To 字段。提前感谢任何帮助。在研究时找不到有关此确切情况的任何内容。

Sub Send_Email()
Dim rng As Range
For Each rng In Range("E2:E22")
If (rng.Value = "Resolved") Then
Call mymacro(rng.Address)
End If
Next rng
End Sub
Private Sub mymacro(theValue As String)
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "Hi, your issue has been resolved should issues persist please contact 611 for additional assistance."
On Error Resume Next
With xOutMail
.To = Cells().Value
.CC = ""
.BCC = ""
.Subject = "Your issue has been resolved."
.Body = xMailBody
.Display   ' using .Send for final version
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub

Answer 1

您想使用 Excel VBA 来实现 Outlook 邮件传递吗？

㞖 您可以使用以下方法获取范围内的电子邮件地址。

您可以使用Sheet1.Cells获取范围内的电子邮件地址。 电子邮件地址位于 RNG 对象(第一列(的同一行中。 表1.细胞(rng.行，1(。价值

调用 mymacro(值为字符串(方法时，将电子邮件地址传递给 Value 参数。

因此，在 mymacro(值为字符串(方法中，.应使用值参数。

Sub Send_Email()
Dim rng As Range
For Each rng In Range("C1:C4")
If (rng.Value = "2") Then
Call mymacro(Sheet1.Cells(rng.Row, 1).Value)
End If
Next rng
End Sub
Private Sub mymacro(theValue As String)
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "Hi, your issue has been resolved should issues persist please contact 611 for additional assistance."
On Error Resume Next
With xOutMail
.To = theValue
.CC = ""
.BCC = ""
.Subject = "Your issue has been resolved."
.Body = xMailBody
.Display   ' using .Send for final version
'.Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub