PS Help for Outlook

PowerShell Help
1

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 to send the excel sheet in the mail but I need to insert the image in body before excel sheet which is not happening.Kindly help me on this

$outlook = new-object -comobject Outlook.Application

$newmail = $outlook.CreateItem(0)
$newmail.Display()

$newmail.Recipients.Add(“user@domain.com”)
$newMail.Subject = “Testing -$(Get-Date)”

Add-PSSnapin Microsoft.Exchange.Management.Powershell.Admin -erroraction silentlyContinue
$att = new-object Net.Mail.Attachment(‘C:/Users/test/Desktop/mail.jpg’)
$att.ContentId = “att”
$newmail.Body = ‘’
$newmail.Attachments.Add($att)

$excel = New-Object -comobject Excel.Application
$FilePath = “C:\Fileloaction.xlsx”
$ReportWorkBook = $excel.Workbooks.Open($FilePath)
$excel.Visible = $false
$ws = $ReportWorkBook.Worksheets.Item(1)
$SelectedRange = $ws.UsedRange
$SelectedRange.Copy()
$newmail.GetInspector.WordEditor.Range().Paste()

2

Test

3

There are a number of examples on the web on how to do this. NO reason to do this from scratch except as a learning effort. Even then, leveraging existing working code as part of the learning process is still prudent.

Using you posts’ title in any search engine will reveal a number of code blocks you can download and tweak as needed.

For example:

Send-MailMessage function with support for inline attachments

Embed inline attachments into HTML bodies with a new Send-MailMessage function.

The Send-MailMessage cmdlet exposes most of the basic functionality for sending email, but you don’t get a lot of control over things like attachments or alternate views. Someone on the TechNet forums was asking how to embed images into an HTML mail message, and I decided to write a version of Send-MailMessage that supports this. I started with a proxy function for the Send-MailMessage cmdlet, so all of the parameters and usage should be intact. The main difference is that I added an -InlineAttachments argument, which accepts a hashtable of pairs in the format ‘ContentId’='FilePath. You can then embed the resources into an HTML body by using URLs of the format “cid:ContentId”.

‘Browse code samples | Microsoft Learn’

Send HTML emails via Powershell

Born from a need to send something better then plain text emails, this script allows for rich email content, as well as including inline attached images (which means Outlook will display them by default
internally, and won’t nag about security.)

‘Send HTML emails via Powershell - Script Center - Spiceworks’

Send Email with images in PowerShell 'sharepointfire.com/2011/12/send-email-with-images-in-powershell'
How to embed Images in a powershell email using MailMessage 'stackoverflow.com/questions/29237687/how-to-embed-images-in-a-powershell-email-using-mailmessage'
(PowerShell) Embed Image in HTML Email 'example-code.com/powershell/smtp_EmbedImage.asp'
4

Actually I am trying this below commands where the data from the sheet is not copying.Kindly help 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]$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

5

Hello Shiva,

Actually I have to agree with postanote on this one.
Hope you can do what you need.

Regards

6

I have got this script which is working where i need to select with Range where the hidden formulas are present inside the excel so only ranges needs to selected how to do this ?I need to add hyperlink for the Introduction which is not happening?

$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 = “recipient@domain.com”
$ws.MailEnvelope.Item.Subject = “My subject”
$ws.MailEnvelope.Item.Send()

7

B
$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”
}

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

$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.Body = "Here is the contents of the excel file

$ForEmail

"
$smtp.Send($msg)

I am integrating the html which is not working Help needed

8

What is not working?

As-is, I don’t see where you’re producing valid HTML in the first place. How is $ForEmail getting HTML in it?

Also, your Body string is not really well-formed. If you’re using -BodyAsHTML, then the ENTIRE BODY needs to be HTML, which means it needs to start with HTML. Yours does not; it starts with “Here is the contents of the excel file,” which is not HTML. As a test, ensure that $ForEmail contains valid HTML, and then set Body = $ForEmail with no other text.

Also, since you’re being very pushy in asking for help on this, please at least take the time to format your code in your posts, as clearly explained in the bullet list above the posting text box. Thank you.

9

Actually I have .html file with tables,i dont know how to integrate here

10

$body = Get-Content myfile.htm

11

$x1 = New-Object -comobject Excel.Application

$UserWorkBook = $x1.Workbooks.Open(“C:\Location\file.xlsx”)

$UserWorksheet = $UserWorkBook.Worksheets.Item(1)

$UserWorksheet.activate()

$rgeSource=$UserWorksheet.range(“A1”,“I32”)

$rgeSource.Copy() | out-null

$Results = Get-Clipboard -TextFormatType Html

$message = @{

From = ‘user1@domain.com’

To = ‘user2@domain.com’

Subject = “Test Mail -$(Get-Date)”

BodyAsHtml = $true

}

$Server = @{

smtp = ‘smtp.domain.com’

}

$message.Body= "This is the test mail

$Results

Regards,

Username

"
Send-MailMessage @message @Server

Now i got the output as such in the Table but the issue is that I get this above the table
How to get rid of this issue ?

Version:1.0 StartHTML:0000000159 EndHTML:0000053673 StartFragment:0000005518 EndFragment:0000053621 SourceURL:file:///Location/file.xlsx