Consulting

Results 1 to 2 of 2

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

  1. #1

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

    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
    Last edited by Paul_Hossler; 10-04-2018 at 07:30 PM.

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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"
    
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •