Variable Height Property for Image in Email

VBA_Novice_0797

New Member
Joined
Jun 7, 2020
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
Hello all,

The code below is an extract from a Module written in Excel VBA that is used to create an email by turning active cells into an image, this works as intended.

I am just attempting to expand on the code slightly so that the height of the image that is added to the email each time adapts to the height of the cell in which new data is added.

I am currently using a fixed height =500, which is leading to compression of data and not what I am after.

I need to be able to set height = NewHeight, where NewHeight is a variable that adds the height of new rows to the base height each time it is run.

I have written the code for 'NewHeight' in a separate Module, the code for this works and gives me a number which is updated each time, with the new height of all rows I wish to include.

I believe that it is just a case of getting this number from the cell I paste it to, E20, being recognised as the desired height for the HTML body.

VBA Code:
With OutMail

.To = Test@Test.com

.Subject = Height Adapt Test

.Attachments.Add MakeJPG, 1, 0

.HTMLBody = “<html><p>” & strbody & “</p><img src=””NamePicture.jpg”” width = 400 height = 500 ></html>”

.Display

End With

I have tried to add a variable to act as the count in this code, with:

.
VBA Code:
HTMLBody = “<html><p>” & strbody & “</p><img src=””NamePicture.jpg”” width = 400 height = NewHeight ></html>”

This does not work.

I have also tried the below, with no success:

VBA Code:
 .HTMLBody = “<html><p>” & strbody & “</p><img src=””NamePicture.jpg”” width = 400 height = Worksheets("Home").Range("E20") ></html>”

I hope this makes sense and would be grateful for any suggestions or pointers on how to make the height variable, as opposed to fixed!

If I have been too vague, not given enough info just let me know.

Excel and Windows 2016.

Thanks,
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Untested, maybe:
VBA Code:
.HTMLBody = "<html><p>" & strbody & "</p><img src='NamePicture.jpg' width=400 height=" & Worksheets("Home").Range("E20").Value & "></html>"
Note that HTML allows either double or single quotes for strings - using the latter makes the VBA clearer.
 
Upvote 0
Hi John,

Thank you for this, unfortunately it has not solved the problem.

I would be grateful for any other suggestions on how to potentially resolve this, including any ideas around how to track the height of the rows that I am trying to include, as I appreciate there may be a way to do this within the code.

Thanks
 
Upvote 0
Why are you using an image to show the data in the email?
 
Upvote 0
It's not really clear what your code is doing or how it's meant to work. I would need to see the whole code to understand what you're trying to do.

I need to be able to set height = NewHeight, where NewHeight is a variable that adds the height of new rows to the base height each time it is run.
I don't understand this. What is the 'base height'?

Also, your img tag looks wrong for including an image in an Outlook email - it should be something like this:

VBA Code:
    .HTMLBody = "<html><p>" & strBody & "</p><img src='cid:" & Mid(MakeJPG, InStrRev(MakeJPG, "\") + 1) & "' width=400 height=500></html>"
using your MakeJPG variable, which I assume holds the full name of the image which your code is adding as an attachment. (The Mid function call above returns the filename.extension part).

Have a look at the following code adds a range of cells (which also contains some charts) as an image in an Outlook email:
 
Upvote 0
Thank you, I have realised that the problem with compression was due to the max height on images of 1775 pixels in Outlook.

The original suggestion you made did work but I was not able to see this due to the max height problem!

The base height was the original height of the rows that are present initially.

I have now adapted my formatting so that the height is no longer a problem though.

Thanks for your help!
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top