# Convert range in Excel into picture and then embed into email

**URL:** <https://forums.powershell.org/t/convert-range-in-excel-into-picture-and-then-embed-into-email/24732>\
**Category:** PowerShell Help\
**Created:** [September 12, 2024, 5:29pm UTC](https://forums.powershell.org/t/convert-range-in-excel-into-picture-and-then-embed-into-email/24732 "2024-09-12T17:29:39Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![mp\_power](https://avatars.discourse-cdn.com/v4/letter/m/73ab20/32.png) [@mp\_power](https://forums.powershell.org/u/mp_power)\
**Post date:** [September 12, 2024, 5:29pm UTC](https://forums.powershell.org/t/convert-range-in-excel-into-picture-and-then-embed-into-email/24732/1 "2024-09-12T17:29:39Z")

</div>

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.

```auto
# 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()
}

```

---

<div class="post-metadata">

**Author:** ![matt-bloomfield](https://avatars.discourse-cdn.com/v4/letter/m/dec6dc/32.png) [@matt-bloomfield](https://forums.powershell.org/u/matt-bloomfield)\
**Post date:** [September 12, 2024, 8:26pm UTC](https://forums.powershell.org/t/convert-range-in-excel-into-picture-and-then-embed-into-email/24732/2 "2024-09-12T20:26:09Z")

</div>

Hi, welcome to the forum 👋

With the wonderful [`ImportExcel` module](https://github.com/dfinke/ImportExcel) you can simplify the image creation. Unfortunately, a bug currently limits the `Convert-ExcelRangeToImage` cmdlet to PowerShell 5.1.

```PowerShell
$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:

```PowerShell
$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:

> **[Send-MgUserMail (Microsoft.Graph.Users.Actions)](https://learn.microsoft.com/en-us/powershell/module/microsoft.graph.users.actions/send-mgusermail?view=graph-powershell-1.0)**
>
> Send the message specified in the request body using either JSON or MIME format. When using JSON format, you can include a file attachment in the same sendMail action call. When using MIME format: This method saves the message in the Sent Items...

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

> **[Mailozaurr 1.0.0](https://www.powershellgallery.com/packages/Mailozaurr/1.0.0)**
>
> Mailozaurr is a PowerShell module that aims to provide SMTP, POP3, IMAP and few other ways to interact with Email. Underneath it uses MimeKit and MailKit and EmailValidation libraries written by Jeffrey Stedfast.

---

<div class="post-metadata">

**Author:** ![mp\_power](https://avatars.discourse-cdn.com/v4/letter/m/73ab20/32.png) [@mp\_power](https://forums.powershell.org/u/mp_power)\
**Post date:** [September 16, 2024, 12:28pm UTC](https://forums.powershell.org/t/convert-range-in-excel-into-picture-and-then-embed-into-email/24732/3 "2024-09-16T12:28:50Z")

</div>

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!

```auto
# 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()
}

```

---

<div class="post-metadata">

**Author:** ![matt-bloomfield](https://avatars.discourse-cdn.com/v4/letter/m/dec6dc/32.png) [@matt-bloomfield](https://forums.powershell.org/u/matt-bloomfield)\
**Post date:** [September 16, 2024, 1:40pm UTC](https://forums.powershell.org/t/convert-range-in-excel-into-picture-and-then-embed-into-email/24732/4 "2024-09-16T13:40:14Z")

</div>

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:

```PowerShell
# 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()

```

---

<div class="post-metadata">

**Author:** ![mp\_power](https://avatars.discourse-cdn.com/v4/letter/m/73ab20/32.png) [@mp\_power](https://forums.powershell.org/u/mp_power)\
**Post date:** [September 16, 2024, 6:29pm UTC](https://forums.powershell.org/t/convert-range-in-excel-into-picture-and-then-embed-into-email/24732/5 "2024-09-16T18:29:08Z")

</div>

Perfect!!! Thank you sooooo much!!!

---

<div class="post-metadata">

**Author:** ![mp\_power](https://avatars.discourse-cdn.com/v4/letter/m/73ab20/32.png) [@mp\_power](https://forums.powershell.org/u/mp_power)\
**Post date:** [September 17, 2024, 2:48pm UTC](https://forums.powershell.org/t/convert-range-in-excel-into-picture-and-then-embed-into-email/24732/6 "2024-09-17T14:48:24Z")

</div>

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

```auto
###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!

```auto
# 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()
###}

```

---

<div class="post-metadata">

**Author:** ![krzydoug](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/krzydoug/32/1008_2.png) [@krzydoug](https://forums.powershell.org/u/krzydoug)\
**Post date:** [September 17, 2024, 8:14pm UTC](https://forums.powershell.org/t/convert-range-in-excel-into-picture-and-then-embed-into-email/24732/7 "2024-09-17T20:14:36Z")

</div>

> [mp\_power](https://forums.powershell.org/u/mp_power)
> 
> [1d](https://forums.powershell.org/t/convert-range-in-excel-into-picture-and-then-embed-into-email/24732/5)
> 
> Perfect!!! Thank you sooooo much!!!

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

---

<div class="post-metadata">

**Author:** ![mp\_power](https://avatars.discourse-cdn.com/v4/letter/m/73ab20/32.png) [@mp\_power](https://forums.powershell.org/u/mp_power)\
**Post date:** [September 18, 2024, 10:55am UTC](https://forums.powershell.org/t/convert-range-in-excel-into-picture-and-then-embed-into-email/24732/8 "2024-09-18T10:55:36Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![matt-bloomfield](https://avatars.discourse-cdn.com/v4/letter/m/dec6dc/32.png) [@matt-bloomfield](https://forums.powershell.org/u/matt-bloomfield)\
**Post date:** [September 18, 2024, 12:25pm UTC](https://forums.powershell.org/t/convert-range-in-excel-into-picture-and-then-embed-into-email/24732/9 "2024-09-18T12:25:13Z")

</div>

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

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:

- Avoid using the COM object, and instead use the `ImportExcel` module for all Excel tasks.
- Stick to native PowerShell where possible, e.g. you don’t need `[System.IO.File]::Delete()`, use `Remove-Item` instead.
- Make sure you clean-up the COM objects properly, don’t just rely on `Quit()`.  
[Windows PowerShell Tip: Getting Rid of a COM Object (Once and For All) | Microsoft Learn](https://learn.microsoft.com/en-us/previous-versions/windows/it-pro/windows-powershell-1.0/ff730962(v=technet.10))
- You have unused variables in your script: `$image` and `$outlookWasRunning` (now that you’ve commented that out). Using an editor like VSCode will highlight small errors like this and help keep your code tidy.
- For further tips, consult the PowerShell Best Practices & Style Guide:  
[GitHub - PoshCode/PowerShellPracticeAndStyle: The Unofficial PowerShell Best Practices and Style Guide](https://github.com/PoshCode/PowerShellPracticeAndStyle)

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex019/uploads/powershell/original/1X/385e4f9fe133561ad30a814262fe0e0ae6bc3ef0.png) [@system](https://forums.powershell.org/u/system)\
**Post date:** [October 18, 2024, 12:25pm UTC](https://forums.powershell.org/t/convert-range-in-excel-into-picture-and-then-embed-into-email/24732/10 "2024-10-18T12:25:31Z")

</div>

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.