vba - 无法弄清楚 Object required 错误

Question

该代码的目的是向地址列表发送电子邮件。为了确定起点，将出现一个用户表单，询问说明列和行(随着我的改进计划，我将添加其他选项)。

我让代码正常工作，但是，我进行了一些调整，并没有不断收到 Object required 错误，我已经尝试了几个小时没有运气。请你看看我的代码并提出我可能出错的地方吗？

注意。我也尝试声明所有变量，但它没有解决问题。

错误在循环直到 username.Value = ""

Sub cmdGo_Click()

Application.DisplayAlerts = False

i = cmbRow

If i = "" Then
    Exit Sub
End If

username = cmbColumn

If username = "" Then
    Exit Sub
End If

Select Case username

    Case "A", "a"
        username = Cells(i, "a").Value
    Case "B", "b"
        username = Cells(i, "b").Value
    Case "C", "c"
        username = Cells(i, "c").Value
    Case "D", "d"
        username = Cells(i, "d").Value
    Case "E", "e"
        username = Cells(i, "e").Value

End Select

Do

Dim OutApp As Object
    Dim OutMail As Object
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
    With OutMail

        .To = username
        .CC = ""
        .BCC = ""
        .Importance = 1
        .Subject = "Hello"
        .HTMLBody = "Message"

'display shows each email before sending
          .Display
'send sends email automatically
'         .Send

    End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing

Application.DisplayAlerts = True

i = i + 1

Loop Until username.Value = ""

End Sub

Answer 1

代码中几乎没有语法和理解错误。我在下面列出了它们，并对您的代码进行了一些重构，以帮助使其按您的意愿工作。

最好使用 Option Explicit在每个模块的顶部和 明确 声明具有所需类型的变量

将用户名设置放在循环中，以便每次使用基于 i = i+1 的新电子邮件重置柜台。

其余注释在代码中:

Option Explicit

Sub cmdGo_Click()

Application.DisplayAlerts = False

If cmbRow = "" or cmbColumn = "" Then 
    Exit Sub
End If

Dim i As Long
i = cmbRow

Dim UserNameCol As String 'created a new variable just to get column letter so can be used later in the loop and removed the `Select Case` block.
UserNameCol = cmbColumn

'set outlook outside loop since you only need to call it once, doing it in loop creates unneccesary processing
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")

Do Until Len(Cells(i, UserNameCol).Value) = 0 'will stop when blank cell appears

    Dim UserName As String
    UserName = Cells(i, UserNameCol).Value 'always will user whatever column choosen

    Set OutMail = OutApp.CreateItem(0) 'this goes here because a new email is needed each time
    On Error Resume Next
    With OutMail

        .To = UserName
        '.CC = "" 'you can remove this lines because you are not putting anything in the field
        '.BCC = "" 'you can remove this lines because you are not putting anything in the field
        .Importance = 1
        .Subject = "Hello"
        .HTMLBody = "Message"

        'display shows each email before sending
        .Display
        'send sends email automatically
        '.Send

    End With
    On Error GoTo 0

    i = i + 1

Loop 

'destroy outlook when finished processing all mails
Set OutMail = Nothing
Set OutApp = Nothing

Application.DisplayAlerts = True

End Sub