e-mail selection from VBA

[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.)

 

[Guest]

hi,
see this site
http://www.rondebruin.nl/sendmail.htm

regards
FSt1

 

[Guest]

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

 

[Guest]

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