Avatar of Michael Dobbins
Michael Dobbins
 asked on

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

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

Dim CH As Chart
Set CH = Charts.Add
CH.Location xlLocationAsObject, "SHEET1"
Set CH = ActiveChart
RNG.CopyPicture xlScreen, xlBitmap
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 />"

End With
On Error GoTo 0

Set omail = Nothing
Next I
Set o = Nothing

End Sub
Microsoft OfficeOutlookVBAMicrosoft Excel

Avatar of undefined
Last Comment
Michael Dobbins

8/22/2022 - Mon

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).
Michael Dobbins

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.

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.
Your help has saved me hundreds of hours of internet surfing.
Michael Dobbins

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.

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Michael Dobbins

Thank you!