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,
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,472
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.
 

VBA_Novice_0797

New Member
Joined
Jun 7, 2020
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
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
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,939
Office Version
  1. 365
Platform
  1. Windows
Why are you using an image to show the data in the email?
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,472
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:
 

VBA_Novice_0797

New Member
Joined
Jun 7, 2020
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,513
Messages
5,548,499
Members
410,840
Latest member
Kar3ousse
Top