Moving Charts from excel to mail body.

[Premanshu]

Hello Everyone,

I am working on a code to move some charts from the activesheet into a mail body which generates from with this excel file itself using the VBA code. I have the below code for this task to be done. The problem I am facing is intially when i had run this macro i had some comments in the text box and the charts with a fewer data points and i ran properly, but on the second day when i tried running the same macro instead of inserting the latest comments and the latest charts with more data points it somehow from somewhere picks and inserts the old chart and comment images. I am not sure from where those old images are appearing when they do not exist at all niether in the current excel file nor in the hard disk. I manually deleted the image files from the hard disk and then tried running only the CreateMail sub and it still fetches the old images from somewhere... ideally it should have given an error as the sorce for the image for HTMLBody does not exist at all. Please have a look at the below code and advice:-

Sub Generate_Mail()

Dim MyChart As String, MyPicture As String
Dim PicWidth As Long, PicHeight As Long
Application.ScreenUpdating = True
Application.DisplayAlerts = True


    
    Sheets("Analytics").Shapes("TextBox 6").Select
      MyPicture = Selection.Name
      With Selection
            PicHeight = .ShapeRange.Height
            PicWidth = .ShapeRange.Width
      End With
      
      Charts.Add
      ActiveChart.Location Where:=xlLocationAsObject, Name:="Analytics"
      Selection.Border.LineStyle = 0
      MyChart = Selection.Name & " " & Split(ActiveChart.Name, " ")(2)
      
      With ActiveSheet
            With .Shapes(MyChart)
                  .Width = PicWidth
                  .Height = PicHeight
            End With
            
            .Shapes(MyPicture).Copy
            
            With ActiveChart
                  .ChartArea.Select
                  .Paste
                  '.Export Filename:="D:\MyComment.png", FilterName:="png"
            End With
            
            .Shapes(MyChart).Cut
            
      End With

'
'    Sheets("Analytics").ChartObjects("Chart 1").Chart.Export "D:\Chart1.jpg"
'    Sheets("Analytics").ChartObjects("Chart 2").Chart.Export "D:\Chart2.jpg"
'    Sheets("Analytics").ChartObjects("Chart 3").Chart.Export "D:\Chart3.jpg"
'    Sheets("Analytics").ChartObjects("Chart 4").Chart.Export "D:\Chart4.jpg"
'    Sheets("Analytics").ChartObjects("Chart 5").Chart.Export "D:\Chart5.jpg"

Call CreateMail

Kill "D:\MyComment.png"
Kill "D:\Chart1.jpg"
Kill "D:\Chart2.jpg"
Kill "D:\Chart3.jpg"
Kill "D:\Chart4.jpg"
Kill "D:\Chart5.jpg"

End Sub

Sub CreateMail()

Dim olapp As Object
Dim olmail As Object
Dim abc As Object


Set olapp = CreateObject("Outlook.application")
Set olmail = olapp.CreateItem(0)

With olmail
.display
.To = ""
.htmlbody = .htmlbody & "< img src='D:\MyComment.png' >" & "<br/>" & "<br/>" & "<br/>"
.htmlbody = .htmlbody & "< img src='D:\Chart1.jpg' >" & "< img src='D:\Chart2.jpg' >" & "<br/>" & "<br/>"
.htmlbody = .htmlbody & "< img src='D:\Chart3.jpg' >" & "< img src='D:\Chart4.jpg' >" & "< img src='D:\Chart5.jpg' >"

End With

Set olmail = Nothing
Set olapp = Nothing


End Sub

Regards,
Premanshu

 

[smiler]

hi,

The charts have to be in the specified location, otherwise you would get a red cross (image doesnt exist) - you could try clearing your browser cache as the emails are HTML it could be puling from a cached version.

if you also have any 'on error resume next' trapping in another part of code that might call this process then switch that off to see if some errors are just being skipped

.htmlbody = .htmlbody & "< img src='D:\MyComment.png' >" & "" & "" & ""
.htmlbody = .htmlbody & "< img src='D:\Chart1.jpg' >" & "< img src='D:\Chart2.jpg' >" & "" & ""
.htmlbody = .htmlbody & "< img src='D:\Chart3.jpg' >" & "< img src='D:\Chart4.jpg' >" & "< img src='D:\Chart5.jpg' >"

 

[Premanshu]

Hi Smiler

First of all Thank you Very much for your help. Your advice for clearing the browser cache worked. Now atleast i do not get the old images which do not exist at all in the file. I also now know from where they were actually coming, I never knew this and was wondering how VBA can pull image which does not exist at all.

However i have a new problem coming in now. When i run the macro instead of pulling the images into the mail body i get rectangle boxes with a red cross on top left saying "The Linked image cannot be displayed. The file may have been moved, renamed or deleted. Verify that the links point to the correct file location."

Please advice if you have any idea of how to handle this issue now.

Thanks again for your help and response, I really appreciate.

Regards,
Premanshu

 

[smiler]

do the files exist that you are using ?
your original code had the 'export' picture creation lines commented out, did you re-activate those rows ?
if you did and you step through your code and just after the images get created and before the call to createmail is fired- do the pictures exist in root of d: ?

 

[Premanshu]

yes I re-activated the export command lines before running the macro. before the CreateMail macro was triggered the image files were present in the d:\ drive for it's use but i still got those rectangle boxes with the red cross and error msg.