{"id":7731,"date":"2015-02-20T00:01:00","date_gmt":"2015-02-20T00:01:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2015\/02\/20\/use-powershell-to-add-table-to-word-doc-and-email-as-attachment\/"},"modified":"2019-02-18T10:30:33","modified_gmt":"2019-02-18T17:30:33","slug":"use-powershell-to-add-table-to-word-doc-and-email-as-attachment","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/use-powershell-to-add-table-to-word-doc-and-email-as-attachment\/","title":{"rendered":"Use PowerShell to Add Table to Word Doc and Email as Attachment"},"content":{"rendered":"

Summary<\/b>: 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.<\/p>\n

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.<\/p>\n

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.<\/p>\n

Getting started<\/h2>\n

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<\/a>.<\/p>\n

So all I need to do is to add the literary approach as a third column to the CSV file. Here is my modification:<\/p>\n

\"Image<\/a> <\/p>\n

Now I open the Windows PowerShell ISE, start a new script, and set my initial variables. Because I am going to use the SaveAs<\/b> method from the Document<\/strong> object, I know that I will need to pass the format by reference. I include this [ref] when I create the WdSaveFormat<\/b> type. I want to know how many columns I have in my CSV file. Each column will be added as a NoteProperty<\/b>, so I use the Get-Member<\/b> cmdlet to find the NoteProperty<\/b> 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:<\/p>\n

[ref]$SaveFormat = "microsoft.office.interop.word.WdSaveFormat" -as [type]<\/p>\n

$topics = Import-Csv C:\\Lit\\Shakespeare.txt<\/p>\n

$path = "C:\\lit\\ResearchTopics.docx"<\/p>\n

$Number_Of_Rows = ($topics.Count +1)<\/p>\n

$Number_Of_Columns = ($topics | gm -MemberType NoteProperty).count<\/p>\n

$x = 2<\/p>\n

Create the Word document <\/h2>\n

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

$Word = New-Object -comobject word.application<\/p>\n

$Word.Visible = $false<\/p>\n

$Doc = $Word.Documents.Add()<\/p>\n

$Range = $Doc.Range()<\/p>\n

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

$Doc.Tables.Add($Range,$Number_Of_Rows,$Number_Of_Columns) | Out-Null<\/p>\n

Now I get the table and add my column headings. I have only added a single table to the Tables<\/b> collection, so I can simply use Item(1)<\/b> to obtain the table object that represents my new table. I now use the Cell<\/b> 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:<\/p>\n

$Table = $Doc.Tables.item(1)<\/p>\n

$Table.Cell(1,1).Range.Text = "Play"<\/p>\n

$Table.Cell(1,2).Range.Text = "Topic"<\/p>\n

$Table.Cell(1,3).Range.Text = "Approach"<\/p>\n

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:<\/p>\n

Foreach($t in $topics)<\/p>\n

{<\/p>\n

 $Table.Cell($x,1).Range.Text = $t.Play<\/p>\n

 $Table.Cell($x,2).Range.Text= $t.Topic<\/p>\n

 $Table.Cell($x,3).Range.Text=$t.Approach<\/p>\n

 $x++<\/p>\n

} <\/p>\n

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<\/b> object. I use a hard-coded number to choose which style I want to use. This is shown here:<\/p>\n

$Table.AutoFormat(9)<\/p>\n

Now I save my document by using the SaveAs<\/b> 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:<\/p>\n

$doc.saveas([ref] $path, [ref]$SaveFormat::wdFormatDocumentDefault)<\/p>\n

$doc.close()<\/p>\n

$word.quit()<\/p>\n

I want to release all of these objects, so I call the ReleaseComObject<\/b> 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:<\/p>\n

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($doc) | Out-Null<\/p>\n

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($word) | Out-Null<\/p>\n

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($range) | Out-Null<\/p>\n

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($table) | Out-Null<\/p>\n

Remove-Variable Doc,Word, range, table<\/p>\n

[gc]::collect()<\/p>\n

[gc]::WaitForPendingFinalizers()<\/p>\n

The document is shown here:<\/p>\n

To email the document to my professor, I use the Send-MailMessage<\/b> 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:<\/p>\n

Send-MailMessage -From "ScriptingGuys@Outlook.com" -To "DrHasenpfeffer@State.Edu" `<\/p>\n

 -Attachments C:\\Lit\\ResearchTopics.docx -Subject "research topics" -Body "attached" `<\/p>\n

 -SmtpServer "smtp-mail.outlook.com" -UseSsl -Credential "ScriptingGuys@Outlook.com"<\/p>\n

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.<\/p>\n

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

Ed Wilson, Microsoft Scripting Guy<\/b><\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":596,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[49,3,45,360],"class_list":["post-7731","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-office","tag-scripting-guy","tag-windows-powershell","tag-word"],"acf":[],"blog_post_summary":"

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 […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/7731","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/596"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=7731"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/7731\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=7731"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=7731"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=7731"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}

 \"Image<\/a><\/p>\n