Mail + Excel Sheet 1 +Powershell +SMTP + Copy Data to building

Hi Team,

The smtp mail needs to be send by copying the sheet 1 data of the excel sheet.I am trying this commands where the sheet 1 is not copying to the building of the mail.Help me on the same

$filename = 'file.xlsx'
$From ="oo@domain.com"
$to = "rr@domain.com"
$SMTP= "smtp@domain.com"
$filepath = "C:\Location"


$excel = New-Object -comobject Excel.Application
$ReportWorkBook = $excel.Workbooks.Open($filePath)
$excel.Visible = $false
$ws = $ReportWorkBook.Worksheets.Item(1)
$SelectedRange = $ws.UsedRange
$SelectedRange.Copy()
$newmail.GetInspector.WordEditor.Range().Paste()

Function sendEmail([string]$emailFrom, [string]$emailTo, [string]$subject,[string]$building,[string]$smtpServer,[string]$filepath)
{
#initate message
$email = New-Object System.Net.Mail.MailMessage
$email.From = $emailFrom
$email.To.Add($emailTo)
$email.Subject = $subject
$email.building = $building
#initiate email attachment


$emailAttach = New-Object System.Net.Mail.Attachment $newmail.GetInspector.WordEditor.Range().Paste()
$email.Attachments.Add($emailAttach)


#initiate sending email
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($email)
}
Send-MailMessage -From $From -To $to -Subject "Test Report -$(Get-Date)" -BodyAsHtml "Report" -SmtpServer $SMTP

  • Idea : Have the building and Copy the Excel Sheet 1 to the SMTP Mail.Kindly help on this issue

  • The table format is not coming correctly kindly help me on this 

    $email = "someone@somecompany.com"
    $smtpServer = "mail.somecompany.com"
    $ForEmail = @()
    ######################################
    #Create and get my Excel Obj
    $x1 = New-Object -comobject Excel.Application
    $UserWorkBook = $x1.Workbooks.Open("C:\temp\bluenose.xlsx")

    #Select first Sheet
    $UserWorksheet = $UserWorkBook.Worksheets.Item(1)
    $UserWorksheet.activate()

    #Copy the part of the sheet I want in the Email
    $rgeSource=$UserWorksheet.range("A1","E20")
    $rgeSource.Copy() | out-null

    $Results = Get-Clipboard
    foreach ($Result in $Results)
    {
    $ForEmail += "$Result<br>"
    }

    ######################################

    $msg = new-object Net.Mail.MailMessage
    $smtp = new-object Net.Mail.SmtpClient($smtpServer)

    $msg.From = "ExcelPaste@somecompany.com"
    $msg.To.Add($email)
    $msg.Subject = "Excel Pasted"
    $msg.IsBodyHtml = $True

    $msg.building = "Here is the contents of the excel file<br>
    <br>
    $ForEmail
    <br>
    "
    $smtp.Send($msg)