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
    Posts
    1
    Articles
    0
    Excel Version
    2010

    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
    Location
    British Columbia
    Posts
    793
    Articles
    0
    Excel Version
    Excel 2010
    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

  3. #3
    Conjurer NormS's Avatar
    Join Date
    Jul 2017
    Posts
    117
    Articles
    0
    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

    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

Posting Permissions

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