Excel 宏 - 自动电子邮件

Question

我正在 excel 中设置一个宏，以便在更新单元格时自动发送电子邮件。是否可以在电子邮件正文中包含单元格的内容？例如，如果单元格 G7 已更新，请在电子邮件中包含单元格 B7 的内容？单元格行将是相同的，列会改变。

这是我正在使用的代码:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xRgSel As Range
    Dim xOutApp As Object
    Dim xMailItem As Object
    Dim xMailBody As String
    On Error Resume Next
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Set xRg = Range("G2:G17")
    Set xRgSel = Intersect(Target, xRg)
    ActiveWorkbook.Save
    If Not xRgSel Is Nothing Then
        Set xOutApp = CreateObject("Outlook.Application")
        Set xMailItem = xOutApp.CreateItem(0)
        xMailBody = "Hello," & vbCrLf & vbCrLf & "The worksheet " & Chr(34) & ActiveWorkbook.Sheets(3).Name & Chr(34) & "has been completed and ready for 1st level review."

        With xMailItem
            .To = ""
            .Subject = ""
            .Body = xMailBody
            .Display
        End With
        Set xRgSel = Nothing
        Set xOutApp = Nothing
        Set xMailItem = Nothing
    End If
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

该宏运行良好，但我想摆脱工作表名称，而是在单元格 B2 中显示消息(如果单元格 G2 已更新。

Answer 1

只是为了结束这个问题:您可以使用 Target.Row 获取该行.

所以按照你的例子，你可以引用B2使用

Me.Cells(Target.Row, 2)

也许

Me.Range("B" & Target.Row)