Sending chart in e-mail body using Excel VBA

[megaron]

Hi all,

I'm using Excel 2010, and the code below works but it's not optimal.

I'm trying to create a vba code that attaches a chart into the body of an e-mail to be viewed by e-mail (outside of network) and iphone.

Searching online, I found a way to export the chart into a Local/Network/Sharepoint drive, then HTML img source the file back into the e-mail. However, this process requires that the file remains on the disk or network drive. If the file is deleted, then no image will appear, and If it's on the network, it slowly loads, especially on sharepoint

Any suggestions? cutting and pasting would be ideal but I don't think that is supported....

Ranny

----------------------------
Code:

Sub Sendmail()

'Working in 2000-2010

Dim OutApp As Object
Dim OutMail As Object
Dim Fname As String

'Turn on Outlook for Excel
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

'Path name
Fname = "X:\roni_" & Format(Date, "dd-mm-yy") & ".jpg"
' X is mapped to sharepoint site C:\Users\rkhano\AppData\Roaming\Microsoft\Windows\Network Shortcuts\Sharepoint\roni_" & Format(Date, "dd-mm-yy") & ".jpg"

'Action on worksheet
ActiveWorkbook.Worksheets("Hourly").ChartObjects("Chart 1").Chart.Export Filename:=Fname, FilterName:="jpg"

With OutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "Test Chart"
.Attachments.Add Fname

'replace x with <
s = "xpxHourly US GMV Chartxpx"
s = s & "xpxximg src=file://" & Fname & "xx/px"
s = "xHTMLxxBODYx" & s & "xHTMLxxBODYx"

.HTMLBody = s
.Send 'or use .Display
End With

Set OutMail = Nothing
Set OutApp = Nothing

End Sub

 

[megaron]

Awesome! your solution worked. Thanks Domenic

The e-mail looks great, but on the iphone arggg....Any ideas on how to fix the iPhone problem

This is what it looks like on iPhone:

 

[Domenic]

Awesome! your solution worked. Thanks Domenic

You're very welcome!

The e-mail looks great, but on the iphone arggg....Any ideas on how to fix the iPhone problem

I don't have a clue. Hopefully some else will be able to help.

 

[megaron]

Hi Domenic,

I want to add a code to the VBA that checks if a cell has (DIVX or -100%), the exit macro, if not , then run.

How can I do that?

 

[Domenic]

Try...

With ActiveWorkbook.Worksheets("Hourly")
    If .Range("A1").Value = "DIVX" Or .Range("A1").Value = 1 Then Exit Sub
End With

If you don't want it to be case-sensitive, try replacing...

.Range("A1").Value = "DIVX"

with

UCASE(.Range("A1").Value) = "DIVX"

 

[megaron]

Thanks Dom, I got it to work using this code:

If Range("A1").Errors.Item(1).Value = True Then

With OutMail
'.SentOnBehalfOfName = "E-mail"
.To = "E-mail"
.Subject = "WARNING!!!!! Daily E-mail Failed"
.Display 'or use
End With

Exit Sub
End If

 

[mahmed1]

Try...

**Remove the spaces before and after "<" and ">"

[FONT=Courier New].Attachments.Add Fname
s = "< p >Hourly US GMV Chart< /p >" & vbNewLine
s = s & "< img src=""cid:roni_" & Format(Date, "dd-mm-yy") & ".jpg"" > " & vbNewLine
s = s & "< p >Bye...< /p >"
.HTMLBody = s[/FONT]

Note that the image file still needs to be attached to the email.

Hi domenic,

is there a way i can have a chart but not have the attachment?

i have something exactly the same that i want to do where i paste a chart in the body but i dont want that chart to be an attachment also

thank you