AndreMateus
New Member
- Joined
- Mar 31, 2023
- Messages
- 11
- Office Version
- 365
- 2016
- Platform
- Windows
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
Please see photo to check how the output should look like after manually filtering the table by email or requestor name.
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 | |||
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.