iManage/Worksite NRL to Document and Email converter Mobile

worksite

Currently you need to purchase imanage mobility server to be able to view NRL files remotely.

We had a request, for users to be able to email Worksite NRL links from any devices especially Mobile devices to an email address and it emails them back the document for review.

It looked like this site software could do it at one stage : http://www.grantselect.co.uk/products/products.htm , however it’s not going anymore , so we developed a Powershell Script to do this.

1) Create a mailbox in Exchange 2010 where you want to get emails sent to . e.g. NRLDocrequest@yourdomain.com.au. Change the value below in the script MAILBOXWHEREREQUESTSGETSENT to the alias of the Email account

2)Under the Inbox create a Folder called Complete and Folder Called External

3)Give the user who will be running this script Send as as well as Full Permissions to mailbox

4)Replace below in script IMANAGESQLSERVERNAME to the SQL server of your iManage Database

5)Where you run this script make sure the user running the script has access to C:\Temp\

6)Add this script as scheduled task to check Mailbox

# Connect to Exchange server and load Exchange powershell modules
. 'C:\Program Files\Microsoft\Exchange Server\V14\bin\RemoteExchange.ps1'
Connect-ExchangeServer -auto
 
$mailbox = Get-Mailbox MAILBOXWHEREREQUESTSGETSENT
$mailAddress = $mailbox.PrimarySmtpAddress.ToString();
 
[Reflection.Assembly]::LoadFile("C:\Program Files (x86)\Microsoft\Exchange\Web Services\2.1\Microsoft.Exchange.WebServices.dll") | Out-Null
$s = New-Object Microsoft.Exchange.WebServices.Data.ExchangeService([Microsoft.Exchange.WebServices.Data.ExchangeVersion]::Exchange2007_SP1)
$s.AutodiscoverUrl($mailAddress);
 
