Copy cells if condition met and paste into body of email

J

jmcconnell

New member
Joined
Jun 6, 2020
Messages
1
Reaction score
0
Points
0
Excel Version(s)
2010
[FONT=&quot]Hi. I'm a beginner so bare with me.[/FONT]

[FONT=&quot]I've got a worksheet called curtailments and I'm working with the data below:[/FONT]

[FONT=&quot]
Site nameUnit IDAgentEntity(MW)Start timeStart dateCease timeCease Date
Batsworthy CrossODFM44-01TestCo1Entity110010:0005/05/202016:0005/05/2020
Denzel DownsODFM45-01TestCo1Entity120011:0005/05/202015:0005/05/2020
ForssODFM42-01TestCo1Entity1010:0005/05/202016:0005/05/2020
Little RaithODFM80-01TestCo1Entity1011:0005/05/202015:0005/05/2020
[/FONT]

[FONT=&quot]The table contents may not always be in this order and the date and times can vary. Therefore I would like to copy the 'Site name', 'start date' and 'time' if for example the table contains 'Forss' and paste them into the body of an email. I know the code on how to access outlook etc.[/FONT]

[FONT=&quot]I can get it to copy the entire row and I can paste that row into another worksheet (There's plenty of examples online) but I can't copy specific cells and get them into the body of an email.[/FONT]

[FONT=&quot]Any help would be much appreciated![/FONT]
 
Don't know how you're getting to Forss but with Forss selected see if something like this helps
Code: 
Sub testing()
    Dim str As String


    With Selection.EntireRow
        str = .Cells(1).Value & "  " & .Cells(7).Value & "  " & .Cells(6).Text
    End With


    MsgBox str


End Sub
 
If your data is in an Excel table, aka listobject, you can filter it then pull out the entries you need

Code: 
Sub FilterTable()

Dim lo As ListObject, strDate As String

Set lo = ActiveSheet.ListObjects("Table2")
lo.Range.AutoFilter Field:=lo.ListColumns("Site name").Index, Criteria1:="Forss"

If lo.DataBodyRange.SpecialCells(xlCellTypeVisible).Rows.Count = 1 Then
    strDate = Intersect(lo.ListRows(1).Range, lo.ListColumns("Start date").DataBodyRange)
    MsgBox "Start date is " & strDate
End If

lo.AutoFilter.ShowAllData

End Sub
 
You must log in or register to reply here.
Back
Top