How do I get a worksheet object using Outlook VBA?

Question

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.

@Ralph Sheet1 will work if the sheet is indeed named Sheet1, which it is by default. — Scott Marcus, Feb 16 '16 at 23:34
The code you've shown in working with outlook and the line you've shown giving you the problem is working with Excel. Can you show the complete code? — Scott Marcus, Feb 16 '16 at 23:36
@Ralph Just tried it and im getting an error that says "Sub or function not defined" — Justin Case, Feb 16 '16 at 23:37
@ScottMarcus Sorry, my mistake. Deryl: Are you looking for the last row in a particular column? Then this solution should prove helpful: http://stackoverflow.com/questions/18952362/excel-vba-select-range-at-last-row-and-column — Ralph, Feb 16 '16 at 23:38
@deryl That implies that you haven't referenced the excel library correctly. Can you post all of your code and clarify if you are writing your VBA in Outlook or Excel. — Scott Marcus, Feb 16 '16 at 23:39
@ScottMarcus just edited the post to provide you with more info. Im running the code in outlook and excel is open. Haven't done anything with excel except opened it — Justin Case, Feb 16 '16 at 23:41
@Ralph no sorry. I'm trying to code to automatically email people by getting the email addresses from the first column of a spreadsheet. All the email addresses will be in the first column — Justin Case, Feb 16 '16 at 23:42
@deryl In Outlook, 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. Also, please post all of your code, soi we can see if you are using the Excel object model correctly. — Scott Marcus, Feb 16 '16 at 23:42
Error is gone! Thank you. I posted the whole thing and feel free to criticize it. — Justin Case, Feb 16 '16 at 23:46
@deryl Glad to help. By the way, you may not need this line Set olApp = CreateObject("Outlook.Application") since you are already in outlook. Instead try replacing olApp with Application. Also, would you mind marking the answer below as correct and voting it up? — Scott Marcus, Feb 16 '16 at 23:48

score 3 · Accepted Answer · answered Feb 16 '16 at 23:49

3

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.

answered Feb 16 '16 at 23:49

Scott Marcus

64,069
6
49
71

How do I get a worksheet object using Outlook VBA?

1 Answers1