Overview
We'll setup a script as a scheduled task, that will check a designated inbox for emails to process. It will scan the body of those emails for keywords to approve or reject.
We'll then take a workflow task process, and update the email to have an approve and a reject mailto link, which will open up an email, which when a user sends, will get processed and will update and close the task.
Step 1: Setup Email Inbox
The first step is to create an email inbox that we'll use for all processing. In this inbox, create three folders:
- Email Approvals
- Email Approvals Processed
- Email Approvals Not Processed
Create an inbox rule that will move the item to the Email Approvals folder if the subject begins with "REPLY:"
This Email Approvals folder will be the folder our script will check to process any emails that arrive there.
Step 2: Setup the script
Download the script from github, and change the necessary variables to local paths on your machine. Set it up on the server that it will be running on.
Step 3: Create Encrypted Credentials
The script assumes there are two users, one that has the required permissions to update the task in SharePoint ($UserSharePoint), and one user for the mailbox where the emails are being sent to ($UserEmail). Create encrypted files for each by following the instructions here, and make sure to match the name and path of the file to the $CredsFileSharePoint and $CredsFileEmail in the script.
Step 4: Create a Scheduled Task
Setup a scheduled task to run the script, and under Actions set the following:
Program/script: C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
Add Arguments: "F:\Scripts\EmailApprovals\Process-Email-Approvals.ps1"
Start in: F:\Scripts\EmailApprovals\
Step 5: Create a Workflow Task Process
Next create a workflow with a task process. For the email option, use the following as a template:
Email to user (sent from workflow)
Subject: ACTION REQUIRED: PO 98451 Change Request
Body: A new PO Change
Request has been submitted and requires your approval:
Title: Requesting a new phone
PO Number: 98451
PO Owner: Jane Doe
Original Amount: $699.00
New Amount: $799.00
Description: Upgraded the model
PO Number: 98451
PO Owner: Jane Doe
Original Amount: $699.00
New Amount: $799.00
Description: Upgraded the model
Please either approve or
reject the request below:
PO: https://vivity.sharepoint.com/Finance/Lists/POChangeForm/DispForm.aspx?ID=117
Mailto Link Sample
In each email from the workflow, we have a mailto link for
approving and rejecting, which should resemble the following.
email.approvals@vivityconsulting.com?subject=REPLY:%20PO%20Change%20Request&body=I%20approve%20the%20following%20PO%20Change%20Request.%0D%0A%0D%0ATitle:%20Requesting%20a%20new%20phone%0D%0APO%20Number:%2098451%0D%0APO%20Owner:Jane%20Doe%0D%0AOriginal%20Amount:%20$699.00%0D%0ANew%20Amount:%20$799.00%0D%0ADescription:%20%E2%80%8BUpgraded the model%0D%0A%0D%0A______________________________%0D%0A%0D%0AFor%20Processing%0D%0A%0D%0Aurl=https://vivity.sharepoint.com/Finance/Lists/POChangeTasks/DispForm.aspx?ID=117
Mailto Response Email
Note that the script keys off of certain words, so if
“approve”, “acknowledge” or “yes” is in the body, it will be approved, if
“reject” is in the body text, it will be rejected.
Subject: REPLY: PO
Change Request
Body: I approve the following PO Change Request.
Title: Requesting a new phone
PO Number: 98451
PO Owner: Jane Doe
Original Amount: $699.00
New Amount: $799.00
Description: Upgraded the model
PO Number: 98451
PO Owner: Jane Doe
Original Amount: $699.00
New Amount: $799.00
Description: Upgraded the model
______________________________
For Processing
url=https://paratekpharma.sharepoint.com/Finance/Lists/POChangeTasks/DispForm.aspx?ID=117
Design
The following should be in place for the process to work properly:
- The link should be to a Tasks lists (as the script is updating task columns)
- The body of the email received must contain either (case insensitive):· Approve· Acknowledge· Yes· Reject
- The body must also contain a "url=" with a link to the task item in the format, which is typically included at the end of the email.url=https://vivity.sharepoint.com/IT/Lists/ManagerTasks/DispForm.aspx?ID=387
- All processing is written out to a log to review any potential issues. A new file is created each time the process is run, and this folder can be regularly cleaned up.
