Link to home
Start Free TrialLog in
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

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
Avatar of byundt
byundt
Flag of United States of America image

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
Avatar of Michael Dobbins
Michael Dobbins

ASKER

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
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.
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
ASKER CERTIFIED SOLUTION
Avatar of byundt
byundt
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you!