How To: Copy Emails From Exchange Into ProjectWise Using PowerShell

Be sure to check out my Scripting4Crypto initiative. It’s a fun way to get into using cryptocurrencies all while getting your PowerShell needs met.

You may or may not be familiar with Dave Brumbaugh’s Postman Service for ProjectWise. This service offers a way to save emails from Exchange directly into ProjectWise. Thanks to Dave, this functionality is now available within PowerShell and I’m going to step through the process of setting it up and running it.

First things first, take a look at the help for the Copy-PWMailMessagesFromExchange cmdlet to familiarize yourself with the available parameters, etc.

Get-Help Copy-PWMailMessagesFromExchange -Full

The key to getting this to work, is creating an Excel spreadsheet to store the various configuration entries and saving it within ProjectWise. We need to create an Excel spreadsheet within ProjectWise with the following columns:

• SubjectKey
• TargetFolder
• FolderURN

The value specified in the SubjectKey, will determine which emails get copied into ProjectWise. The Subject of all emails contained within the Inbox, will be searched for the SubjectKey. Only those that contain the SubjectKey will be copied into ProjectWise.

The values entered in the TargetFolder and FolderURN columns pertain to the ProjectWise folder in which the emails are copied into. To populate the FolderURN, select the target folder within ProjectWise, right mouse click in the address bar and  select Copy URN. Then paste it into the FolderURN column.

Below, you’ll see that I created a spreadsheet and named it EmailMappings.xlsx per the example within the cmdlet’s help information. My subject key is “Storage Area Issue”. The ProjectWise folder to copy emails into is Brian-TEST\Correspondence.

The spreadsheet was saved into the Brian-TEST folder within ProjectWise. You will see I also have created the Correspondence folder which is where I would like my emails to be copied into (see above).

Now, within PowerShell we will need to select our Excel spreadsheet document. Of course, you will need to be logged into your ProjectWise datasource.

$doc = Get-PWDocumentsBySearch -FolderPath 'Brian-TEST' -DocumentName 'EmailMappings'

Next, we use the returned document object to populate the MappingFile parameter within the Copy-PWMailMessagesFromExchange cmdlet. We also, need to provide a MailUser account and password. Finally, we specify a duration or time-frame in which to search for emails. In this example, 6 hours is specified.

# Splatting containing the parameter name and value pairs.
$Splat_Mail = @{
    MappingFile = $doc
    MailUser = 'joe.blow@bentley.com'
    # Prompt for Exchange password.
    MailPassword = (Read-Host "Enter Mail Password:" -AsSecureString)
    # Use the HoursOld parameter to specify a time-frame to return emails from.
    HoursOld = 6
}
Copy-PWMailMessagesFromExchange @Splat_Mail -Verbose

When you run the script you will be prompted to enter your Exchange password.

You should see verbose feedback within the PowerShell console. If any emails match the SubjectKey criteria, they will be copied into the specified ProjectWise folder.

The following Verbose messages indicate when a message is skipped. The number of messages found to copy into ProjectWise. If an email message has attachments, images, etc., they will be copied separately into ProjectWise as well.

VERBOSE: Message rejected because no valid subject key was found in subject. The search was case insensitive.

VERBOSE: Found 7 e-mails to add to ProjectWise
VERBOSE: Created 'Brian-TEST\Correspondence\[2018-09-11 13_13_13] RE_ Storage Area issue with “._lock_....” files (username).msg'
VERBOSE: Created 'Brian-TEST\Correspondence\image001.jpg'
VERBOSE: Created 'Brian-TEST\Correspondence\image002.jpg'
...
VERBOSE: Added 21 messages to ProjectWise.

The results.

Experiment with it and have fun.

Hopefully, you find this useful. Please let me know if you have any questions or comments.