Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
chart to email
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? :-\ -- Oh, the creative possibilities! ^_^ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
chart to email
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 "KBL" wrote: 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? :-\ -- Oh, the creative possibilities! ^_^ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
chart to email
Hi KBL See also http://www.rondebruin.nl/mail/folder2/chart.htm Or use this http://www.rondebruin.nl/mail/folder3/mailenvelope.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KBL" wrote in message ... 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? :-\ -- Oh, the creative possibilities! ^_^ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
chart to email
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! -- Oh, the creative possibilities! ^_^ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Macro how to create email link for the email addresses in aRange or Selection | Excel Worksheet Functions | |||
Excel VBA macro to send email attachment from default email client | Excel Programming | |||
email out Pivot Chart with one record only | Excel Programming | |||
email chart | Excel Programming | |||
Email a chart from excel via Lotus Notes | Excel Programming |