$if = '\portal2010.brand.comsitesHC2017 count.xlsx'
$excel = New-Object -Com Excel.Application
$Workbook = $Excel.Workbooks.Open($if)
$page = 'HC'
$ws = $Workbook.worksheets | where-object {$_.Name -eq $page}
$range = $ws.Range("V2:AF")
$rows = $range.Rows.Count
$hcTableCopy = $ws.Range("V2:AF$rows").Copy()
$hcTablePaste = $hcTableCopy.PasteSpecial($default, $default, $default, $default, 9, $default, $default)
$SendTo = "[email protected]"
$SMTPServer = "smtp.brand.com"
$EmailFrom = "[email protected]"
$EmailSubject = "Weekly Email"
$Image2 = '\portal2010.brand.comsitesHCHC Dashboards2017 HC_files2017 count_25311_image002.png'
$Image4 = '\portal2010.brand.comsitesHCHC Dashboards2017 HC_files2017 count_25311_image004.png'
$Image6 = '\portal2010.brand.comsitesHCHC Dashboards2017 HC_files2017 count_25311_image006.png'
$Image8 = '\portal2010.brand.comsitesHCHC Dashboards2017 HC_files2017 count_25311_image008.png'
$Message = new-object Net.Mail.MailMessage
Add-PSSnapin Microsoft.Exchange.Management.Powershell.Admin -erroraction silentlyContinue
$att2 = new-object Net.Mail.Attachment($Image2)
$att2.ContentId = "att2"
$att4 = new-object Net.Mail.Attachment($Image4)
$att4.ContentId = "att4"
$att6 = new-object Net.Mail.Attachment($Image6)
$att6.ContentId = "att6"
$att8 = new-object Net.Mail.Attachment($Image8)
$att8.ContentId = "att8"
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$body = ' 
'
$Message.From = $EmailFrom
$Message.To.Add($SendTo)
$Message.Cc.Add($CCTo1)
$Message.Cc.Add($CCTo2)
$Message.Cc.Add($CCTo3)
$Message.Cc.Add($CCTo4)
$Message.Subject = $EmailSubject
$Message.Body = $body + $hcTablePaste
$Message.IsBodyHTML = $true
$Message.Attachments.Add($att2)
$Message.Attachments.Add($att4)
$Message.Attachments.Add($att6)
$Message.Attachments.Add($att8)
$smtp.Send($Message)
$excel.DisplayAlerts = $False
Start-Sleep -s 5
$excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)The above code is what I have tried so far. I get an error that says it can't open the file from sharepoint at this point and I am not sure the table paste portion of this is going to work right.
I tried modifying the following, but I really don't think I should have to check out and make editable the file just to copy the table range to an email.
foreach ($file in $excelfiles)
{ $workbookpath = $file.fullname if ($excel.workbooks.canCheckOut($workbookpath)) { # open the worksheet and check it out $excelworkbook = $excel.workbooks.Open($workbookpath) $excelworkbook = $excel.workbooks.CheckOut($workbookpath) # Don't ask cuz I don't know (yet). You have to open it again. $excelworkbook = $excel.workbooks.Open($workbookpath) # Refresh all the pivot tables with the new data. $excelworkbook.RefreshAll() # Save and Check it in $excelworkbook.Save() $excelworkbook.CheckInWithVersion() }
}
$excel.quit()So any help to get the table pasting part would be great. The email send with images just fine other than that.