VBA: Copy cells, paste as a picture, then save as a picture?

Question

I have the following code:

Range("A1:M28").CopyPicture Appearance:=xlScreen, Format:=xlPicture

Sheets.Add.Name = "Without Formatting"
Worksheets("Without Formatting").Paste _
Destination:=Worksheets("Without Formatting").Range("A1:M28")

That copies some cells then pastes them as a picture in another worksheet, is there a way of saving the picture (preferably as a jpeg) into a specific location so that I can then use the picture in an email?

Answer 1

I have used the following in the past. It copies the cells as an image, creates a new chart, paste the image into the chart, export the chart as an image then deletes the chart.

Range("A1:M28").CopyPicture Appearance:=xlScreen, Format:=xlPicture
    Set cht = ActiveSheet.ChartObjects.Add(0, 0, Rng.Width + 0.01, Rng.Height + 0.01)
    cht.Name = "tempchart"
    cht.Chart.Paste
    ActiveChart.Shapes.Range(Array("chart")).Select
        Selection.ShapeRange.Fill.Visible = msoFalse
        Selection.ShapeRange.Line.Visible = msoFalse
    ActiveSheet.ChartObjects("tempchart").Activate
        ActiveSheet.Shapes("tempchart").Fill.Visible = msoFalse
        ActiveSheet.Shapes("tempchart").Line.Visible = msoFalse
    cht.Chart.Export strPath & "c:\filepath\imagename.jpg"
    ActiveSheet.ChartObjects.Delete

Hopefully that will help,