仅在硬编码范围内的值处理单元格

Question

我有代码将电子邮件起草给列中的所有收件人。

    Sub Send_Bulk_Mail()
    Dim objOutlook As Object
    Dim objMail As Object
    Dim ws As Worksheet
    Set objOutlook = CreateObject("Outlook.Application")
    Set ws = ActiveSheet
  For Each cell In ws.Range("G2:G100")
    Set objMail = objOutlook.CreateItem(0)
        With objMail
            .To = cell.Value
            .Subject = "This is the test subject"
            .Body = "This is the test email body"
            .Send
        End With
        Set objMail = Nothing
    Next cell
    Set ws = Nothing
    Set objOutlook = Nothing
End Sub

当G列中的行比100少时，起草了所有电子邮件后会有错误。

上一封电子邮件后如何自动停止？

这是示例的链接，单击此处

Answer 1

或简单地使用range.end属性(excel(

Dim rCell As Range
For Each rCell In Ws.Range("G2", Ws.Range("G100").End(xlUp))
     'code here 
Next rCell

---

完整示例

Option Explicit
Sub Send_Bulk_Mail()
    Dim objOutlook As Object
    Dim objMail As Object
    Dim Ws As Worksheet
    Set objOutlook = CreateObject("Outlook.Application")
    Set Ws = ActiveSheet
    Dim rCell As Range
    For Each rCell In Ws.Range("G2", Ws.Range("G100").End(xlUp))
        Debug.Print rCell.Address
        Set objMail = objOutlook.CreateItem(0)
        With objMail
            .To = rCell.Value
            .Subject = "This is the test subject"
            .Body = "This is the test email body"
            .Send
        End With
    Next rCell
    Set objMail = Nothing
    Set Ws = Nothing
    Set objOutlook = Nothing
End Sub

---

请参见更多示例此处https://stackoverflow.com/a/48497589/4539709

Answer 2

对Cell进行检查，以查看是否在尝试使用之前是否没有。

If Not cell Is Nothing Then
  If cell.Value <> "" Then
    Set objMail = objOutlook.CreateItem(0)
    With objMail
        .To = cell.Value
        .Subject = "This is the test subject"
        .Body = "This is the test email body"
        .Send
    End With
  End If
End IF