worksheet name Runtime Error 9

MinMax

New Member
Joined
Jan 14, 2021
Messages
4
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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,072
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
I don't get an error if the sheet is named Sheet2 (the name that appears on the sheet tab).
 

MinMax

New Member
Joined
Jan 14, 2021
Messages
4
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.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,072
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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:

MinMax

New Member
Joined
Jan 14, 2021
Messages
4
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.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,072
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Your welcome, happy it helped,
 

Watch MrExcel Video

Forum statistics

Threads
1,130,323
Messages
5,641,531
Members
417,215
Latest member
Diaryman

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
Top