chart to email

[KBL]

hello and thank you in advance for your help.

i'm trying to get a small number chart from a spreadsheet to the body of an
email (using .htmlbody=) by simply running a macro.

i've currently got the code working so far to save each chart as a jpg. the
problem i'm having is that when my charts save into the temp folder, when i
run the macro again, the charts do not update in the new email.

i'm using the following chunk of code for the charts to email macro:

--------------------------------------------------------------------------------------------

Worksheets("Charts").Activate

Fname1 = Environ$("temp") & "\Chart1.jpg"
ActiveWorkbook.Worksheets("Charts").ChartObjects("Chart 1").Chart.Export _
Filename:=Fname1, FilterName:="JPG"

Fname2 = Environ$("temp") & "\Chart2.JPG"
ActiveWorkbook.Worksheets("Charts").ChartObjects("Chart 2").Chart.Export _
Filename:=Fname2, FilterName:="JPG"

Fname3 = Environ$("temp") & "\Chart3.JPG"
ActiveWorkbook.Worksheets("Charts").ChartObjects("Chart 3").Chart.Export _
Filename:=Fname3, FilterName:="JPG"

'to allow enough time for the jpgs to process
Application.Wait (Now + TimeValue("00:00:03"))

s1 = "<p><img src=file://" & Fname1 & "></p><br>"
s2 = "<p><img src=file://" & Fname2 & "></p><br>"
s3 = "<p><img src=file://" & Fname3 & "></p><br>"

On Error Resume Next
With OutMail
.Recipients.Add sRecipient
.Subject = "Charts Report"
'.HTMLBody = "The following is the recent history of
files:<br><br>" _
& s1 & s2 & s3 & "<br><br><br><br>" Thanks!
Application.Wait (Now + TimeValue("00:00:03"))
.Display
End With

----------------------------------------------------------------------------------------------


the code seems right... the charts populate in my email just as they should,
but the data in them isnt correctly updating. the odd thing i've noticed...
is that when i restart my computer and run the code, they actually update
correctly for the first run right after restart... then from there,
regardless of how many times i run the macro, the charts remain the same.
i've tried changing up the number in the worksheet just to test the updating
of the charts, but still nothing.

any suggestions? :-\

 

[ker_01]

I'm not sure this will help or not, but I looked over some of my own code,
the code itself is different (I'm using CDO, etc) and I'm no expert so I
can't offer direct syntax suggestions. One key differences is that when
creating (and using) GIFs of cell ranges, I'm assigning them a new name for
each file (which can be deleted at the end of the macro). Another difference
is that I'm using a folder specifically for this project, not the temp folder
(I don't know why that would matter, but just figured I'd note it as a
difference).

It wasn't explicit in your posting, but I'm assuming you've checked between
each macro run and confirmed that the jpg itself is being updated, and that
the problem is that somehow the machine is caching an old version and
re-using it? If so, the first thing I'd test is what happens if you delete
the jpg when you are done with it, and if it is still being cached somewhere,
then you might have to figure out where and clear it.

Alternatively, is it possible that when the jpg file already exists, it is
not being overwritten by your code (and depending on whether or not you have
alerts on, maybe it isn't telling you)? That seems less likely since they do
update after a restart, unless your temp files are cleared out when you log
off/shut down.

HTH
Keith

 

[Ron de Bruin]

Hi KBL

See also
http://www.rondebruin.nl/mail/folder2/chart.htm

Or use this
http://www.rondebruin.nl/mail/folder3/mailenvelope.htm

 

[KBL]

Thanks so much for your input on this.

you are correct in saying that i've checked the actual temp jpgs between
macros... i've checked them before, during (gotta love break mode), and
after, and still the same result.

however, i did notice a difference... i changed the filenames to a specified
folder so they would no longer be in the temp folder using the
"Environ$("temp")" command.

now... i can access the jpgs just like before, but now the email is showing
boxes with x's in them (as if the file does not exist).

maybe i need to re-phrase how i've indicated where the file is? currently,
i'm using:
s1 = "<p><img src=file://" & Fname1 & "></p><br>" where the Fname variable
is directed to "C:\temp charts\tempchart1.jpg" and so on from there.

so... maybe my syntax is just off with my html? i believe it would read (if
the variable was written out..):
"<p><img src=file://"C:\temp charts\tempchart1.jpg"></p><br>"

is this correct?

i'm obviously still missing something... and knowing how things go... its
probably really simple.

thanks again!