Convert range in Excel into picture and then embed into email

PowerShell Help
1

What I’m doing now is simply refreshing a spreadsheet and emailing it out as an attachment. I would like to get a certain range in the spreadsheet and convert it into a jpg and then embed that into the body of the email. Hopefully this can also be done without actually having to open the spreadsheet. Below is just the code I’m using to send the email with the attachment. I got the bulk of this code online. I’m just starting out the PowerShell. I’m in the process of converting my auto generated Excel reports that use VBA in the Workbook_Open module to get the job done. I was able to find VBA code showing me how to get the pic into the email body but would LOVE to do it in PowerShell.

# Check if Outlook is running
if (!(Get-Process "OUTLOOK" -ErrorAction SilentlyContinue)) {
    # Start Outlook if it's not running
    Start-Process "OUTLOOK"
    # Flag to track whether Outlook was open before running the script
    $outlookWasRunning = $false
} else {
    # Outlook was already running
    $outlookWasRunning = $true
}

# Open the Excel file
$excel = New-Object -ComObject Excel.Application
$workbook = $excel.Workbooks.Open("C:\ScheduledReports\DSR\DSRBaseFile\DSRBaseFile.xlsx")


# Refresh the data in the workbook
$workbook.RefreshAll()

# Add a wait timer to allow the connection refresh to complete
Start-Sleep -Seconds 30


# Save the workbook to DailyUpdateFile location
$newFileName = "DSR.xlsx"
$ReportName = "DSR"


if (Test-Path "C:\ScheduledReports\DSR\DSRDailyUpdateFile\$newFileName") {
    # Overwrite the existing file
    [System.IO.File]::Delete("C:\ScheduledReports\DSR\DSRDailyUpdateFile\$newFileName")
}

# Remove the queries as they are not needed in the new file
foreach ($query in $workbook.Queries) {
    $query.Delete()
}

# Save the workbook as a new file
$excel.DisplayAlerts = $false
$workbook.SaveAs("C:\ScheduledReports\DSR\DSRDailyUpdateFile\$newFileName")

# Close the workbook and Excel application
$workbook.Close()
$excel.Quit()

# Create and send an email
$ol = New-Object -ComObject Outlook.Application
$mail = $ol.CreateItem(0)
$mail.To = "someone@somewhere.com"
$mail.CC = ""
$mail.Subject = "DSR"
$mail.Body = "See attached $ReportName.  Please do not reply to this email.  For questions or concerns reach out to admin@company.com"
# Attach the file to the email if it exists
if (Test-Path "C:\ScheduledReports\DSR\DSRDailyUpdateFile\$newFileName") {
    $mail.Attachments.Add("C:\ScheduledReports\DSR\DSRDailyUpdateFile\$newFileName")
}
$mail.Send()

# Add a wait timer to keep Outlook open long enough to send the email
Start-Sleep -Seconds 15
$excel.Quit()
# Close Outlook if it wasn't running before the script
if (!$outlookWasRunning) {
    $ol.Quit()
}
2

Hi, welcome to the forum :wave:

With the wonderful ImportExcel module you can simplify the image creation. Unfortunately, a bug currently limits the Convert-ExcelRangeToImage cmdlet to PowerShell 5.1.

$ConvertParam = @{
    Path          = 'E:\Temp\addressBook.xlsx'
    WorksheetName = 'Sheet1'
    Range         = 'A1:F3'
    Destination   = 'E:\Temp\xlAddressBook.jpg'
}

Convert-ExcelRangeToImage @ConvertParam

Now that you have the image, you can embed it in an HTML message by attaching it to the e-mail and referencing it in the HTML:

$MessageBody = @"
<html>
<head></head>
<body>
Hi,<br>
Here's the image you wanted:<br>       
<img id="1" src="cid:xlAddressBook.jpg">
</body>
</html>
"@

$Outlook = New-Object -ComObject Outlook.Application
$Message = $Outlook.CreateItem(0)
$Message.To = 'recipient@somedomain.com'
$Message.Subject = 'That Excel Image You Wanted'
$Message.HTMLBody = $MessageBody
$Message.Attachments.Add('E:\Temp\xlAddressBook.jpg')

# Message Preview
$Inspector = $Message.GetInspector
$Inspector.Activate()

Note: there are better methods than COM object for sending messages. My preferred method is the Graph API:

Another option is Mailozaurr which is the recommended drop-in replacement for the deprecated Send-MailMessage. Not used that myself though.

1 Like
3

Thank you Matt! You got me this far: I am able to “attach” the picture to the email, but I can’t figure out how to “embed” the picture in the body of the email. So the script is just putting both the Excel file attachment AND the picture attachment in the email. I read a little about Mailozaurr but as a noob, I can’t figure out how to use it (or if it is entirely necessary). I was able to get it installed however! Can you fix up what I have below to get the image embedded instead of attached? Again, MANY THANKS!

# Check if Outlook is running
if (!(Get-Process "OUTLOOK" -ErrorAction SilentlyContinue)) {
    # Start Outlook if it's not running
    Start-Process "OUTLOOK"
    # Flag to track whether Outlook was open before running the script
    $outlookWasRunning = $false
} else {
    # Outlook was already running
    $outlookWasRunning = $true
}

