Copying data from sheet into VBA module

[Afro_Cookie]

My end goal is to generate an e-mail with specific cell data, based of its value. I have a macro the selects all cells in A:A that are >=14.

I want to take this selection and the 2 cells to the right of it, add it to my email body so that it can be sent. I have no idea how to add this data into a VBA module, if it's even possible.

Any help would be appreciated.

Sub gather()
' This is to select all the cells in A:A that are >=14
Dim ws As Worksheet
Dim Selectcells As Range
Dim xcell As Object
Dim Rng As Range

Set ws = Worksheets("Sheet1")
Set Rng = ws.Range("A:A")

For Each xcell In Rng
    If xcell.Value >= 14 Then
        If Selectcells Is Nothing Then
        Set Selectcells = Range(xcell.Address)
        Else
        Set Selectcells = Union(Selectcells, Range(xcell.Address))
        End If
    End If
Next

Selectcells.Select

Sub notice()
Dim Email As Outlook.Application
Set Email = New Outlook.Application
Dim Sr As String
Dim newmail As Outlook.MailItem
Set newmail = Email.CreateItem(olMailItem)

newmail.To = "test@gmail.com"
newmail.CC = "test2@gmail.com"
newmail.Subject = "Random subject, related to data"

newmail.HTMLBody = "Below data needs to be addressed" & vbNewLine & vbNewLine & "This is where the selected data should be pasted" _
& vbNewLine & "Regards," & vbNewLine & "My name"

Sr = ThisWorkbook.FullName

newmail.Attachments.Add Sr

newmail.Send

End Sub

 

[JLGWhiz]

I think you will probably have to create a new temproary workbook to paste the data into as an attachment since you are dealing with non contiguous ranges. See this link for some advice by an expert.

Mail Range or Selection

www.rondebruin.nl

 

[Afro_Cookie]

perfect! Thanks JLGWhiz

 

[JLGWhiz]

perfect! Thanks JLGWhiz

You're welcome,
Regards, JLG