[VBA] Change size of a pasted chart from Excel into an email

[Shewie]

Hi,
I'm having a problem with a chart that I'm pasting from an excel sheet to an email: I can't set the size of the chart.
Can you help me please?

Set oLookApp = GetObject(, "Outlook.Application")
Set ChrtObj = ActiveSheet.ChartObjects(1)
      ChrtObj.Chart.ChartArea.Copy

With oLookItm
        .To = "xyz"
        .CC = "abc"
        .HTMLBody "aaaaa"

Set oWdEditor = .GetInspector.WordEditor

Set oWdRng = oWdEditor.Application.ActiveDocument.Content
      oWdRng.InsertAfter " " & vbNewLine
      oWdRng.Collapse Direction:=wdCollapseEnd

      oWdRng.Paste

I tried to add

.LockAspectRatio =msoFalse
.Height = 1000
.Widht = 1000

But it didn't work.

Thanks a lot!

 

[Domenic]

After pasting the chart, try adding the following code...

    With oWdEditor
        With .InlineShapes(.InlineShapes.Count)
            .LockAspectRatio = msoFalse
            .Width = 1000
            .Height = 1000
        End With
    End With

Hope this helps!

 

[Shewie]

After pasting the chart, try adding the following code...

    With oWdEditor
        With .InlineShapes(.InlineShapes.Count)
            .LockAspectRatio = msoFalse
            .Width = 1000
            .Height = 1000
        End With
    End With

Hope this helps!

WoW!
Thank you so much, it works!

Could you please explain me the logic behind?

Thanks a lot!

 

[Domenic]

You're very welcome, glad I could help.

With regards to the logic, when the chart is added to the document, it's added to the InlineShapes collection. As each one is added to the collection, it's assigned an index number starting with 1. Therefore, we have the following...

oWdEditor.InlineShapes.Count --> returns the number of inline shapes that the document contains

oWdEditor.InlineShapes(oWdEditor.InlineShapes.Count) --> refers to the last inline shape that was copied and pasted into the document

For efficiency, I simply used the With/End With statement to refer to the last inline shape...

    With oWdEditor
        With .InlineShapes(.InlineShapes.Count)
            .LockAspectRatio = msoFalse
            .Width = 1000
            .Height = 1000
        End With
    End With

Cheers!