# Open the Excel file
$excel = New-Object -ComObject Excel.Application
$workbook = $excel.Workbooks.Open("C:\ScheduledReports\DSRtestpic\DSRtestpicBaseFile\DSRtestpicBaseFile.xlsx")


# Refresh the data in the workbook
$workbook.RefreshAll()

# Add a wait timer to allow the connection refresh to complete
Start-Sleep -Seconds 30

$newFileName = "DSR.xlsx"
$ReportName = "DSR"


# Save the workbook with the current date
if (Test-Path "C:\ScheduledReports\DSRtestpic\DSRtestpicDailyUpdateFile\$newFileName") {
    # Overwrite the existing file
    [System.IO.File]::Delete("C:\ScheduledReports\DSRtestpic\DSRtestpicDailyUpdateFile\$newFileName")
}

# Remove the queries as they are not needed in the new file
foreach ($query in $workbook.Queries) {
    $query.Delete()
}

# Save the workbook as a new file
$excel.DisplayAlerts = $false
$workbook.SaveAs("C:\ScheduledReports\DSRtestpic\DSRtestpicDailyUpdateFile\$newFileName")


#pic routine
$ConvertParam = @{
    Path          = 'C:\ScheduledReports\DSRtestpic\DSRtestpicBaseFile\DSRtestpicBaseFile.xlsx'
    WorksheetName = 'Sheet3'
    Range         = 'A1:E7'
    Destination   = 'C:\ScheduledReports\DSRtestpic\picture\DSR.jpg'
}

Convert-ExcelRangeToImage @ConvertParam


# Close the workbook and Excel application
$workbook.Close()
$excel.Quit()


# Create and send an email
$image = 'C:\ScheduledReports\DSRtestpic\picture\DSR.jpg'
$ol = New-Object -ComObject Outlook.Application
$mail = $ol.CreateItem(0)
$mail.To = ""
$mail.CC = "someone@mycompany.com"
$mail.Subject = "DSR"
$mail.Body = "See attached $ReportName. For quick screenshot, please open DSR.jpg. `nPlease do not reply to this email.  For questions or concerns reach out to dhausner@marcalpaper.com" 
$mail.IsBodyHTML = $true
# Attach the file to the email if it exists
if (Test-Path "C:\ScheduledReports\DSRtestpic\DSRtestpicDailyUpdateFile\$newFileName") {
    $mail.Attachments.Add("C:\ScheduledReports\DSRtestpic\DSRtestpicDailyUpdateFile\$newFileName")
    $mail.Attachments.Add("C:\ScheduledReports\DSRtestpic\picture\DSR.jpg")
}
$mail.Send()

# Add a wait timer to keep Outlook open long enough to send the email
Start-Sleep -Seconds 15
$excel.Quit()
# Close Outlook if it wasn't running before the script
if (!$outlookWasRunning) {
    $ol.Quit()
}
4

The attachment is referenced using the CID (Content-ID) value in the HTML. You just need to modify the body of the message to include the attachment name:

# Create and send an email
$messageBody = @"
<html>
<head></head>
<body>
<p>See attached $ReportName.</p>
<p>For a quick screenshot, please see below.</p>
<p>Please do not reply to this email.<br>  
For questions or concerns reach out to me@mycompany.com</p>
<img id="1" src="cid:DSR.jpg">
</body>
</html>
"@

$ol = New-Object -ComObject Outlook.Application
$mail = $ol.CreateItem(0)
$mail.To = ""
$mail.CC = "someone@mycompany.com"
$mail.Subject = "DSR"
$mail.HTMLBody = $messageBody 

# Attach the file to the email if it exists
if (Test-Path "C:\ScheduledReports\DSRtestpic\DSRtestpicDailyUpdateFile\$newFileName") {
    $mail.Attachments.Add("C:\ScheduledReports\DSRtestpic\DSRtestpicDailyUpdateFile\$newFileName")
    $mail.Attachments.Add("C:\ScheduledReports\DSRtestpic\picture\DSR.jpg")
}
$mail.Send()
1 Like
5

Perfect!!! Thank you sooooo much!!!

6

Well, after having some time to dig in, I found one issue with the script below. It is NOT overwriting the jpg file located in C:\ScheduledReports\DSR\picture\DSR.jpg

Also, I commented out a bunch of code because I don’t think it’s needed. I’m also not sure what other code is not needed (especially when it comes to sending the email out).
I also had to comment out

###if (!$outlookWasRunning) {
  ### $ol.Quit()
###}

because it was closing Outlook. In all the other files I send out (without any picture stuff, just a reguler file attachment), Outlook stays open (which is what I want).

Again, any help getting this all cleaned up is gratly appreciated!

# Check if Outlook is running
if (!(Get-Process "OUTLOOK" -ErrorAction SilentlyContinue)) {
    # Start Outlook if it's not running
    Start-Process "OUTLOOK"
    # Flag to track whether Outlook was open before running the script
    $outlookWasRunning = $false
} else {
    # Outlook was already running
    $outlookWasRunning = $true
}

