AndreMateus
New Member
- Joined
- Mar 31, 2023
- Messages
- 11
- Office Version
- 365
- 2016
- Platform
- Windows
Currently, I have a macro that sends a snapshot of a filtered table and pasts it in the body of an outlook email as text and sends it off to the email address listed in a certain cell along with a message in the body of the email. The outlook mailbox defaults to the primary mailbox and I need to be able to select which mailbox to send the email with.
Now, my spreadsheet contains multiple recipients and, often times a recipient may be listed more than once. For this reason, I would like to have only 1 email sent to the recipient even if their email shows multiple times on the table. Along with the snapshot of the table, I would like to add attachments. The number of attachments being sent to the recipient will vary depending on the number of times our recipient shows on the list.
The goal is to distribute soft token files to each requestor but each email must contain the appropriate information for each of our requestors.
Here's a brief summary of what I am looking for:
1 - Need to be able to choose with mailbox the email will be sent from
2 - Email must be sent to 1 recipient even if the same email is showing multiple times (see "Requestor" field).
3 - Email subject line is always the same one
4 - Email Body message is always the same expect it captures/filters the info that should only be sent to our recipient
5 - Must attach one or more files (depending on the number of files being sent to the same submitter). Only 1 file per row. (see "Soft token file name" and "requestor" field).
6 - Not sure if it is possible to have the script loop through the table and complete the above steps but automatically. If not, I would not mind filtering the requests manually and having the emails sent automatically.
Please see pictures below for more information on how my current table looks and how I would like the final email(s) to look like.
Current script:
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
.CC = ""
.BCC = ""
.Subject = "SOW Contingent Worker - Remote Access Request"
.Body = "Hi, As part of your Create, Modify or Terminate SOW Contingent Worker ServiceNow request for the below user, you requested Remote Access for the user. 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.
regards,
My Signature"
.display
Set xInspect = newEmail.GetInspector
Set pageEditor = xInspect.WordEditor
Sheet4.Range("Table2[#All]").Copy
pageEditor.Application.Selection.Start = Len(.Body)
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
Now, my spreadsheet contains multiple recipients and, often times a recipient may be listed more than once. For this reason, I would like to have only 1 email sent to the recipient even if their email shows multiple times on the table. Along with the snapshot of the table, I would like to add attachments. The number of attachments being sent to the recipient will vary depending on the number of times our recipient shows on the list.
The goal is to distribute soft token files to each requestor but each email must contain the appropriate information for each of our requestors.
Here's a brief summary of what I am looking for:
1 - Need to be able to choose with mailbox the email will be sent from
2 - Email must be sent to 1 recipient even if the same email is showing multiple times (see "Requestor" field).
3 - Email subject line is always the same one
4 - Email Body message is always the same expect it captures/filters the info that should only be sent to our recipient
5 - Must attach one or more files (depending on the number of files being sent to the same submitter). Only 1 file per row. (see "Soft token file name" and "requestor" field).
6 - Not sure if it is possible to have the script loop through the table and complete the above steps but automatically. If not, I would not mind filtering the requests manually and having the emails sent automatically.
Please see pictures below for more information on how my current table looks and how I would like the final email(s) to look like.
Current script:
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
.CC = ""
.BCC = ""
.Subject = "SOW Contingent Worker - Remote Access Request"
.Body = "Hi, As part of your Create, Modify or Terminate SOW Contingent Worker ServiceNow request for the below user, you requested Remote Access for the user. 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.
regards,
My Signature"
.display
Set xInspect = newEmail.GetInspector
Set pageEditor = xInspect.WordEditor
Sheet4.Range("Table2[#All]").Copy
pageEditor.Application.Selection.Start = Len(.Body)
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
