Subscript out of range on usedrange code--Attempting to copy range to html and back t

[AngelMike]

Hi All,

I am a VBA newbie and the code below is to create an outlook email and copy and paste a table from an excel range to an outlook email. I am getting Subscript out of range - error 9 on the following code below. Can you please assist? Thanks in advance!

Sincerely
Mike

Sub Email()
'Module created by Nona B.
'Purpose-  Create email and attach binder
'Date-Oct 2, 2018
Dim P As String
Dim wb As ThisWorkbook


Set wb = ThisWorkbook


Dim ws As Worksheet
Set ws = wb.Sheets("BookingGrid")
Dim new_wb As Workbook
Dim rng As Range
Set rng = Range("A1:B18")
Dim rng2 As Range
P = "C:\Users" & Environ("Username") & "\Desktop\tempfile.html"


Workbooks.Add
Set new_wb = ActiveWorkbook
MsgBox new_wb.Sheets("BookingGrid").UsedRange.Address




ThisWorkbook.Activate
rng.Copy


new_wb.Activate
ActiveCell.PasteSpecial xlPasteValues
ActiveCell.PasteSpecial xlPasteFormats
ActiveCell.PasteSpecial xlPasteColumnWidths


'new_wb.PublishObjects.Add (xlSourceRange.P.new_wb.Sheets("bookinggrid").Name,




Dim OLapp As Object
Dim oLMail As Object
Dim myattachments As Object
Dim olMailItem As Object
Dim myfilenamepath As String


Set OLapp = CreateObject("outlook.application")
'Set oLMail = OLapp.cREATEITEM(olMailItem)
Set olMailItem = OLapp.cREATEITEM(0)
Set myattachments = olMailItem.attachments


    With olMailItem
    .To = Distribution
    .CC = ccDistribution
    ''.Subject = "Test"
    .Subject = "Booking Sheet for" & "" & Range("A1").Value & "" & Range("B1").Value
    .Body = "This is a test"
    ''.attachments ()
    myfilenamepath = Application.GetOpenFilename()
    myattachments.Add myfilenamepath
    .Display
    End With


End Sub

[Paul_Hossler]

I added CODE tags to your macro - you can use the [#] icon to insert the CODE and /CODE tags to paste your macro between

1. It'd help if you said which line was getting the error

2.

P = "C:\Users" & Environ("Username") & "\Desktop\tempfile.html"

should probably be (added slash after Users)

P = "C:\Users\" & Environ("Username") & "\Desktop\tempfile.html"