仅处理硬编码范围有值单元格及邮件代码自动终止问题咨询

Hey there! Let's break down how to solve both of your requirements clearly, assuming you're working with Excel VBA (since you're drafting emails from column G):

To ensure you only act on cells that actually contain a value within your target range, you'll want to add a check for empty values before processing each cell. This prevents wasted cycles on blank entries and avoids errors from trying to draft emails to empty recipients.

The root cause of your error is likely that your code is looping all the way to row 100, even when there are fewer valid recipients. Instead of hardcoding the end row to 100, we'll dynamically find the last row with data in column G, then cap it at 100 (to honor your hardcoded range limit). This way, the loop stops naturally after the last valid recipient.

Combined Solution Code #

Here's a revised version of your email drafting code that addresses both requirements:

Sub DraftRecipientEmails()
    Dim targetSheet As Worksheet
    Dim lastValidRow As Long
    Dim currentRow As Long
    Dim recipientEmail As String
    
    ' Set your target worksheet (update to your sheet name)
    Set targetSheet = ThisWorkbook.Sheets("YourSheetName")
    
    ' Get the last row with data in column G
    lastValidRow = targetSheet.Range("G" & targetSheet.Rows.Count).End(xlUp).Row
    
    ' Enforce hardcoded upper limit of 100 rows
    lastValidRow = WorksheetFunction.Min(lastValidRow, 100)
    
    ' Start from row 2 (adjust if your header is in a different row)
    For currentRow = 2 To lastValidRow
        recipientEmail = targetSheet.Range("G" & currentRow).Value
        
        ' Only process if the cell has a non-empty value (Requirement 1)
        If Trim(recipientEmail) <> "" Then
            ' --- Insert your existing email drafting code here ---
            ' Example Outlook draft code:
            ' Dim outlookApp As Object
            ' Dim newEmail As Object
            ' Set outlookApp = CreateObject("Outlook.Application")
            ' Set newEmail = outlookApp.CreateItem(0)
            
            ' With newEmail
            '     .To = recipientEmail
            '     .Subject = "Your Custom Subject"
            '     .Body = "Your email content goes here."
            '     .Save ' Saves as draft; use .Send to send immediately
            ' End With
            
            ' Clean up objects
            ' Set newEmail = Nothing
            ' Set outlookApp = Nothing
        End If
    Next currentRow
    
    ' Optional: Confirm completion
    MsgBox "All valid email drafts have been created!", vbInformation
End Sub

Key Details That Solve Your Issues: #

• Requirement 1: The If Trim(recipientEmail) <> "" Then check skips any blank or whitespace-only cells in your hardcoded range.
• Requirement 2: By calculating lastValidRow dynamically, we only loop through rows that actually have data. The WorksheetFunction.Min(lastValidRow, 100) ensures we never exceed your 100-row hard limit, even if there are more than 100 valid entries. This eliminates the error because the loop stops as soon as we process the last valid recipient.

If you're using a different language (like Python with openpyxl), the logic stays the same: find the last non-empty row in column G, cap it at 100, loop through each row, and only process cells with values.

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