Results 1 to 3 of 3

Thread: Copy cells if condition met and paste into body of email

  1. #1
    Neophyte jmcconnell's Avatar
    Join Date
    Jun 2020
    Excel Version

    Copy cells if condition met and paste into body of email

    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi. I'm a beginner so bare with me.

    I've got a worksheet called curtailments and I'm working with the data below:

    Site name Unit ID Agent Entity (MW) Start time Start date Cease time Cease Date
    Batsworthy Cross ODFM44-01 TestCo1 Entity1 100 10:00 05/05/2020 16:00 05/05/2020
    Denzel Downs ODFM45-01 TestCo1 Entity1 200 11:00 05/05/2020 15:00 05/05/2020
    Forss ODFM42-01 TestCo1 Entity1 0 10:00 05/05/2020 16:00 05/05/2020
    Little Raith ODFM80-01 TestCo1 Entity1 0 11:00 05/05/2020 15:00 05/05/2020

    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.

    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.

    Any help would be much appreciated!

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    British Columbia
    Excel Version
    Excel 2010
    Don't know how you're getting to Forss but with Forss selected see if something like this helps
    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

  3. #3
    Conjurer NormS's Avatar
    Join Date
    Jul 2017
    Excel Version
    Excel 2016 ProPlus
    If your data is in an Excel table, aka listobject, you can filter it then pull out the entries you need

    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
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts