vba email

[Rob Mann]

Please can someone help. Below is a macro I have recorded that sends emails out to people to advise them of some information on a sheet. What I need to do is attach a chart from a different worksheet to the email, the chart is named "chart 3". please help.

Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long

Sub SendEMail()
Dim Email As String, Subj As String
Dim Msg As String, URL As String
Dim r As Integer, x As Double
For r = 2 To 8 'data in rows 2-8
' Get the email address
Email = Cells(r, 2)

' Message subject
Subj = "bla bla bla"
' Compose the message
Msg = ""
Msg = Msg & "Dear " & Cells(r, 1) & "," & vbCrLf & vbCrLf
Msg = Msg & Cells(r, 9) & Cells(r, 3) & Cells(r, 10) & vbCrLf & vbCrLf

Msg = Msg & "Joe Bloggs" & vbCrLf
Msg = Msg & "Managing Director"

' Replace spaces with %20 (hex)
Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")

' Replace carriage returns with %0D%0A (hex)
Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A") ' Create the URL
URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg
' Execute the URL (start the email client)
ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus
' Wait two seconds before sending keystrokes
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "%s"

 

[Andrew Poulsom]

This may help you:

http://www.rondebruin.nl/mail/folder2/chart.htm

 

[Rob Mann]

thanks very much for your reply.
could you help me with adding your code into my code.
its taken me about 3 months to get a basic understanding of the code which I have written and adding your code into mine will confuse me alot.

 

[Andrew Poulsom]

You'll have to start again, because the code in the link I posted uses Outlook not Shell.