excel - 如何使用 Outlook VBA 获取工作表对象？

Question

我正在尝试从我的 Excel 工作表第 1 列中获取所有单元格。
我的代码抛出错误。

"object required"

Public Sub emailList()
    'Setting up the Excel variables.
    Dim olApp As Object
    Dim olMailItm As Object
    Dim iCounter As Integer
    Dim Dest As Variant
    Dim SDest As String
       
    'Create the Outlook application and the empty email.
    Set olApp = CreateObject("Outlook.Application")
    Set olMailItm = olApp.CreateItem(0)
       
    'Using the email, add multiple recipients, using a list of addresses in column A.
    With olMailItm
        SDest = ""
        For iCounter = 1 To WorksheetFunction.CountA(Workbooks("Book1.xls").Sheets(1).Columns(1))
            If SDest = "" Then
                SDest = Range.Cells(iCounter, 1).Value
            Else
                SDest = SDest & ";" & Range.Cells(iCounter, 1).Value
            End If
        Next iCounter
           
        'Do additional formatting on the BCC and Subject lines, add the body text from the spreadsheet, and send.
        .BCC = SDest
        .Subject = "FYI"
        .Body = ActiveSheet.TextBoxes(1).Text
        .Send
    End With
       
    'Clean up the Outlook application.
    Set olMailItm = Nothing
    Set olApp = Nothing
End Sub

如何获取工作表对象？
我试过了

Workbooks("Book1.xls").Sheet1.Columns(1)

但这也会引发错误。
我在 Outlook 中运行代码并打开了一个 Excel 窗口。

Answer 1

您将需要添加对 Excel 对象库的引用，这是在 VBA 编辑器中的工具/添加引用下完成的。仅仅打开 Excel 是不够的。