excel - 如果满足条件，如何从 excel 编辑我的 VBA 代码以在主题中包含特定行？

Question

如果相应的 FH 列等于已批准或待定，我需要知道如何将我的电子邮件主题行自动更改为 A 列中的名称。我有 2 封基于单元格值触发的电子邮件等于已批准和待处理，但如果 F、G、OR H 更改，我需要主题包含人名“A 列”

我在下面发布了我的代码。任何帮助将不胜感激。

Sub Approved()

Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
strbody = "Team," & vbNewLine & vbNewLine & _
"Approved. Update us when completed." & vbNewLine & _
"HR"

On Error Resume Next

With OutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "Term Request: 1/1 Test Approved"
.body = strbody
.Display

End With

On Error GoTo 0

Set OutMail = Nothing

Set OutApp = Nothing
End Sub

Private Sub CommandButton1_Click()
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
strbody = "Team," & vbNewLine & vbNewLine & _

On Error Resume Next

With OutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "Term Request: 1/1 Test"
.body = strbody
.Display

 End With

On Error GoTo 0

Set OutMail = Nothing

Set OutApp = Nothing
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("F3:H14")) Is Nothing Then

    Select Case Target.Value

    'if Pending: Send term Email
        Case "Pending"
                Call CommandButton1_Click

    'If Approved: Send Approval Email
        Case "Approved"
                Call Approved
    End Select

    End If
 End Sub

标签： excelvbaemailoutlook

Answer 1

首先，您需要遍历所有单元格，Target因为Target不一定是单个单元格，而是可以是多个单元格的范围，您的代码会在此失败。

您需要调整您的程序（例如Sub Approved），以便它们PersonName采用用于主题的参数：

Sub Approved(ByVal PersonName As String)
    'your code …

    .Subject = "Term Request: 1/1 Test for " & PersonName

    'your code …
End Sub

最后，您需要阅读Worksheet_Change事件中更改单元格的 A 列，并将其作为参数提供给您的程序Approved PersonName:=Target.Parent.Cells(Cell.Row, "A")

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim AffectedRange As Range
    Set AffectedRange = Intersect(Target, Target.Parent.Range("F3:H14"))

    If Not AffectedRange Is Nothing Then
        Dim Cell As Range
        For Each Cell In AffectedRange.Cells 'loop through all changed cells

            Select Case Cell.Value
                Case "Pending"  'if Pending: Send term Email
                    CommandButton1_Click PersonName:=Target.Parent.Cells(Cell.Row, "A").Value

                Case "Approved" 'If Approved: Send Approval Email
                    Approved PersonName:=Target.Parent.Cells(Cell.Row, "A").Value
            End Select

        Next Cell
    End If
End Sub