Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
e-mail selection from VBA
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.) -- Dan Cooley |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
e-mail selection from VBA
hi,
see this site http://www.rondebruin.nl/sendmail.htm regards FSt1 "Dan Cooley" wrote: 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.) -- Dan Cooley |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
e-mail selection from VBA
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 -- Best wishes, Jim "Dan Cooley" wrote: 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.) -- Dan Cooley |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
e-mail selection from VBA
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 = " .CC = "" .BCC = "" .Subject = "Daily Report" .Body = strbody .send ' Or if you wish .Send End With Set OutMail = Nothing Set OutApp = Nothing End Sub "Dan Cooley" wrote: 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.) -- Dan Cooley |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to send Excel selection using Live Mail | Excel Discussion (Misc queries) | |||
Copy Selection - Transpose Selection - Delete Selection | Excel Discussion (Misc queries) | |||
Copying Excel 2003 Selection into Outlook 2003 HTML E-Mail Message | Excel Discussion (Misc queries) | |||
Error: cannot load the mail service. Check your mail installation. | Excel Discussion (Misc queries) |