troubleshooting Question

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

Avatar of Michael Dobbins
Michael Dobbins asked on
Microsoft OfficeOutlookVBAMicrosoft Excel
6 Comments1 Solution66 ViewsLast Modified:
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
Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros