Trying to store a range in variable, to use in email

[Dtex20]

Sub approvePurchase(control As IRibbonControl)

Dim rng As Range
Set rng = Nothing


If (ActiveSheet.Name) = "Purchase List" Then


     If Not Application.Intersect(ActiveCell, Range("I2:I600")) Is Nothing Then


      If ActiveCell.Value = "Pending" Then
      ActiveCell.Value = "Approved"
      Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(0, -8)).Copy
      Set rng = Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(0, -8)).SpecialCells(xlCellTypeVisible)
      
      With Worksheets("Approved Purchases")
      .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
      Application.CutCopyMode = False
      
      End With
      Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(0, -8)).ClearContents
      Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(0, 1)).ClearContents
      
      End If


     End If


Else


MsgBox "You Do Not Have Permission To Approve Purchases!"


Exit Sub
End If




End Sub

Hi guys,

I know this is a common question, but i don't know the answer for the way i want to use it. I've got the rest of the email macro Praise "Ron De Bruin", however i'm trying to copy the range i've just copied to another sheet into a variable "rng", to then use it in an email sub later on.

However i just can't seem to get it to store the range in a variable, if i can get this working i can use it in my email sub.

Thanks

 

[daverunt]

Most use the RangetoHtml function to copy a range to an email body as shown in Ron's examples as this takes away the pain.

The alternative is to concatenate each cell of the range into a string variable, which may be an option.

Here are 2 examples that I have used before for single columns or multiple columns.
The single column example also ignores blank cells in the column but you could remove that.

    strRng = ""
    For Each cell In Range("A1:A20")
    If Not cell.Value = "" Then
            strRng = strRng & cell.Value & vbNewLine
    End If
    Next
    MsgBox (strRng)

If it consists of more than 1 column you'll need to do a bit more work especially if you have a dynamic range.
But here's an example

FirstRow = 1
LastRow = rng.Rows.Count 
FirstCol = 1 
LastCol = rng.Columns.Count 

For r = FirstRow To LastRow
For c = FirstCol To LastCol
For Each cell In Cells(r, c)
strRng = strRng & "  " & cell.Value
Next
Next
strRng = strRng & vbNewLine
Next
MsgBox (strRng)