Mail Allignment Issue

Lakshmana

Hi Team,

Actually I am having the Shift Handover file in which Sheet 1 needs to be send mail automatically through Powershell.I am using this link to export the sheet 1 to the mail body where the allignment is not coming good..Kindly help with this script

https://social.technet.microsoft.com/Forums/scriptcenter/en-US/21643bfc-40c0-4b2e-8a75-365361f0654e/...

#Create and get my Excel Obj
$x1 = New-Object -comobject Excel.Application
$UserWorkBook = $x1.Workbooks.Open("C:\MyExcelFile.xls")

#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").Copy()

#create outlook Object
$Outlook = New-Object -comObject Outlook.Application
$Mail = $Outlook.CreateItem(0)
$Mail.Recipients.Add("[email protected]")

#Add the text part I want to display first
$Mail.Body = "My Comment on the Excel Spreadsheet"

#Then Copy the Excel using parameters to format it
$Mail.Getinspector.WordEditor.Range().PasteExcelTable($true,$false,$false)
#Then it becomes possible to insert text before
$wdDoc = $Mail.Getinspector.WordEditor
$wdRange = $wdDoc.Range()
$wdRange.InsertBefore("Hello world!")
$Mail.Display()

RoopanKumar

ena boss automation panuringala work panam ???

Lakshmana

Help Needed to paste the excel sheet inside email body

$filename = 'file.xlsx'
$From ="[email protected]"
$to = "[email protected]"
$SMTP= "[email protected]"
$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]$body,[string]$smtpServer,[string]$filepath)
{
#initate message
$email = New-Object System.Net.Mail.MailMessage 
$email.From = $emailFrom
$email.To.Add($emailTo)
$email.Subject = $subject
$email.Body = $body
#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

dafyre

$SMTP= "[email protected]"

First off, $smtp should be the address of your company's SMTP server... It would be like "mail.domain.com" or something like that -- not an email address.

$filepath ="C:\Location"

needs to be the FULL file path, not just a folder name.

The way this script is laid out, you really don't need the sendEmail function at all. See how it works like this:

(i've only checked this for basic syntax)

$filename = 'file.xlsx'
$From ="[email protected]"
$to = "[email protected]"
$SMTP= "mail.domain.com"
$filepath = "C:\Location\$filename"


$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()

$body = @"

My email content goes here

"@


#initate message
$email = New-Object System.Net.Mail.MailMessage 
$email.From = $emailFrom
$email.To.Add($emailTo)
$email.Subject = $subject
$email.Body = $body

#initiate email attachment
$emailAttach = $SelectedRange.Paste()
$email.Attachments.Add($emailAttach)


#initiate sending email 
$smtp = new-object Net.Mail.SmtpClient($smtp)
$smtp.Send($email)

Lakshmana

@dafyre said in Mail Allignment Issue:

$filename = 'file.xlsx'
$From ="[email protected]"
$to = "[email protected]"
$SMTP= "mail.domain.com"
$filepath = "C:\Location$filename"

$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()

$body = @"

My email content goes here

"@

#initate message
$email = New-Object System.Net.Mail.MailMessage
$email.From = $emailFrom
$email.To.Add($emailTo)
$email.Subject = $subject
$email.Body = $body

#initiate email attachment
$emailAttach = $SelectedRange.Paste()
$email.Attachments.Add($emailAttach)

#initiate sending email
$smtp = new-object Net.Mail.SmtpClient($smtp)
$smtp.Send($email)

Exception calling "Add" with "1" argument(s): "Value cannot be null.
Parameter name: item"
At C:\Reports\excel.ps1:26 char:1
+ $email.To.Add($emailTo)
+ ~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ArgumentNullException
 
Method invocation failed because [System.__ComObject] does not contain a method named 'Paste'.
At C:\Reports\excel.ps1:31 char:1
+ $emailAttach = $SelectedRange.Paste()
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (Paste:String) [], RuntimeException
    + FullyQualifiedErrorId : MethodNotFound
 
Exception calling "Add" with "1" argument(s): "Value cannot be null.
Parameter name: item"
At C:\Reports\excel.ps1:32 char:1
+ $email.Attachments.Add($emailAttach)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ArgumentNullException
 
Exception calling "Send" with "1" argument(s): "A recipient must be specified."
At C:\Reports\excel.ps1:37 char:1
+ $smtp.Send($email)
+ ~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : InvalidOperationException

dafyre

Check through the code, I see a couple of issues I didn't catch the first time.

At the top, change $to="[email protected]" to be $emailTo="[email protected]

I'm not sure about the $SelectedRange.paste() error. You'll have to dig into that one. You had that bit working in one of your earlier scripts.

Fixing the $emailTo at the top should also fix the "Send" last error as well.

Lakshmana

@stess can you help me on this tooo??

stess

@lakshmana
Is the file XLSX or CSV? If it is CSV then the example I provided in an other thread works just fine. XLSX is a lot more trickier as you have to target the cells and what not.

Lakshmana

here the file is xlsx where i need to copy sheet1 only from excel to body of the mail!!

stess

@lakshmana
I'll say I'm not good with excel in powershell. I always avoid dealing with excel just because there are too many area that could goes wrong in the long term.

Lakshmana

whether can i convert. xlsx to. csv andcopy data to body of mail??? ![alt text](image url)

Lakshmana

How to add Hyperlink to the Introduction where <a href="">is not working here

Help needed !!!

$xl=New-Object -ComObject Excel.Application
$wb=$xl.Workbooks.Open('c:\scripts\process.xlsx')
$ws=$wb.Sheets.Item(1)
$ws.MailEnvelope.Introduction = "This is a sample worksheet."
$ws.MailEnvelope.Item.To = "[email protected]"
$ws.MailEnvelope.Item.Subject = "My subject"
$ws.MailEnvelope.Item.Send()

Lakshmana

@stess
@scottalanmiller
I got one script for copying the excel sheet content to the mail body which have issue in it.The issue where the table is not copying to body only contents are copied to the mail body without table.Help needed

$email = "[email protected]"
$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 = "[email protected]"
$msg.To.Add($email)
$msg.Subject = "Excel Pasted"
$msg.IsBodyHtml = $True
 
$msg.Body = "Here is the contents of the excel file<br>
<br>
$ForEmail
<br>
"
$smtp.Send($msg)](![image url](image url))