0

I would like to add a range from my sheet to the body of an email which is generated but vba. I keep getting errors and any help would be appreciated

myRange = Range("A12:A12.End(xlDown)").SpecialCells(xlCellTypeVisible)

'This will extract the info for the worksheet
'This extracts the subject title from the worksheet

EmailSubject = "STOCKS " & Type1 & " - " & Left(Trade, 6) & " [" & _ 
               Range("A12").Value & "/" & Range("A12").End(xlDown).Value _
               & "]: % at %"

Body = "<b>" & Range("A8").Value & "</b><br/>" & Range("A9").Value & "<br/>" _ 
       & Range("A10").Value & "<br/><br/>" _
       & myRange
user1234
  • 111
  • 9
  • The code snippet as is doesn't even compile, specifically, the `EmailSubject...` part. You need a "_" at the end of the line. – Miqi180 May 29 '18 at 11:34
  • its all in one line, its just the editor here that cut it – user1234 May 29 '18 at 11:37
  • Sounds strange, but you really should edit your post then. Generally, posting code that doesn't even compile is a big no no. – Miqi180 May 29 '18 at 11:40

1 Answers1

3

Your error is in the way you are declaring the range.

  • Range is an object and it has to be Set
  • End(xlDown) cannot be passed as a String
  • Try to define the start and the end cell of the range and then the range is going to be defined correctly:
Option Explicit

Sub TestMe()

    Dim myRange As Range
    Dim firstCell   As Range
    Dim lastCell As Range

    Set firstCell = Range("A12")
    Set lastCell = firstCell.End(xlDown)
    Set myRange = Range(firstCell, lastCell).SpecialCells(xlCellTypeVisible)

End Sub

Once you manage to have the correct range, it would be easier - Paste specific excel range in outlook

Vityata
  • 42,633
  • 8
  • 55
  • 100
  • @theudster - simply take the answer from [here](https://stackoverflow.com/a/18683610/5448626) and declare your range as in my answer. It should work. – Vityata May 29 '18 at 13:23