VBA Macro - How to send a single email to recipients showing one or more times in an excel table

[AndreMateus]

I have a script (see below) that will generate an email from my secondary mailbox and copy a filtered range from a table and place a copy of the filtered table in the body of the email (the copied range is changed manually on the table before running the macro).
This code is not adding the filtered email address to the "to" field of the outlook email and it is also not listing the attachment that need to be sent to the correct recipient(s).

Basically, what I would like to see is that when manually filtering the table based on "requestor" or "email", the attachments listed in the active workbook (column G" should be added to the outlook email along with the 2 fixed documents that must always be added as atatchment.

Here's the current code:

Sub Soft_Token_Distribution()
Dim outlook As Object
Dim newEmail As Object
Dim xInspect As Object
Dim pageEditor As Object
Dim path As String


Set outlook = CreateObject("Outlook.Application")
Set newEmail = outlook.CreateItem(0)


With newEmail

.To = Sheet4.Range("L2").Text ' this line here needs to add the email address from the filtered active table of our recipient
.CC = ""
.BCC = ""
.Subject = "SOW Contingent Worker - Remote Access Request"
.HTMLBody = "Hi, <br/><br/> As part of your <b> Create, Modify or Terminate SOW Contingent Worker </b> ServiceNow request for the below user, you requested Remote Access for the user. <br/><br/> Vendor Remote Access has been provisioned for this user. Please share the attached documents with the user so that they can configure their Vendor Remote Access. <br/><br/> Regards,"

.attachments.Add "C:\Instructions.pdf"
.attachments.Add "C:\PIN Mode.pdf"
.attachments.Add "C:\RemoteAMBA\bin\SoftTokens\" & Range("g2").Value ' this line here needs to add all attachments listed in the active table for the current recipient only



Set .SendUsingAccount = outlook.Session.Accounts.Item(2)

.Display
Set xInspect = newEmail.GetInspector
Set pageEditor = xInspect.WordEditor

Sheet4.Range("Table2[#All]").Copy

pageEditor.Application.Selection.Start = 316
pageEditor.Application.Selection.End = pageEditor.Application.Selection.Start
pageEditor.Application.Selection.PasteAndFormat (wdFormatPlainText)

.Display
'.Send
Set pageEditor = Nothing
Set xInspect = Nothing
End With
Set newEmail = Nothing
Set outlook = Nothing
End Sub

SOW Contingent Worker - Work in Progress (003).xlsm
	B	C	D	E	F	G	H	I	J	K	L
1	Opened	Worker First Name	Worker Last Name	Contract Start Date	ID	Soft Token File Name	Cost Center	Domain	Remote Access	Requestor	Email
2	3/29/2023	First_Name_1	Last_Name_1	2023-04-12	ID_1	ID_1_000123456789.sdtid	3638	TDBFG	Yes	Andre Mateus	Andre.Mateus@test.com
3	3/29/2023	First_Name_2	Last_Name_2	2023-04-03	ID_2	ID_2_000123456789.sdtid	7983	TDBFG	Yes	Andre Mateus	Andre.Mateus@test.com
4	3/27/2023	First_Name_3	Last_Name_3	2023-03-24	ID_3	ID_3_000123456789.sdtid	9947	TDBFG	Yes	Andre Mateus	Andre.Mateus@test.com
5	3/29/2023	First_Name_4	Last_Name_4	2023-04-11	ID_4	ID_4_000123456789.sdtid	6084	TDBFG	Yes	Andre Mateus	Andre.Mateus@test.com
6	3/29/2023	First_Name_5	Last_Name_5	2023-04-03	ID_5	ID_5_000123456789.sdtid	3510	TDBFG	Yes	John Doe	John.Doe@test.com
7	3/29/2023	First_Name_6	Last_Name_6	2023-04-13	ID_6	ID_6_000123456789.sdtid	3930	TDBFG	Yes	John Doe	John.Doe@test.com
8	3/29/2023	First_Name_7	Last_Name_7	2023-02-02	ID_7	ID_7_000123456789.sdtid	9754	TDBFG	Yes	John Doe	John.Doe@test.com
9	3/27/2023	First_Name_8	Last_Name_8	2023-03-24	ID_8	ID_8_000123456789.sdtid	9120	TDBFG	Yes	John Doe	John.Doe@test.com
10	3/29/2023	First_Name_9	Last_Name_9	2023-04-06	ID_9	ID_9_000123456789.sdtid	3638	TDBFG	Yes	Mr Clean	Mr.Clean@test.com
11	3/29/2023	First_Name_10	Last_Name_10	2023-04-15	ID_10	ID_10_000123456789.sdtid	7983	TDBFG	Yes	Mr Clean	Mr.Clean@test.com
Token Distribution

Please see photo to check how the output should look like after manually filtering the table by email or requestor name.