Results 1 to 2 of 2

Thread: VBA created Image displays as blank after execution
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2018
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking VBA created Image displays as blank after execution

    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

  2. #2
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,855
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default Re: VBA created Image displays as blank after execution

    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...

    Code:
    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?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •