Categories

12187

Status of Office software

You can also use the SoftwareLicensingProduct CIM class to test the status of your Office products.

Get-CimInstance -ClassName SoftwareLicensingProduct -Filter "Name LIKE 'Office%'" |
where PartialProductKey |
select Name, ApplicationId, LicenseStatus

You need to be careful with Office as you might find a lot more options than you expected. On my machine I found this:

Get-CimInstance -ClassName SoftwareLicensingProduct -Filter "Name LIKE 'Office%'" | select Name -Unique | sort name

Office 15, OfficeO365ProPlusR_Grace edition
Office 15, OfficeO365ProPlusR_Retail edition
Office 15, OfficeO365ProPlusR_Subscription1 edition
Office 15, OfficeO365ProPlusR_Subscription2 edition
Office 15, OfficeO365ProPlusR_Subscription3 edition
Office 15, OfficeO365ProPlusR_Subscription4 edition
Office 15, OfficeO365ProPlusR_Subscription5 edition
Office 15, OfficeO365ProPlusR_SubTrial1 edition
Office 15, OfficeO365ProPlusR_SubTrial2 edition
Office 15, OfficeO365ProPlusR_SubTrial3 edition
Office 15, OfficeO365ProPlusR_SubTrial4 edition
Office 15, OfficeO365ProPlusR_SubTrial5 edition
Office 15, OfficeO365SmallBusPremR_Grace edition
Office 15, OfficeO365SmallBusPremR_Retail edition
Office 15, OfficeO365SmallBusPremR_Subscription1 edition
Office 15, OfficeO365SmallBusPremR_Subscription2 edition
Office 15, OfficeO365SmallBusPremR_Subscription3 edition
Office 15, OfficeO365SmallBusPremR_Subscription4 edition
Office 15, OfficeO365SmallBusPremR_Subscription5 edition
Office 15, OfficeO365SmallBusPremR_SubTrial1 edition
Office 15, OfficeO365SmallBusPremR_SubTrial2 edition
Office 15, OfficeO365SmallBusPremR_SubTrial3 edition
Office 15, OfficeO365SmallBusPremR_SubTrial4 edition
Office 15, OfficeO365SmallBusPremR_SubTrial5 edition
Office 15, OfficeProjectProCO365R_Subscription edition
Office 15, OfficeProjectProCO365R_SubTest edition
Office 15, OfficeProjectProCO365R_SubTrial edition
Office 15, OfficeProjectProDemoR_BypassTrial180 edition
Office 15, OfficeProjectProMSDNR_Retail edition
Office 15, OfficeProjectProO365R_Subscription edition
Office 15, OfficeProjectProO365R_SubTest edition
Office 15, OfficeProjectProO365R_SubTrial edition
Office 15, OfficeProjectProR_Grace edition
Office 15, OfficeProjectProR_OEM_Perp edition
Office 15, OfficeProjectProR_Retail edition
Office 15, OfficeProjectProR_Trial edition
Office 15, OfficeProPlusDemoR_BypassTrial180 edition
Office 15, OfficeProPlusMSDNR_Retail edition
Office 15, OfficeProPlusR_Grace edition
Office 15, OfficeProPlusR_OEM_Perp edition
Office 15, OfficeProPlusR_Retail edition
Office 15, OfficeProPlusR_Trial edition
Office 15, OfficeVisioProCO365R_Subscription edition
Office 15, OfficeVisioProCO365R_SubTest edition
Office 15, OfficeVisioProCO365R_SubTrial edition
Office 15, OfficeVisioProDemoR_BypassTrial180 edition
Office 15, OfficeVisioProMSDNR_Retail edition
Office 15, OfficeVisioProO365R_Subscription edition
Office 15, OfficeVisioProO365R_SubTest edition
Office 15, OfficeVisioProO365R_SubTrial edition
Office 15, OfficeVisioProR_Grace edition
Office 15, OfficeVisioProR_OEM_Perp edition
Office 15, OfficeVisioProR_Retail edition
Office 15, OfficeVisioProR_Trial edition

which was a lot more than I expected.

It is possible to use WMI to set the product key – use the SoftwareLicensingService class

PowerShell 3 and Word

 

This is a common scenario

