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!
Thank you in advance for taking time of your day to assist!

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