How to size and wrap a photo of a range in an email?

Question

I have a daily task to copy a range of cells into an email.

I managed to do this, with the range being formatted as a photo. (I learnt that copying a range in table format isn't as straight forward as it seems).

How do I code the size and format of the photo produced, height: 9cm, width: 28cm, format as square?

I think I need to do this via the Word editor. I tried .style.width but it throws an error.

Sub email()

    Dim ol As Object 'Outlook.Application
    Dim olEmail As Object 'Outlook.MailItem
    Dim olInsp As Object 'Outlook.Inspector
    Dim wd As Object 'Word.Document

    Sheets("Daily message").Range("B3:L21").SpecialCells(xlCellTypeVisible).Copy

    Set ol = GetObject(, "Outlook.Application") '/* if outlook is running, create otherwise */
    Set olEmail = ol.CreateItem(0) 'olMailItem

    With olEmail
        Set olInsp = .GetInspector
        If olInsp.EditorType = 4 Then 'olEditorWord
            Set wd = olInsp.WordEditor
            wd.Range.PasteAndFormat 13 'wdChartPicture
        End If
        .To = "my email"
        .BCC = ""
        .Subject = "Daily message"
        .Display
    End With

End Sub

Have you seen [THIS](https://stackoverflow.com/questions/57909276/rangetohtml-image-in-cell)? — Siddharth Rout, Sep 20 '21 at 18:05
Does this answer your question? [RangeToHTML & Image in Cell](https://stackoverflow.com/questions/57909276/rangetohtml-image-in-cell) — Eugene Astafiev, Sep 20 '21 at 19:19

Eugene Astafiev · Answer 1 · 2021-09-21T08:50:42.660

0

You can use the HTMLBody property to format the pasted image and the message body in general.

There are three main ways for setting the body of Outlook items:

The Body property (a plain text).
The HTMLBody property - allows customizing the body using the HTML markup as shown above.
The Word object model. Outlook uses Word as an email editor by default. The WordEditor property of the Inspector class returns an instance of the Document class which represents the body.

You can read more about these ways in Chapter 17: Working with Item Bodies.

In HTML you can use the style attribute to specify the width and height of an image.

<img src="img1.jpg" style="width:500px;height:600px;">

Alternatively, you can use the width and height attributes:

<img src="img2.jpg" width="500" height="600">

The width and height attributes always define the width and height of the image in pixels.

edited Sep 21 '21 at 08:50

answered Sep 20 '21 at 22:52

Eugene Astafiev

47,483
3
24
45

Hi Eugene - thanks so much for your answer. I'm aware of the word model, however not being particularly well versed in VBA code I found myself at a bit of a loss. Using .HTMLBody, how would I define the desired size? – nattymcpatty Sep 21 '21 at 08:24
You can use `width` and `height` attributes in HTML. – Eugene Astafiev Sep 21 '21 at 08:51
Apologies - I'm not sure how to put this into the code as the sample code using img src doesn't apply to what I've done - I'm guessing the formatting will need to be within the word editor bit however I am unsure what word editor expression I would need to use to tell it to format width & height. – nattymcpatty Sep 21 '21 at 14:00

score 0 · Answer 2 · answered Feb 27 '22 at 17:13

Managed to get this to work!

Sub RangeToOutlook_Single()

Dim oLookApp As Object
Dim oLookItm As Object
Dim oLookIns As Object

Dim oWrdDoc As Object
Dim oWrdRng As Object

Dim ExcRng As Range

On Error Resume Next

Set oLookApp = GetObject(, "Outlook.Application")


    If Err.Number = 429 Then

        Err.Clear
    
        Set oLookApp = Object
        
    End If
    
Set oLookItm = oLookApp.CreateItem(olMailItem)


Set ExcRng = ActiveWorkbook.Sheets("Sheet1").Range("B4:L26")

With oLookItm

    //Email
    .To = ActiveWorkbook.Sheets("Sheet1").Range("X1")
    .CC = ActiveWorkbook.Sheets("Sheet1").Range("V1")
    .Subject = "Here are all of my Ranges"
    .Body = "Here are all the Ranges from my worksheet."
    
    'Display the email
    .Display
    
    'Get the Active Inspector
    Set oLookIns = .GetInspector
    
    'Get the document within the inspector
    Set oWrdDoc = oLookIns.WordEditor

    
    ExcRng.Copy
    
    Set oWrdRng = oWrdDoc.Application.ActiveDocument.Content
        oWrdRng.Collapse Direction:=wdCollapseEnd
        
    Set oWrdRng = oWdEditor.Paragraphs.Add
        oWrdRng.InsertBreak
                
    oWrdRng.PasteSpecial DataType:=wdPasteMetafilePicture


End With

How to size and wrap a photo of a range in an email?

2 Answers2