Changing the sizing when pasting image to Outlook via macro

[mikeellinas]

Hi. I have a Module that copies and pastes into a new outlook email:

Sub Email()

'Copy range of interest
Dim r As Range
Set r = Range("A1:E24")
r.Copy
'Open a new mail item
Dim outlookApp As Outlook.Application
Set outlookApp = CreateObject("Outlook.Application")
Dim outMail As Outlook.MailItem
Set outMail = outlookApp.CreateItem(olMailItem)
With outMail
.CC = "xxxxxx"
.Subject = "xxxxxxxx"
End With

'Get its Word editor
outMail.Display
Dim wordDoc As Word.Document
Set wordDoc = outMail.GetInspector.WordEditor
'To paste as picture
wordDoc.Range.PasteAndFormat wdChartPicture
End Sub

This works great for me with one exception. The image is small and blurry in the outlook window. When I manually re-size it larger in the outlook window before sending, it looks great. After re-sizing, I clicked on image properties and in the Layout/Size tab, it says Height is absolute 5.84"; Width is Absolute 12.27"; Scale Height and Width are both 80% with "lock aspect ratio" and "relative to original picture size" boxes both checked. The original size says 7.29" and original width says 15.28".

Can someone help me program the resizing to happen automatically as part of my module?

 

[Kenneth Hobson]

I haven't tested but maybe try r.CopyPicture?

 

[mikeellinas]

Thanks for the suggestion, Kenneth. Unfortunately, this did not look any different. I am hoping for a way to manually code the desired size details noted above if possible.

 

[Kenneth Hobson]

I don't understand about the resizing to be bigger but it is smaller than your original. Were you wanting to resize it to original?

If you copy and paste to Word, is it what you want?

Maybe record a macro in Word to see what syntax you need for the resize?

Paste and PasteSpecial migth be worth looking into.

 

[mikeellinas]

Thanks again for replying.

When the macro pastes into outlook, the image is too small. It does not matter if I change the size in the original. It always pastes the same size in outlook. What I am asking is this: is there a way to write a re-size into my code above?

I cannot use the recorder for this because I have multiple other things going on.

PasteSpecial did not work either unfortunately.

 

[Kenneth Hobson]

I don't understand the macro recording comment. Macro recording is done just as you do for Excel marco recording. It is a tool. That is the beauty of Excel-Word-Outlook. e.g. Select your range in Excel, and then copy and paste into Word. Then start the recording to resize it....

I am not sure how you got the original sizes. Ranges are not in those dimensions per se. Was it just a range or did you have objects in the range too?

I will try some things to see if I can see what you do and how to get what you want. There are probably several ways to do it.

If you like, post a simple example from a free shared site like dropbox.com.

 

[mikeellinas]

I just copied and pasted in Word. It looks worse than outlook. I then copied from word to outlook and it looks even worse.

The size info I mentioned in my OP were details I found when right clicking and choosing format on the image in outlook after I had manually resized it.

What examples are you asking for?

 

[Kenneth Hobson]

The xlsx or xlsm with the range to copy is the file for me to test.

The point of recording is not resolution but how to resize it. Of course not all things can be recorded.

 

[mikeellinas]

thanks again. Unfortunately, I cannot share the workbook as it has proprietary information. Thanks for trying. Hopefully I can figure it out soon

 

[mikeellinas]

this is weird. When I manually copy in excel, paste in excel as picture, then the picture is a good size and is very clear. I wonder why the macro distorts and shrinks the image.