Use PowerShell to Add Table to Word Doc and Email as Attachment

Doctor Scripto

Summary: Microsoft Scripting Guy, Ed Wilson, talks about using Windows PowerShell to create a document in Microsoft Word, add a table, and email the document as an attachment.

Microsoft Scripting Guy, Ed Wilson, is here. One of the cool things I like to do with Windows PowerShell is to combine multiple tasks so that it makes life easier for me. I use what I call the “annoyance meter” quite often to determine whether to write a script or not. Today’s script is one of those things that rates pretty high on my annoyance meter.

Here is the scenario: I need to supply my professor in my Shakespeare class with a list of proposed topics and my critical approach to them. So what is the big deal? I would have to create a table, type out each of the plays, add my topics, and add my critical approach to each topic. I then have to save the file, open email, find the file, and send the email. That is like, I don’t know, maybe a dozen steps.

Getting started

But what do I have? I already have a text file with my plays, and I already came up with a listing of topic ideas for each of the plays. I did that yesterday in Add Custom Headers to Folder Full of Word Documents.

So all I need to do is to add the literary approach as a third column to the CSV file. Here is my modification:

Now I open the Windows PowerShell ISE, start a new script, and set my initial variables. Because I am going to use the SaveAs method from the Document object, I know that I will need to pass the format by reference. I include this [ref] when I create the WdSaveFormat type. I want to know how many columns I have in my CSV file. Each column will be added as a NoteProperty, so I use the Get-Member cmdlet to find the NoteProperty members, and I count them. I find the number of rows that I will need by counting the number of rows in my CSV file. The code is shown here:

[ref]$SaveFormat = "microsoft.office.interop.word.WdSaveFormat" -as [type]

$topics = Import-Csv C:\Lit\Shakespeare.txt

$path = "C:\lit\ResearchTopics.docx"

$Number_Of_Rows = ($topics.Count +1)

$Number_Of_Columns = ($topics | gm -MemberType NoteProperty).count

$x = 2

Create the Word document 

I create the Word.Application object, set it to not be visible, and add a document to the Documents collection object. I then obtain a range object from the document and store the returned objects in the appropriate objects. This is shown here:

$Word = New-Object -comobject word.application

$Word.Visible = $false

$Doc = $Word.Documents.Add()

$Range = $Doc.Range()

Now that I have a Range object, I can add a table to the Tables collection. When I do this, I use the Add method and I specify the Range that will host the table (the range I have stored in the $Range variable), the number of rows, and the number of columns that the table will contain. I pipe the results to the Out-Null cmdlet to avoid cluttering up my output pane. Here is the code:

$Doc.Tables.Add($Range,$Number_Of_Rows,$Number_Of_Columns) | Out-Null

Now I get the table and add my column headings. I have only added a single table to the Tables collection, so I can simply use Item(1) to obtain the table object that represents my new table. I now use the Cell property to add my column headings. I want the columns to begin with the first row and first column. Then I specify a text property for the range associated with each cell. This code is shown here:

$Table = $Doc.Tables.item(1)

$Table.Cell(1,1).Range.Text = "Play"

$Table.Cell(1,2).Range.Text = "Topic"

$Table.Cell(1,3).Range.Text = "Approach"

Now I want to add the information to my table. I walk through the information I obtained from my CSV file, and add each appropriate value. Because I want to ensure that play titles appear under the Play column heading, I have specified each property rather than simply walking through the collection of items. This code is shown here:

Foreach($t in $topics)

{

 $Table.Cell($x,1).Range.Text = $t.Play

 $Table.Cell($x,2).Range.Text= $t.Topic

 $Table.Cell($x,3).Range.Text=$t.Approach

 $x++

} 

I just want a nice looking table, but I do not want to fool with a lot of manual formatting. Luckily, Microsoft Word has an automatic format method associated with the Table object. I use a hard-coded number to choose which style I want to use. This is shown here:

$Table.AutoFormat(9)

Now I save my document by using the SaveAs method. I need to specify the path and the format when I call this method. I then close the document and exit the application. Here is the code:

$doc.saveas([ref] $path, [ref]$SaveFormat::wdFormatDocumentDefault)

$doc.close()

$word.quit()

I want to release all of these objects, so I call the ReleaseComObject method. I do this for each of objects I created. I then call garbage collection to scavenge memory, and I remove the variables. This is shown here:

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($doc) | Out-Null

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($word) | Out-Null

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($range) | Out-Null

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($table) | Out-Null

Remove-Variable Doc,Word, range, table

[gc]::collect()

[gc]::WaitForPendingFinalizers()

The document is shown here:

To email the document to my professor, I use the Send-MailMessage cmdlet. All I need to do is ensure that I am using the right SMTP email server, and that I specify my credentials if required. Here is the code I use:

Send-MailMessage -From "ScriptingGuys@Outlook.com" -To "DrHasenpfeffer@State.Edu" `

 -Attachments C:\Lit\ResearchTopics.docx -Subject "research topics" -Body "attached" `

 -SmtpServer "smtp-mail.outlook.com" -UseSsl -Credential "ScriptingGuys@Outlook.com"

That is all there is to using Windows PowerShell to create a Word document, add a table, and email the document as an attachment. Join me tomorrow when I will talk about more cool stuff.

I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

Ed Wilson, Microsoft Scripting Guy

Doctor Scripto Scripter, PowerShell, vbScript, BAT, CMD

Follow