CODEWITHSUNDEEP
excelvbaoutlook
Justin Case
Justin Case

Reputation: 787

How do I get a worksheet object using Outlook VBA?

I'm trying to get all the cells from my Excel worksheet in column 1.

My code throws an error.

"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

How do I get a worksheet object?

I tried

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

but this also throws an error.

I'm running the code in Outlook and have an open Excel window.

Upvotes: 1

Views: 359

Answers (1)

Scott Marcus
Scott Marcus

Reputation: 65806

You will need to add a reference to the Excel object library, which is done in the VBA editor, under Tools / Add References. Just having Excel open isn't enough.

Upvotes: 3

Related Questions