G列新增指定内容时自动触发Outlook邮件发送的技术问询

Got it, let's get this working for you! Instead of relying on a command button, we'll use Excel's built-in Worksheet_Change event to automatically trigger the email send whenever a specific value (like your order name) is entered in column G. Here's the adjusted code and breakdown:

Step 1: Replace Your Button Code with This Worksheet Event #

Open the VBA editor, navigate to the worksheet where you want this to work (not a standard module), and paste this code:

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Only run if the changed cell is in column G
    If Not Intersect(Target, Me.Range("G:G")) Is Nothing Then
        ' Check if the entered value matches your specified content (replace with your actual order name)
        If Target.Value = "Your Specific Order Name" Then
            Dim Email_Subject, Email_Send_From, Email_Send_To, Email_Cc, Email_Bcc, Email_Body As String
            Dim Mail_Object, Mail_Single As Variant
            
            ' Fill in your email details here
            Email_Subject = "Your Email Subject"
            Email_Send_From = "your.email@example.com"
            Email_Send_To = "recipient@example.com"
            Email_Cc = "cc.recipient@example.com"
            Email_Bcc = "bcc.recipient@example.com"
            Email_Body = "Your email body text here"
            
            ' Disable events temporarily to prevent infinite loops
            Application.EnableEvents = False
            
            On Error GoTo debugs
            Set Mail_Object = CreateObject("Outlook.Application")
            Set Mail_Single = Mail_Object.CreateItem(0)
            
            With Mail_Single
                .Subject = Email_Subject
                .To = Email_Send_To
                .CC = Email_Cc
                .BCC = Email_Bcc
                .Body = Email_Body
                .Send ' Use .Display instead of .Send if you want to preview the email first
            End With
            
debugs:
            ' Re-enable events regardless of success/failure
            Application.EnableEvents = True
            If Err.Description <> "" Then MsgBox "Error sending email: " & Err.Description
        End If
    End If
End Sub

Key Details to Adjust: #

• Specified Trigger Value: Replace "Your Specific Order Name" with the exact text that should trigger the email (e.g., "Order #12345").
• Email Details: Fill in all the Email_* variables with your actual subject, sender, recipients, and body text.
• Preview vs. Send: If you want to review the email before sending, replace .Send with .Display.

Important Notes: #

• Worksheet Module: Make sure you paste this code into the worksheet's code module (right-click the worksheet tab > View Code), not a standard module. This ensures the event only runs for that specific sheet.
• Macro Permissions: You'll need to enable macros in Excel, and ensure Outlook allows programmatic access (check Outlook's trust center settings if you get permission errors).
• Multiple Cells: If you need to handle pasted values across multiple G-column cells, add a loop through Target.Cells to check each entry individually.

内容的提问来源于stack exchange，提问作者mego4m