$word = New-Object -ComObject "Word.application"            
$word.visible = $true            
$doc = $word.Documents.Add()            
$doc.Activate()            
            
$word.Selection.Font.Name = "Cambria"            
$word.Selection.Font.Size = "20"            
$word.Selection.TypeText("PowerShell")            
$word.Selection.TypeParagraph()            
            
$word.Selection.Font.Name = "Calibri"            
$word.Selection.Font.Size = "12"            
$word.Selection.TypeText("The best scripting language in the world!")            
$word.Selection.TypeParagraph()            
            
$file = "c:\scripts\office\test1.doc"            
$doc.SaveAs([REF]$file)            
            
$Word.Quit()


Create a new Word document – put some text into it and save it with a given file name.  I’ve used it successfully to create server documentation.



Unfortunately with PowerShell v3 it fails with this message



 



Exception calling "SaveAs" with "1" argument(s): "This is not a valid file name.

Try one or more of the following:


* Check the path to make sure it was typed correctly.


* Select a file from the list of files and folders."


At line:17 char:1


+ $doc.SaveAs([REF]$file)


+ ~~~~~~~~~~~~~~~~~~~~~~~


    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException


    + FullyQualifiedErrorId : COMException



It appears not to like the [ref] but if you leave it out you get this



Argument: '1' should be a System.Management.Automation.PSReference. Use [ref].

At line:18 char:1


+ $doc.SaveAs($file)


+ ~~~~~~~~~~~~~~~~~~


    + CategoryInfo          : NotSpecified: (:) [], MethodException


    + FullyQualifiedErrorId : NonRefArgumentToRefParameterMsg



[ref] isn’t case sensitive.



The only way round it that I know of is to create a blank Word document to use as a template



Copy-Item -Path mydoc.doc  -Destination testdoc.doc -Force            
            
$file = "C:\MyData\SkyDrive\Data\Scripts\Office-Word\testdoc.doc"            
            
$word = New-Object -ComObject "Word.application"            
$word.visible = $true            
$doc = $word.Documents.Open($file)            
            
$word.Selection.Font.Name = "Cambria"            
$word.Selection.Font.Size = "20"            
$word.Selection.TypeText("PowerShell")            
$word.Selection.TypeParagraph()            
            
$word.Selection.Font.Name = "Calibri"            
$word.Selection.Font.Size = "12"            
$word.Selection.TypeText("The best scripting language in the world!")            
$word.Selection.TypeParagraph()            
            
$doc.Save()            
$doc.Close()            
$Word.Quit()


Notice that you need to give the full path to the file. Use the Open method and add the text. You can then save, close and quit the application.



I’ve tested this using office 2010 & office 2013 on Windows 7 & 8



Unfortunately we are still left with the problem that we can’t save the Word document into different formats.

Create a calendar item

Continuing the occasional look at Outlook automation its time to see how we create a calendar item

function new-calendaritem {            
param (            
            
 [string]$mailbox,            
             
 [datetime]$start,            
             
 [datetime]$end,            
             
 [string]$subject,            
             
 [string]$location,            
             
 [string]$body            
            
)            
$outlook = New-Object -ComObject Outlook.Application            
            
$folder = get-mailfolders |             
where {$_.Path -like "*calendar*" -and $_.Path -notlike "*birthday*" -and $_.Path -like "*$mailbox*"}            
            
$calendar = $outlook.Session.GetFolderFromID($folder.EntryID, $folder.StoreID)            
             
$entry = $calendar.Items.Add(1)            
$entry.Start = $start            
             
$entry.End = $end            
             
$entry.Subject = $subject            
             
$entry.Location = $location            
             
$entry.Body = $body             
             
$entry.Save()            
            
}


 



The appropriate calendar is identified – remember I have 4 to choose from. The Add method is used from the calendar items collection and the properties populated.



I always enter dates like this “14 September 2011 11:00” as it removes any problems with culture. In the UK this would be “14/9/2011 11:00” but when entering in this style I have to remember to use the US format “9/14/2011 11:00”



The mailbox parameter helps identify which calendar the entry goes

Outlook: removing calendar entries

We recently looked at dumping the Calendar entries

