基于条件按行发邮件的Excel VBA问题：代码运行无反应无报错

Hey there! Let’s dig into why your adapted Ron de Bruin VBA script is running silently without sending emails or updating column V. When macros fail without errors, it’s almost always a hidden issue with data targeting, Outlook permissions, or untriggered logic—let’s troubleshoot step by step.

First, make sure your code is actually targeting rows with data and correctly skipping empty email addresses. If your range is defined wrong (like hardcoding a row limit that’s too low, or checking the wrong column for emails), the macro might loop through nothing at all.

• Replace hardcoded row limits with a dynamic range to capture all your data:

  Dim lastRow As Long
  lastRow = Cells(Rows.Count, "A").End(xlUp).Row ' Adjust column A to your key data column
  For i = 2 To lastRow ' Start at row 2 assuming row 1 is headers
  

• Double-check your empty email check matches your email column (e.g., column B):

  If Trim(Cells(i, "B").Value) = "" Then
      Cells(i, "V").Value = "no email" ' Optional: mark empty rows for clarity
      GoTo NextRow
  End If
  

Ron’s scripts rely on Outlook, and sometimes security settings block the macro without showing an error. Here’s how to confirm:

• Add a quick test message at the start of your macro to ensure it’s running:

  MsgBox "Macro started! Checking rows now."
  

  If this doesn’t pop up, your macro isn’t enabled or you’re running the wrong Sub.
• If the message appears but no emails show up, temporarily enable "Enable all macros" (Excel Options > Trust Center > Trust Center Settings > Macro Settings) to test if security is blocking it. Revert this after testing for safety.
• Ensure Outlook is initialized properly—add this to avoid silent failures if Outlook isn’t running:

  Dim OutApp As Object
  On Error Resume Next
  Set OutApp = GetObject(, "Outlook.Application")
  If Err.Number <> 0 Then Set OutApp = CreateObject("Outlook.Application")
  On Error GoTo 0
  

Add debug checks to see if your code is reaching the email creation step. Inside your loop (after the empty email check):

Debug.Print "Processing row " & i & " | Email: " & Cells(i, "B").Value
MsgBox "About to create email for: " & Cells(i, "B").Value ' Optional visual check

Open the VBA Immediate Window (Ctrl+G) to view the debug prints. If you don’t see these, your code is skipping the email block—double-check any conditions you added.

Even if emails aren’t sending, check if column V updates. If it doesn’t, your loop isn’t reaching that line. Make sure you have:

Cells(i, "V").Value = "sent"

And that there’s no premature GoTo NextRow before this line.

Here’s a cleaned-up version with all the checks above, tailored to your needs:

Sub SendPersonalizedEmails()
    Dim OutApp As Object, OutMail As Object
    Dim lastRow As Long, i As Long
    Dim emailAddr As String, userName As String
    
    ' Get last row with data (adjust column A to your main data column)
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    ' Initialize Outlook
    On Error Resume Next
    Set OutApp = GetObject(, "Outlook.Application")
    If Err.Number <> 0 Then Set OutApp = CreateObject("Outlook.Application")
    On Error GoTo 0
    
    ' Loop through rows
    For i = 2 To lastRow
        emailAddr = Trim(Cells(i, "B").Value) ' Adjust B to your email column
        
        ' Skip empty emails
        If emailAddr = "" Then
            Cells(i, "V").Value = "no email"
            GoTo NextRow
        End If
        
        ' Pull personalized data (adjust columns to match your sheet)
        userName = Cells(i, "C").Value
        
        ' Create email
        Set OutMail = OutApp.CreateItem(0)
        With OutMail
            .To = emailAddr
            .Subject = "Hi " & userName & ", Your Custom Subject Here"
            .Body = "Dear " & userName & "," & vbNewLine & vbNewLine & _
                    "This is your personalized message tailored to row " & i & "." & vbNewLine & _
                    "Best regards," & vbNewLine & "Your Name"
            .Display ' Use .Send instead of .Display to send automatically
        End With
        
        ' Mark as sent
        Cells(i, "V").Value = "sent"
        
NextRow:
    Next i
    
    ' Cleanup
    Set OutMail = Nothing
    Set OutApp = Nothing
    MsgBox "Email process finished!"
End Sub

• Adjust column references (B for emails, C for names) to match your spreadsheet.
• Use .Display instead of .Send first to test emails without sending them.

• Make sure Outlook is open when running the macro (some versions require this).
• Check your Outlook "Sent Items" folder—sometimes emails send without a pop-up.
• Add error handling to catch hidden issues:

  On Error GoTo ErrorHandler
  ' ... your code ...
  

ErrorHandler:
MsgBox "Error on row " & i & ": " & Err.Description
Resume NextRow

内容的提问来源于stack exchange，提问作者R.E.L.