Copying data from sheet into VBA module

Afro_Cookie

Board Regular
Joined
Mar 17, 2020
Messages
103
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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
VBA Code:
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
 
Last edited by a moderator:

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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.
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,217
Members
452,619
Latest member
Shiv1198

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top