worksheet name Runtime Error 9

MinMax

New Member
Joined
Jan 14, 2021
Messages
5
Hi,

when I run this program, i get Run time Error 9. at first "set wsM"
am i doing something wrong?
when i change this to .activeworksheet, error dissapears.
this error is same across all the set functions.

please help.
VBA Code:
Sub Test()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim Signature As String
    Dim wbMain As Workbook
    Dim wsM As Worksheet
    Dim wsQ As Worksheet
    Dim wsB As Worksheet
    Dim Email_Subject As String
   
    Set wbMain = ThisWorkbook
    wbMain.Activate
    Set wsM = wbMain.Worksheets("Sheet2")
    Email_Subject = wsM.Range("H2").Value & " Acceptance"
   
   
'    Set wsQ = wbMain.Worksheets("Sheet4")
'    Set wsB = wbMain.Worksheets("Sheet9")
   

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
   
  

    strbody = "<html>" & _
    "<style>" & "table,th,td" & "{border: 1px solid black ; border-collapse: collapse}" & "</style>" & _
    "<table style=" & "width:50%" & ">" & _
     "<tr>" & "<td>" & Range("A1") & "</td>" & "<td>" & Range("B1") & "</td>" & "<td>" & Range("C1") & "</td>" & "</tr>" & _
     "<tr>" & "<td>" & Range("A2") & "</td>" & "<td>" & Range("B2") & "</td>" & "<td>" & Range("C2") & "</td>" & "</tr>" & _
     "<tr>" & "<td>" & Range("A3") & "</td>" & "<td>" & Range("B3") & "</td>" & "<td>" & Range("C3") & "</td>" & "</tr>" & _
     "<tr>" & "<td>" & Range("A4") & "</td>" & "<td>" & Range("B4") & "</td>" & "<td>" & Range("C4") & "</td>" & "</tr>" & "</table>" & "</html>"

    With OutMail
        .To = "[EMAIL]zzz.com@zzz.com[/EMAIL]"
        .CC = ""
        .BCC = ""
        .Subject = Email_Subject
        .HTMLBody = strbody
        .Display (True)
    End With

    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing

End Sub
 
Last edited by a moderator:

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
I don't get an error if the sheet is named Sheet2 (the name that appears on the sheet tab).
 
Upvote 0
I think I made a mistake of using code name as full it should be wb.worksheets(2)

I’ll try this

thanks for your help.
 
Upvote 0
I am afraid that you are getting a little bit confused...
What you are using in post 1 is the sheet name (the name on the sheets tab or inside the brackets when you look in the Project window)... Worksheets("Sheet2")

What you are using in post 3 is the sheets Index.... worksheets(2)

The sheets codename is the name outside the brackets when you look in the Project window and you would use it in code as Sheet2, no brackets, no quotes and it defaults to ThisWorkbook.

Perhaps the links below will explain it clearer

Sheet CodeNames: Reference Sheets in Excel Workbooks by Code Name (ozgrid.com)
Using sheet codenames in Microsoft Excel VBA (wiseowl.co.uk)
VBA Guide To Referencing Worksheet Tabs — TheSpreadsheetGuru

The comment in the box at the end of the Wiseowl page isn't actually correct, it is just more difficult.
 
Last edited:
Upvote 0
I am afraid that you are getting a little bit confused...
What you are using in post 1 is the sheet name (the name on the sheets tab or inside the brackets when you look in the Project window)... Worksheets("Sheet2")

What you are using in post 3 is the sheets Index.... worksheets(2)

The sheets codename is the name outside the brackets when you look in the Project window and you would use it in code as Sheet2, no brackets, no quotes and it defaults to ThisWorkbook.

Perhaps the links below will explain it clearer

Sheet CodeNames: Reference Sheets in Excel Workbooks by Code Name (ozgrid.com)
Using sheet codenames in Microsoft Excel VBA (wiseowl.co.uk)
VBA Guide To Referencing Worksheet Tabs — TheSpreadsheetGuru

The comment is the box at the end of the Wiseowl page isn't actually correct, it is just more difficult.
thanks for your help.
I was able to resolve my issue.
I used sheet codename direct, as sheet name is changing dynamically with other code from cell reference, so i wanted to use worksheet codename.

ran code, worked like charm.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top