Insert excel table into email body using for each loop

rain123 · 2019-09-17T00:33:17.000Z

Hi, I am trying to send different excel files to their respective email recipients by including the table from the excel file in the email body. I am having problem with the bold lines of code below where it doesn’t loop to each excel file available but work perfectly fine if I specify the range as follows ( $rgeSource=$UserWorksheet.range(“A1”,“D11”)) instead of $rgeSource=$UserWorksheet.range($firstrow,$lastCol). My code as below:

$RefineryConfiguration = Get-Content -Path "path.json" -Raw | ConvertFrom-Json
$RefineryList = $RefineryConfiguration.refineries

foreach ($Refinery in $RefineryList) {
    $x1 = New-Object -comobject Excel.Application
    $UserWorkBook = $x1.Workbooks.Open($Refinery.filepath)
    $UserWorksheet = $UserWorkBook.Worksheets.Item(1)
    $UserWorksheet.activate()
    #change here to get last row and column, original: $rgeSource=$UserWorksheet.range(“A1”,“D11”)#
    [int]$firstRowvalue = ($UserWorksheet.UsedRange.rows.count + 1)
    $firstrow = $UserWorksheet.Cells.Item($firstRowvalue, 1).Value
    [int]$lastColvalue = ($UserWorksheet.UsedRange.columns.count + 1) - 1
    $lastCol = $UserWorksheet.Cells.Item($lastColvalue, 1).Value
    $rgeSource=$UserWorksheet.range($firstrow,$lastCol)
    #done change##
    $rgeSource.Copy() | Out-Null
    $Outlook = New-Object -comObject Outlook.Application
    $Mail = $Outlook.CreateItem(0)
    $Mail.Recipients.Add($Refinery.email)
    $Mail.Attachments.Add($Refinery.filepath)
    $Mail.Subject=“enter subject”
    $oDoc = $Mail.GetInspector.WordEditor
    $oRange = $oDoc.Range()
    $oRange.InsertParagraph
    $oRange.InsertBefore(“Hi Team,”)
    $oRange.InsertParagraphAfter()
    $oRange.InsertParagraphAfter()
    $oRange.InsertAfter(" enter the mail body")
    $oRange.InsertParagraphAfter()
    $oRange.InsertParagraphAfter()
    $oRange.Collapse($wdCollapseStart)
    $oRange.Paste()
    $oRange.Collapse($wdCollapseEnd)
    $oRange.move(4,3)
    $oRange.InsertParagraphAfter()
    $oRange.InsertAfter(“Thanks & Regards,”)
    $oRange.InsertParagraphAfter()
    $Mail.Display()
    $Mail.Send()
}

Your help is much appreciated. Thank you in advance.

Neally · 2019-09-17T00:36:33.000Z

If you post code, please use the insert code button </>

PLEASE READ BEFORE POSTING! Read if you're new to the PowerShell forum! Programming & Development

Hi, and welcome to the PowerShell forum! Don’t apologize for being a “noob” or “newbie” or “n00b.” There’s just no need – nobody will think you’re stupid, and the forums are all about asking questions. Just ask! Use a descriptive subject. Don't say "Need help" or "PowerShell Help", actually summarize what the problem is. It helps the rest of us keep track of which problem is which. Don’t post massive scripts. We’re all volunteers and we don’t have time to read all that, nor will we copy, past…

Neally · 2019-09-17T01:22:59.000Z

have you looked int the ‘importexcel’ module by Doug Finke, that would do a log of the leg work for you.

powershellgallery.com

ImportExcel 6.5.1

PowerShell module to import/export Excel spreadsheets, without Excel. Check out the How To Videos https://www.youtube.com/watch?v=U3Ne_yX4tYo&list=PL5uoqS92stXioZw-u-ze_NtvSo0k0K0kq

martin9700 · 2019-09-17T09:11:09.000Z

Why not just send the spreadsheet (created from ImportExcel) as an attachment? If you really want it in the email body I’d go with HTML for maximum compatibility (not everyone uses Windows).

DoctorDNS · 2019-09-17T09:33:08.000Z

The two suggestions I can make are: use the Import Excel module to manage the spread sheet.

In terms of assing it in an email - if it were me, I’d just add the spreadsheet as an attachment.

furicle · 2019-09-17T12:56:54.000Z

First guess without looking at it really, get it to echo the value of first and last row to make sure they are what you think they are.