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