vba 从 Excel 发送多封电子邮件

Sending multiple emails from Excel

提问by Trace

I have a workbook that has 7 worksheets. I have the below vba to send an email once a value is met on a particular sheet.

我有一个包含 7 个工作表的工作簿。一旦在特定工作表上满足值，我就有以下 vba 发送电子邮件。

Each sheet has a different value and a different attachment to be sent. How do I add a code for each sheet so the email is sent?

每张纸都有不同的值和不同的要发送的附件。如何为每张纸添加代码以便发送电子邮件？

Thanks in advance

提前致谢

Set as General (Declarations)

设为一般（声明）

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Application.Intersect(Range("M4:M368"), Target) Is Nothing Then
        If IsNumeric(Target.Value) And Target.Value < 3500 Then
            Call Fuel_LevelW03
        End If
    End If
End Sub

followed by a module General Fuel_LevelW03

后跟一个模块 General Fuel_LevelW03

Sub Fuel_LevelW03()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    strbody = "Hi" & vbNewLine & vbNewLine & _
              "Please order fuel as attached." & vbNewLine & _
              "" & vbNewLine & _
              "Kind Regards" & vbNewLine & _
              ""

    On Error Resume Next
    With OutMail
        .To = "email address"
        .CC = "email address"
        .BCC = ""
        .Subject = "Fuel Order W03"
        .Body = strbody
        .Attachments.Add ("H:\Fuel Order Sheets\Glen Eden W03 Pump Station.xlsx")
        .Send
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

采纳答案by Bernard Saucier

From what I understand, you try to "tell the method" a bit about what the Target.Valuewas. Just pass the parameter to the function like this :

据我所知，您尝试“讲述方法”一点关于它是什么Target.Value。只需将参数传递给函数，如下所示：

If IsNumeric(Target.Value) Then
    If Target.Value < 3500 Then
        Call Fuel_LevelW03( Sh.Name, Target.Value )
    End If
End If

and change the function's name with this one :

并用这个更改函数的名称：

Fuel_LevelW03( sheetName as String, targetValue as String )
                                                   'Change String to appropriate type

---

---

EDIT2 :I changed the code around a bit, if you need any help let me know.

EDIT2：我稍微更改了代码，如果您需要任何帮助，请告诉我。

EDIT :Ok, here's how you solve this. Inside the "ThisWorkbook" code object (underneath the sheet code objects, on the left side of the code editor), paste this :

编辑：好的，这是您解决此问题的方法。在“ThisWorkbook”代码对象内（在代码编辑器左侧的工作表代码对象下方），粘贴以下内容：

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Application.Intersect(Range("M4:M368"), Target) Is Nothing Then
        If IsNumeric(Target.Value) And Target.Value < 3500 Then
            Call Fuel_LevelW03( Sh.Name )
        End If
    End If
End Sub

Sub Fuel_LevelW03( sheetName as String )
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)


    On Error Resume Next

    If sheetName = "Sheet1" Then 'Replace Sheet1 with the name of your worksheet

        strbody = "Hi" & vbNewLine & vbNewLine & _
              "Please order fuel as attached." & vbNewLine & _
              "" & vbNewLine & _
              "Kind Regards" & vbNewLine & _
              "STRING BODY1"

        With OutMail
            .To = "email address"
            .CC = "email address"
            .BCC = ""
            .Subject = "Fuel Order W03"
            .Body = strbody
            .Attachments.Add ("H:\Fuel Order Sheets\Glen Eden W03 Pump Station.xlsx")
            .Send
        End With
        On Error GoTo 0

    ElseIf sheetName = "Sheet2" Then 'Replace Sheet2 with the name of the next sheet and 

        'Put the same content as the first IF statement, but adapted to "Sheet2"

    ElseIf sheetName = "Sheet3" Then 'Replace Sheet3 with the name of the next sheet and 

        'Put the same content as the first IF statement, but adapted to "Sheet3"

    ElseIf sheetName = "Sheet4" Then 'Replace Sheet4 with the name of the next sheet and 

        'Put the same content as the first IF statement, but adapted to "Sheet4"

    'ElseIf ............. (So on, so forth)


    End If

    Set OutMail = Nothing
    Set OutApp = Nothing

End Sub

You can add as many ElseIf's as you want (one for each sheet)

您可以根据需要添加任意数量的ElseIf's（每张纸一个）

---

---

Am pretty sure this is what you need, although am not sure.

我很确定这是你需要的，虽然我不确定。

If ActiveSheet.Name = "Sheet1" Then

    'Do something specific to "Sheet1"

ElseIf ActiveSheet.Name = "Sheet2" Then

    'Do something specific to "Sheet2"

    'And so on so forth...

End If

You have a button to that macro in each sheet, and depending on the sheet calling the macro, you want a different e-mail to be sent, right? Then this will do it. You can add as many ElseIf's as you want.

您在每个工作表中都有一个指向该宏的按钮，并且根据调用宏的工作表，您希望发送不同的电子邮件，对吗？然后就可以了。您可以根据需要添加任意数量的ElseIf。