# Open the Excel file
$excel = New-Object -ComObject Excel.Application
$workbook = $excel.Workbooks.Open("C:\ScheduledReports\DSR\DSRBaseFile\DSRBaseFile.xlsx")


# Refresh the data in the workbook
$workbook.RefreshAll()

# Add a wait timer to allow the connection refresh to complete
Start-Sleep -Seconds 15


$newFileName = "DSR.xlsx"
$ReportName = "DSR"


# Save the workbook with the current date
if (Test-Path "C:\ScheduledReports\DSR\DSRDailyUpdateFile\$newFileName") {
    # Overwrite the existing file
    [System.IO.File]::Delete("C:\ScheduledReports\DSR\DSRDailyUpdateFile\$newFileName")
}

# Remove the queries as they are not needed in the new file
foreach ($query in $workbook.Queries) {
    $query.Delete()
}

# Save the workbook as a new file
$excel.DisplayAlerts = $false
$workbook.SaveAs("C:\ScheduledReports\DSR\DSRDailyUpdateFile\$newFileName")



#pic routine
$ConvertParam = @{
    Path          = 'C:\ScheduledReports\DSR\DSRBaseFile\DSRBaseFile.xlsx'
    WorksheetName = 'Sheet3'
    Range         = 'A1:E7'
    Destination   = 'C:\ScheduledReports\DSR\picture\DSR.jpg'
}

Convert-ExcelRangeToImage @ConvertParam


# Close the workbook and Excel application
$workbook.Close()
$excel.Quit()


# Create and send an email
$image = 'C:\ScheduledReports\DSR\picture\DSR.jpg'


$ol = New-Object -ComObject Outlook.Application

$att1 = new-object Net.Mail.Attachment($Image)
$att1.ContentType.MediaType = “image/jpg”
$att1.ContentId = “Attachment”


$mail = $ol.CreateItem(0)
$mail.To = ""
$mail.CC = "me@company.com"
$mail.Subject = "DSR"

###$mail.IsBodyHTML = $true


# Attach the file to the email if it exists
###if (Test-Path "C:\ScheduledReports\DSR\DSRDailyUpdateFile\$newFileName") {
   ### $mail.Attachments.Add("C:\ScheduledReports\DSR\DSRDailyUpdateFile\$newFileName")
    ###$mail.Attachments.Add("C:\ScheduledReports\DSR\picture\DSR.jpg")
   ###}

$messageBody = @"
<html>
<head></head>
<body>
<p>See attached $ReportName.</p>
<img id="1" src="cid:DSR.jpg">
<p>Please do not reply to this email.<br>  
For questions or concerns reach out to me@company.com</p>

</body>
</html>
"@

$ol = New-Object -ComObject Outlook.Application
$mail = $ol.CreateItem(0)
$mail.To = ""
$mail.CC = "me@company.com"
$mail.Subject = "DSR"
$mail.HTMLBody = $messageBody 

# Attach the file to the email if it exists
if (Test-Path "C:\ScheduledReports\DSR\DSRDailyUpdateFile\$newFileName") {
    $mail.Attachments.Add("C:\ScheduledReports\DSR\DSRDailyUpdateFile\$newFileName")
    $mail.Attachments.Add("C:\ScheduledReports\DSR\picture\DSR.jpg")
}
$mail.Send()


# Add a wait timer to keep Outlook open long enough to send the email
Start-Sleep -Seconds 15
$excel.Quit()
# Close Outlook if it wasn't running before the script
###if (!$outlookWasRunning) {
   ### $ol.Quit()
###}
7

mp_power

1d

Perfect!!! Thank you sooooo much!!!

Are you saying this portion that you marked as the solution didn’t solve your issue?

8

Sorry guys, I should have unchecked it as the “final” solution. Matt did get me what I asked for but when I went back and started to look again a bit later, I noticed that the picture was not being overwritten (again, I wasn’t clear at first and take the blame for that).
So what I did just now is uncheck that it’s the solution (when technically it’s like 95% of the final solution). Again, my apologies. I’m a total noob!!! (both in PowerShell AND this forum). If there is a point system on the forum, I believe matt-bloomfield should get them.

So, my hopefully final ask is to get the script to always overwrite the picture and get it embedded into the email body. I feel what I have now is so very close, but as I wrote in my last post, I think there’s possibly extra stuff in the script that isn’t needed as well as some little extra thing that would always overwrite the picture each time the script is run.

9

No problem. Before you unchecked it, I think it was your own post that was marked as the solution anyway :smiley:

I cannot replicate the picture not being replaced. I don’t even have to delete it,
Convert-ExcelRangeToImage just overwrites an existing file with the new image.

Are you getting any errors?

Are you definitely running it under PowerShell 5.1? As previously mentioned the cmdlet doesn’t work in PowerShell 7+ so if you have a test image or one previously created in PowerShell 5.1 and are now using version 7, because the cmdlet is failing there won’t be a new image.

Regarding cleaning up your code, a few pointers: