Email Excel Cell Range using Macro

  • Thread starter Thread starter angelapercival
  • Start date Start date
A

angelapercival

Hi All,

I have worked out how to send as an excel attachment using a macro. But what I am now looking to do is sending the data in the excel spread sheet pasted in the email. Here is what I have: (The error is with the body - copying the ranges)

Hope you can helps. Cheers.

Sub CreateEmail()

'write the default Outlook contact name list to the active worksheet

Dim OlApp As Object
Dim OlMail As Object
Dim ToRecipient As Variant
Dim CcRecipient As Variant

Set OlApp = CreateObject("Outlook.Application")
Set OlMail = OlApp.CreateItem(olmailitem)

For Each ToRecipient In Array("(e-mail address removed)")
OlMail.Recipients.Add (ToRecipient)
Next ToRecipient

'fill in Subject field- in this case whatever value is in cell A1
OlMail.Subject = Range("B20").Value

'fill in Body field -
OlMail.Body = "Hi," & vbNewLine & vbNewLine & "Please find attached a completed " & Range("B20:C31").Value


'Display the message
OlMail.Display 'change this to OlMail.Send if you just want to send it without previewing it.

End Sub
 
Back
Top