Copy first visible cell in column D to email recipient bar

[Jonlowery]

As the title indicates I am trying to find a way to copy the first visible email address in a given column to the recipient bar inside of excel. I had no problem setting who the email would be CCd to as it is static. Further I have been able to copy from a cell into another cell in a dynamic manner based off of the data available, however copying into the outlook recipient bar is proving to be more of a challenge. Any advice you can give or resources you could point me towards perusing would be greatly appreciated!

Thank you in advance for taking time of your day to assist!

Sub Send_Range()
   ActiveSheet.Range("F12").Select
   With Worksheets("Pledge Notifications").AutoFilter.Range
       ActiveCell.Value2 = Range("A" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Value2
       End With
   Dim sht As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range
Set sht = Worksheets("pledge notifications")
Set StartCell = Range("F8")
  Worksheets("Sheet1").UsedRange
  LastRow = sht.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  sht.Range("F8:N" & LastRow).Select
  Selection.Copy
   ActiveWorkbook.EnvelopeVisible = True
   With ActiveSheet.MailEnvelope
      .Item.cc = "myname@abc.com"
      .Item.Subject = "Upcoming Redemption and Pledge Release Request-#NOSEC"
      .Item.Display
   End With
End Sub