PS Help for Outlook

Welcome Forums General PowerShell Q&A PS Help for Outlook

This topic contains 10 replies, has 4 voices, and was last updated by

 
Participant
8 months ago.

  • Author
    Posts
  • #95034

    Participant
    Points: 0
    Rank: Member

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

  • #95058

    Participant
    Points: 0
    Rank: Member

    Test

  • #95091

    Participant
    Points: 227
    Helping Hand
    Rank: Participant

    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".

    'gallery.technet.microsoft.com/scriptcenter/Send-MailMessage-3a920a6d'

    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.)

    'community.spiceworks.com/scripts/show/1037-send-html-emails-via-powershell'

    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'

  • #95142

    Participant
    Points: 0
    Rank: Member

    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

  • #95223

    Participant
    Points: 0
    Rank: Member

    Hello Shiva,

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

    Regards

  • #95507

    Participant
    Points: 0
    Rank: Member

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

  • #96065

    Participant
    Points: 0
    Rank: Member

    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

  • #96083

    Keymaster
    Points: 1,638
    Helping HandTeam Member
    Rank: Community Hero

    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.

  • #96245

    Participant
    Points: 0
    Rank: Member

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

  • #96272

    Keymaster
    Points: 1,638
    Helping HandTeam Member
    Rank: Community Hero

    $body = Get-Content myfile.htm

  • #96408

    Participant
    Points: 0
    Rank: Member

    $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

The topic ‘PS Help for Outlook’ is closed to new replies.