Email Excel Cell Range using Macro



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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question