http://msmvps.com/blogs/richardsiddaway/archive/2011/08/23/outlook-viewing-calendar-entries.aspx

 

I usually leave entries to build up in the Calendar but a simple clean operation is to delete everything that occurred before a certain date

function remove-calendaritem {            
param (            
 [datetime]$date            
)            
$outlook = New-Object -ComObject Outlook.Application            
            
get-mailfolders |             
where {$_.Path -like "*calendar*" -and $_.Path -notlike "*birthday*"} |            
foreach {            
  $targetfolder = $outlook.Session.GetFolderFromID($_.EntryID, $_.StoreID)            
              
  $targetfolder.Items | foreach {            
    if ($_.StartTime -lt $date){$_.Delete()}            
  }             
}            
}


The date parameter defines the date for which we want to delete all earlier entries. Get the calendar folders and test the StartTime of each entry. If its before our date then delete it.

Outlook: Viewing Calendar Entries

We have seen how to view emails – this is how to view items in your calendar

function get-calendaritem {            
            
$outlook = New-Object -ComObject Outlook.Application            
            
get-mailfolders |             
where {$_.Path -like "*calendar*" -and $_.Path -notlike "*birthday*"} |            
foreach {            
  $targetfolder = $outlook.Session.GetFolderFromID($_.EntryID, $_.StoreID)            
              
  $targetfolder.Items | foreach {            
   New-Object -TypeName PSObject -Property @{            
    Folder = $targetfolder.FolderPath            
    StartTime = $_.Start            
    EndTime = $_.End            
    Organizer = $_.Organizer            
    Subject = $_.Subject            
    Location =$_.Location            
   }            
  }             
}            
}


Use the get-mailfolders function we developed earlier. Filter for the calendar folders. For each of them get the folder and strip the appropriate properties from the entry. Create an object and display

Outlook: sending emails

We’ve looked at examining the email folders and their contents, as well as cleaning out old emails.

Its time to look at sending emails. This function shows the skeleton of the process

function send-mailitem {            
param (            
            
 [string]$to,            
             
 [string]$from,            
             
 [string]$subject,            
             
 [string]$body            
            
)            
$outlook = New-Object -ComObject Outlook.Application            
            
$email = $outlook.CreateItem($types::olMailItem)            
            
$email.To = $to            
$email.subject = $subject            
$email.body = $body            
            
$email.SendUsingAccount = $outlook.Session.Accounts | where {$_.DisplayName -eq $from}            
$email.Send()            
}


Create a new mail item object. Set the To, subject and body



The choose the account from which to send the email – remember I have multiple hotmail accounts connected through Outlook. If you are using Exchange you may want to test using smtpaddress instead of DisplayName



We can then use the send method to fire off the email.



There are a number of things to do to tidy this up:



  1. Create a function to get the account
  2. Accept the To parameter from the pipeline to enable multiple recipients
  3. Check the email addresses using regex (oh joy)

Outlook: deleting mail items

As promised here is the function to delete mail items in a specific folder

function remove-mailitem {            
[CmdletBinding(SupportsShouldProcess=$true)]            
param (            
 [parameter(Mandatory=$true)]            
 [string]$mailfolder,            
              
 [datetime]$start,            
 [datetime]$finish            
)            
if ($start -and $finish){            
  if ($start -ge $finish){Throw "Finish $($finish) before Start $($start)"}            
}            
            
$outlook = New-Object -ComObject Outlook.Application            
            
$folders | where {$_.Path -like "*$mailfolder"} |            
foreach {            
$targetfolder = $outlook.Session.GetFolderFromID($_.EntryID, $_.StoreID)            
              
foreach ($item in $targetfolder.Items)  {            
 if ($start){if ($item.SentOn -lt $start){continue}}            
 if ($finish){if ($item.SentOn -gt $finish){continue}}            
             
  if ($psCmdlet.ShouldProcess("$($item.Subject) $($item.SentOn)", "deleting")) {            
    $item.Delete()            
  }            
              
}            
               
}            
}


If you’ve been following this series you will recognise how this works. The parameters are the mail folder and an optional start and finish date. A quick check to see if the dates are the right way round and we jump into the processing. The folders that match our input are selected and foreach of them we loop through the mail items.