$MailboxRootid = new-object  Microsoft.Exchange.WebServices.Data.FolderId([Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::MsgFolderRoot,$mailAddress)
$MailboxRoot = [Microsoft.Exchange.WebServices.Data.Folder]::Bind($s,$MailboxRootid)
 
# Get Folder ID from Path to be able to move
Function GetFolder()
{
	# Return a reference to a folder specified by path
 
	$RootFolder, $FolderPath = $args[0];
 
	$Folder = $RootFolder;
	if ($FolderPath -ne '\')
	{
		$PathElements = $FolderPath -split '\\';
		For ($i=0; $i -lt $PathElements.Count; $i++)
		{
			if ($PathElements[$i])
			{
				$View = New-Object  Microsoft.Exchange.WebServices.Data.FolderView(2,0);
				$View.Traversal = [Microsoft.Exchange.WebServices.Data.FolderTraversal]::Deep;
				$View.PropertySet = [Microsoft.Exchange.WebServices.Data.BasePropertySet]::IdOnly;
 
				$SearchFilter = New-Object Microsoft.Exchange.WebServices.Data.SearchFilter+IsEqualTo([Microsoft.Exchange.WebServices.Data.FolderSchema]::DisplayName, $PathElements[$i]);
 
				$FolderResults = $Folder.FindFolders($SearchFilter, $View);
				if ($FolderResults.TotalCount -ne 1)
				{
					# We have either none or more than one folder returned... Either way, we can't continue
					$Folder = $null;
					Write-Host "Failed to find " $PathElements[$i];
					Write-Host "Requested folder path: " $FolderPath;
					break;
				}
 
				$Folder = [Microsoft.Exchange.WebServices.Data.Folder]::Bind($s, $FolderResults.Folders[0].Id)
			}
		}
	}
 
	$Folder;
}
 
$folderid = new-object  Microsoft.Exchange.WebServices.Data.Folderid([Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::Inbox,$mailAddress)
$InboxFolder = [Microsoft.Exchange.WebServices.Data.Folder]::Bind($s,$folderid)
 
$mails = $inboxFolder.FindItems(5) 
$mails | % {$_.Load()}
#foreachemails
foreach($Item in $mails.Items)
	{ 
	#block emails from outside of domain for security
	if($item.Sender.Address -like "*@yourinternaldomain.com") {
 
 
	#only load items with attachments
	if($item.Attachments.Count -gt 0)
		{ 
 
		#Create the email message and set the Subject and Body
		$message = New-Object Microsoft.Exchange.WebServices.Data.EmailMessage -ArgumentList $s
 
		#Go through each attachment
		foreach($attach in $item.Attachments)
			{
			#Is it an NRL File?
			if ($attach.name -like '*.nrl') {
 
			#If it it load it to temp location
			$attach.load("C:\Temp\"+$attach.name)
 
			#Start Array for content for NRL
			$var = @{}
			$count=0
 
			#Get NRL file and store to array
			Get-Content -LiteralPath ("C:\Temp\" + $attach.name) | ForEach-Object {
				$count++
				$list = $_.Split(':!')
				$var.Add($count, $list)
				}
 
			#Get Variables from Array
			$database = $var[2][8]
			$docvalue = $var[2][11].Split(',')
			$docnumber = $docvalue[0]
			$docversion = $docvalue[1]
 
			#Remove NRL File
			Remove-Item ("C:\Temp\" + $attach.name)
 
##SQL Query Function no SQL installs needed all powershell baby
function Invoke-SQL ($SQLServer, $SqlQuery)  {
	#Uncomment below to double check the statement
	#Write-Host $SqlQuery
	$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
	$SqlConnection.ConnectionString = "Server = $SQLServer; Integrated Security = True"
 
	$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
	$SqlCmd.CommandText = $SqlQuery
	$SqlCmd.Connection = $SqlConnection
 
	$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
	$SqlAdapter.SelectCommand = $SqlCmd
 
	$DataSet = New-Object System.Data.DataSet
	[void]$SqlAdapter.Fill($DataSet)
 
	$SqlConnection.Close()
	return $dataset.tables
}
 
#Get the Doc Details from Number
$sqlresult = Invoke-SQL IMANAGESQLSERVERNAME "SELECT DOCLOC,DOCNAME,APPEXTENSION FROM [$database].[MHGROUP].DOCMASTER INNER JOIN [$database].[MHGROUP].DOCTYPES ON [$database].[MHGROUP].DOCMASTER.T_ALIAS=[$database].[MHGROUP].DOCTYPES.T_ALIAS where [DOCNUM]='$docnumber' and [VERSION]='$docversion'"
 
#Get the Location of file including imanage file server name
$docloc = $sqlresult| select -expand DOCLOC
$docloc = $docloc.Split(':')
$doclocsrv = $docloc[0]
 
#get the doc name to rename or it will be doc number
$docname = $sqlresult| select -expand DOCNAME
 
#get the doc type
$docfileextension = $sqlresult| select -expand APPEXTENSION
 
#Get the unc path of file to copy
$sqlresult = Invoke-SQL IMANAGESQLSERVERNAME "SELECT LOCATION  FROM [$database].[MHGROUP].DOCSERVERS where [DOCSERVER]='$doclocsrv'"
$location = $sqlresult| select -expand LOCATION
$source = $location + $docloc[1]
$destination = $docname + "." + $docfileextension
$pattern = "[{0}]" -f ([Regex]::Escape( [System.IO.Path]::GetInvalidFileNameChars() -join '' ))
$destination = "C:\Temp\NRL\" + [Regex]::Replace($destination, $pattern, '')
 
copy-item $source -Destination $destination
 
$message.Attachments.AddFileAttachment($destination) | Out-Null
 
#End of NRL Loop
}
#End of For Each Attachment
}
#End of the if attachment exists
}
 
#Is the email being sent have any documents to send?
if ($message.Attachments.Count -gt 0){
 
	$message.From = “email@fromdomain.com.au”
	$message.Subject = $item.Subject
	$message.Body = $msgBody
 
	$message.ToRecipients.Add($item.Sender.Address)
 
	#Send the message and save a copy in the users Sent Items folder (Alt is message.Send which will not save a copy.)
	if($item.Sender.Address -ne "emails@toignore.com")
		{
		$message.SendAndSaveCopy()
		}
 
}
 
 
#Move to Folder After Replying
$FolderObject = GetFolder($MailboxRoot, "\Inbox\Complete\");
		try
		{
		$Item.Move($FolderObject.Id) | out-null;
		}
		catch
		{
		Write-Host "Failed to move item", $Item.Id.UniqueId
		}
 
Remove-Item c:\TEMP\NRL\*
 
#Loop per email
 
}
 
#Move to Folder After Replying
$FolderObject = GetFolder($MailboxRoot, "\Inbox\External\");
		try
		{
		$Item.Move($FolderObject.Id) | out-null;
		}
		catch
		{
		Write-Host "Failed to move item", $Item.Id.UniqueId
		}
 
}

 

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Tags: .nrl, docrequest, email, GS Link Warden, imanage, imanage mobility, Interwoven, NRL to File, powershell, worksite

Trackback from your site.