How do I copy a chart in VBA and keep source colors?

Question

I've got a VBA macro that creates a new email in outlook, copies some charts from excel, and pastes them in the new email. It works great, except the colors of the bar charts change after I paste.

If I manually copy and paste into the email and click "keep source formatting", the colors are correct. I can't get an equivalent option to work in vba.

I try to use xlPasteFormats or xlPasteAllUsingSourceTheme, but neither of those keep the correct colors.

I've also tried using CopyPicture instead of Copy, but the quality of the charts don't look right, they look more pixelated.

I've also tried every way I can find to change the default colors in outlook, but that hasn't helped either.

Here's the relevant code:

Sub CopyAndPasteToMailBody()
    Set mailApp = CreateObject("Outlook.Application")
    Set mail = mailApp.CreateItem(olMailItem)
    mail.Display
    Set wEditor = mailApp.ActiveInspector.wordEditor
   
 For Each ws In Worksheets
        If ws.Name = "results" Then
    For Each cht In ws.ChartObjects
    cht.Copy
    With wEditor.Application.Selection
        .Paste
        .InsertAfter vbCrLf
        .Collapse Direction:=wdCollapseEnd
    End With
    Next cht
    End If

https://stackoverflow.com/a/48897439/4539709 – 0m3r May 06 '21 at 20:30 — 0m3r, May 06 '21 at 20:30

score 1 · Answer 1 · answered May 06 '21 at 22:29

1

Name the graphic on a particular page and try to copy it like this

Sub a()

Sheet1.Shapes("graphic1").Copy

Me.Range("C5").PasteSpecial 'I'm copying it to a different page here

End Sub

answered May 06 '21 at 22:29

SzB

70
5

I tried using PasteSpecial, but the result is the same. – user3634056 May 07 '21 at 15:20

score 1 · Answer 2 · answered May 06 '21 at 22:46

1

Next time, please post your code.

When you use CopyPicture, you have two choices:

ActiveChart.CopyPicture Format:=xlBitmap
ActiveChart.CopyPicture Format:=xlPicture

xlPicture keeps the picture in WMF/EMF format (vector graphic, comprised of numerous shapes), which may have a better appearance. If I'm using Gmail, I can only paste a bitmap, but maybe Outlook is more flexible. I've never seen much pixelation of a bitmap, though, unless the picture is resized.

answered May 06 '21 at 22:46

Jon Peltier

5,895
1
27
27

If I use xlPicture, it looks the same and looks pixelated. if I use xlBitmap, it says, "Run-time error '5': Invalid Procedure call or arguement". I'll add my code to the original post. – user3634056 May 07 '21 at 15:21

How do I copy a chart in VBA and keep source colors?

2 Answers2