The SentOn date is compared to the start and finish dates if they are defined. The item is skipped if it falls outside of those dates.



We can then delete the item. I’ve also added the –whatif parameter to the function by using   [CmdletBinding(SupportsShouldProcess=$true)] and



if ($psCmdlet.ShouldProcess("$($item.Subject) $($item.SentOn)", "deleting")) {
  $item.Delete()
}



This adds another safety level to the function

Outlook: Viewing mail items

Continuing our perambulation around Outlook when used with multiple hotmail accounts its time to look at the other folders and the mail items they contain. This post will show how to view the mail items and a future post will show how to delete items from a specific folder.

Viewing mail items cam be performed with the following function

function get-mailitem {            
param (            
 [parameter(Mandatory=$true)]            
 [string]$mailfolder,            
              
 [switch]$all            
)            
$outlook = New-Object -ComObject Outlook.Application            
            
$folders | where {$_.Path -like "*$mailfolder"} |            
foreach {            
  $targetfolder = $outlook.Session.GetFolderFromID($_.EntryID, $_.StoreID)            
              
  if ($all){            
   $targetfolder.Items             
  }            
  else {            
   $targetfolder.Items | sort  SentOn -desc | select SenderEmailAddress, Subject, SentOn            
  }            
}            
}


The function takes a mandatory string to identify the mail folder and we use the GetFolderFromID method to access the folder. If the –all switch is used we get a dump of the full object for each mail item otherwise a summary consisting of the sender’s address, date sent and subject is output.



If the –all switch is used we can use the PowerShell pipeline to perform further processing e.g.



get-mailitem –mailfolder Baen –all | where {$_.SentOn –lt [datetime]”1 January 2007”}

Clearing junk mail

Getting back to looking at working with Outlook we can adapt the function used to deleted the contents of the Deleted Items folder to work with the Junk mail folders

function clear-junkmail {            
            
$outlook = New-Object -ComObject Outlook.Application            
            
get-mailitemcount -junk            
            
$folders | where {$_.Path -like "*junk*"} |            
foreach {             
  $mailfolder = $outlook.Session.GetFolderFromID($_.EntryID, $_.StoreID)            
              
  if ($mailfolder.Items.Count -gt 0){            
    do {            
      foreach ($item in $mailfolder.Items){$item.Delete()}            
    } while ($mailfolder.Items.Count -gt 0)            
  }               
}            
get-mailitemcount -junk            
}


Very much the same as before but we are looking for folders that contain the work “junk”



It would be possible to combine this function and the clear-deletedmail function but I decided to keep them separate for simplicity

Outlook folder item count revisited

I started this series http://msmvps.com/blogs/richardsiddaway/archive/2011/07/30/outlook-connector-amp-mail-folder-item-count.aspx by looking at how we could enumerate the mail folders in Outlook 2010 when I had had four hotmail accounts open.  The function has been modified since then

function get-mailitemcount {            
param (            
 [parameter(ParameterSetName="All")]            
 [switch]$all,             
 [parameter(ParameterSetName="Deleted")]            
 [switch]$deleted,            
 [parameter(ParameterSetName="Junk")]            
 [switch]$junk            
)            
$outlook = New-Object -ComObject Outlook.Application            
foreach ($folder in $outlook.Session.Folders){            
              
  foreach($mailfolder in $folder.Folders ) {            
               
  if ($deleted) {if ($($mailfolder.Name) -notlike "Deleted*"){continue} }            
  if ($junk)  {if ($($mailfolder.Name) -notlike "Junk*"){continue} }            
               
    New-Object -TypeName PSObject -Property @{            
      Mailbox = $($folder.Name)            
      Folder = $($mailfolder.Name)            
      ItemCount = $($mailfolder.Items.Count)            
    } | select Mailbox, Folder, ItemCount            
  }             
}            
}


I use three parameters to determine if I want to look at the Junk or Deleted folders or if I’m  going to dump the information for all folders.  I’ve used parameter sets to make the three parameters mutually exclusive.



The same loops are used as before – the outer one loops through the mailboxes and the inner one through each folder in those mailboxes.  If the deleted or junk switches have been set any folder that doesn’t match the criteria is skipped.



An object is created to output the name of the mailbox, folder and number of items