VBA_Novice_0797
New Member
- Joined
- Jun 7, 2020
- Messages
- 7
- Office Version
- 2016
- Platform
- 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.
I have tried to add a variable to act as the count in this code, with:
.
This does not work.
I have also tried the below, with no success:
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,
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,