CODEWITHSUNDEEP
vbaexcelemailrange
user1234
user1234

Reputation: 121

Display a Range part of the email body vba

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

Upvotes: 0

Views: 189

Answers (1)

Vityata
Vityata

Reputation: 43585

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

Upvotes: 3

Related Questions