Copy chart to email and send to list based on cell values

Michael Dobbins
Michael Dobbins used Ask the Experts™
on
Daily-Call-Metrics---By-Campaign_Ne.xlsmI am trying to email a pivot table to a list of email addresses on an excel sheet. I have tried multiple ways to accomplish this but no success. Currently I can create the pivot table as a chart on a new sheet and open a new email. However, the list of email addresses will not appear in the "TO" field and the chart will not copy to the email. I am open to new ways of accomplishing this. Creating a new worksheet with the pivot table converted to a chart was just the only way I could figure out to do it. Ideally I would not have to create a new worksheet that I have to close when done. I will attached the worksheet. Here is what I have so far:

Sub FLSnapShot()

Dim RNG As Range
Set RNG = Range("A1:U26")
Dim sdate As String
Dim FileName As String
Dim SavePath As String
Dim SaveAs As String

sdate = Format(Sheets("Florida").Range("W1"), "MM-DD-YYYY")
FileName = "Daily Call Stats "
SaveAs = FileName & sdate & ".png"
SavePath = "N:\WFM\Snapshots\"


'''build the chart

Workbooks.Add
Dim CH As Chart
Set CH = Charts.Add
CH.Location xlLocationAsObject, "SHEET1"
Set CH = ActiveChart
RNG.CopyPicture xlScreen, xlBitmap
CH.Paste
CH.Export (SavePath & SaveAs)
CH.Parent.Name = "DS"
ActiveSheet.ChartObjects("DS").Height = RNG.Height
ActiveSheet.ChartObjects("DS").Width = RNG.Width


''''sending in outlook

Dim o As Outlook.Application
Set o = New Outlook.Application
Dim wks As Worksheet
Set wks = Worksheets("Sheet1")

On Error Resume Next

For I = 2 To wks.Range("W" & Rows.Count).End(xlUp).Row
Dim omail As Outlook.MailItem
Set omail = o.CreateItem(olMailItem)

With omail
.To = wks.Range("W" & I).Value
.Subject = "Daily Call Stats"
.HTMLBody = "Hello," & "<br>" & "<br>" & "Here is the daily dashboard:" & "<br>" & "<br>" & "<img src = SavePath & SaveAs />"
.Display



End With
On Error GoTo 0

Set omail = Nothing
Next I
Set o = Nothing




End Sub
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
I'm not getting the chart to appear in the body of your email. Where did you get the code for that?

You weren't getting any addresses because the addresses weren't copied to the worksheet with the chart. I tweaked the code to avoid that problem.

I also put the code in the workbook rather than Personal.xlsb (and redirected the action item for the button).
Daily-Call-Metrics---By-Campaign_Ne.xlsm

Author

Commented:
Thanks, Byundt! This helped with adding the recipients name to the emails. I am also not able to get the chart to appear in the email. The code for this was put together by looking at examples online and tweaking it to what I need. My end goal is to copy the chart into an email and send to a list of recipients on the worksheet. It possible I would like to also close the new worksheet that is opened to create the chart after it is done copying it to an email.

I changed the range for the recipient list due to getting blank emails from hidden rows that were empty. I also added a row counter for the range of the chart to account for adding/subtracting rows. I attached the updated file.
Updated-Example-12.1.xlsm
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
The code for this was put together by looking at examples online and tweaking it to what I need.
And when the tweaks aren't working, the first place to look is for the source of the information. That's why I asked for it, as I couldn't tell by inspection where the problem lay.

If you are open to starting from scratch, Microsoft Excel MVP Ron de Bruin has an excellent set of webpages discussing how to email stuff from Excel. Although you call it a chart, it is actually a range of cells that you are sending.
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

Author

Commented:
I was able to get the excel sheet to paste into the body of the email. However, what previously worked to enter the email addresses it will be sent to, is not working here. I am needing all email addresses in column W to be added to the "To" field on the email. I would also like to be able to autofit the table that is being pasted into the email. I have tried a few different ways but keep getting errors. Lastly on both subs "UpdatedateFL" and "newds", I am trying to add a message box if there is an error (or the date being requested to update is not found) and then have it exit the sub without making any changes. Currently on the "UpdatedateFL" sub, if there is no data to match the date being entered, it defaults to "All" fields.

Any suggestions will be greatly appreciated. New file attached.
Updated-Example-12.1.xlsm
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
I worked on your newds sub.

Your email recipients lists starts in cell W7, but the code had it started in cell W5. You were also setting the mail object to nothing inside the loop so you could only use it one time. You were missing an Exit Sub statement if the run completed successfully.

I fixed these problems and also added a statement to send the email and to close it. You may want to comment one or both of these out.
Updated-Example-12.1.xlsm

Author

Commented:
Thank you!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial