VBA created Image displays as blank after execution

[manicmighty]

I have used the below VB code to copy a range from my excel sheet to create an image file which I then add to the body of an email:

Once the file has been created it then sometimes displays the image but more often than not it comes back as blank showing just a border; can anyone advise on how to amend the code to ensure the image is displayed each time I run the report.

Thanks

Sub CreateEmailImageEOD()

Selection.CopyPicture xlScreen, xlBitmap
Dim mchart As ChartObject
Set mchart = Sheets("Report_ENDOFDAY").ChartObjects.Add(0, 2500, 50, 50)
Sheets("Report_ENDOFDAY").Select
Range("B2:AB35").Select
mchart.Name = "EOD"
mchart.Width = Selection.Width
mchart.Height = Selection.Height
Sheets("Report_ENDOFDAY").ChartObjects("EOD").Activate
ActiveChart.Paste
ActiveChart.Export ThisWorkbook.Path & "EOD.bmp", "bmp"
mchart.Delete

End Sub

 

[Domenic]

First, I'm assuming that you want the width and height of your image the same size as your selected range of cells. Secondly, it looks like you're missing a backslash (\) from your destination path and filename. Accordingly, try the following instead...

Option Explicit

Sub CreateEmailImageEOD()


    Selection.CopyPicture xlScreen, xlBitmap
    
    Dim chrtObj As ChartObject
    With Selection
        Set chrtObj = ActiveSheet.ChartObjects.Add(0, 0, .Width, .Height)
    End With
    
    With chrtObj
        .Activate
        .Name = "EOD"
        With .Chart
            .ChartArea.Format.Line.Visible = msoFalse
            .Paste
            .Export ThisWorkbook.Path & "\EOD.bmp", "bmp"
        End With
        .Delete
    End With
    
End Sub

Does this help?