e-mail selection from VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have multiple sites that report overnight production numbers to a
distribution list. I would like to automate the process. Rather than send
an entire workbook for a few cells worth of data, I'd like to just send the
selected cells so as the body of the message so they can be easily red from a
hand held device. From File > Send To > Mail recipient I get an option to
either Send the entire workbook as an attachment, or Send the entire sheet as
the message body. If a range is selected and I choose the second option I
get exactly what I want. How do I do this from code? (I notice the Send
Button will say "Send this sheet" if no cells are selected, or "Send this
selection" if there are cells selected.)
 
Dim OutMail As Outlook.MailItem
Dim strbody As String
Sheets("Data").Activate
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
strbody = "Here is the report." & vbnewline & Range("A1:C12")


IncludeAttachments = false
With OutMail
.To =
.CC =
.BCC = ""
.Subject = "Daily Report"
.Body = strbody
.Display ' Or if you wish .Send

End With

Set OutMail = Nothing
Set OutApp = Nothing
 
hi Dan,

I have revised what Jim posted as a String will not accept a Range object
larger than 1 cell. This is a quick and easy way, but not the prettiest. It
essentially, just creates a tab-delimited array in the message body (you need
to add a reference to the Outlook Object library):

Sub mailSend()
Dim OutMail As Outlook.MailItem
Dim strbody As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)

Dim arrData As Variant
arrData = Sheet1.Range("YourRangeHere")

For ctr1 = LBound(arrData, 1) To UBound(arrData, 1)
For ctr2 = LBound(arrData, 2) To UBound(arrData, 2)
strbody = strbody & arrData(ctr1, ctr2) & vbTab
Next ctr2
strbody = strbody + vbNewLine
Next ctr1

IncludeAttachments = False
With OutMail
.To = "(e-mail address removed)"
.CC = ""
.BCC = ""
.Subject = "Daily Report"
.Body = strbody
.send ' Or if you wish .Send

End With

Set OutMail = Nothing
Set OutApp = Nothing

End Sub
 
Back
Top