Send a range of cells in email

[stumpie]

I have Office 97 and Outlook 98. I am attempting to set up a macro that when initiated will email the active sheet in the body of the email. I don't want it sent as an attachment, I want it to show up as text in the email yet reference the .xls file in the subject.

Any help would be appreciated. I have been trying different tricks from this forum but nothing seems to execute with the result I'm looking for.

Thanks.

 

[Smitty]

Welcome to the Board, Stumpie!

Check out http://www.rondebruin.nl/sendmail.htm

It should answer all of your Outlook e-mail questions.

Hope that helps,

Smitty

 

[clares]

Hi Stumpie

This link will take you to a page that will do exactly what you want to acheive. It have tried it myself and use it.

http://www.danielklann.com/excel/sending_a_range_as_the_body.htm

Kindest Regards

Peter

 

[Ekim]

Stumpie,

Regrettably, the macro by Mr. Ron de Bruin for sending an active sheet in the body of the email will not work in Office 97. Mr. de Bruin notes this in his web site.

Hopefully, the macro from Daniel Klann will work for you.

Regards,

Mike

 

[dk]

Hi,

Only the SendRangeSimple will work in Excel 97 (see Method 2 on the referenced page). The procedure will only insert a very basic table into the body of the email. The code could be improved to make the table look more appealing but this would only benefit Excel 97 users, and unfortunately I don't think the time involved would be justified.

 

[nisht]

Okay give a try to this one also.

Sub mailactivesheet()
Dim struse As String
Dim maddress
Dim wbnew As Workbook
Dim strcurwb As String
Dim i As Integer

Application.ScreenUpdating = False

strcurwb = ActiveWorkbook.Name

i = Application.SheetsInNewWorkbook

'Only adding one worksheet in workbook
Application.SheetsInNewWorkbook = 1
Set wbnew = Workbooks.Add
Application.SheetsInNewWorkbook = i

'You can define your range here
'
Workbooks(strcurwb).Worksheets("reading").UsedRange.Copy
struse = Workbooks(strcurwb).Worksheets("reading").UsedRange.Address


'With wbnew
'' If you want to Define properties on new workbook.
' .Title = "Trial Workbook"
'End With
wbnew.Sheets(1).Name = "Robert"
wbnew.Sheets("Robert").Range(struse).PasteSpecial xlFormats
wbnew.Sheets("Robert").Range(struse).PasteSpecial xlPasteValues
'Workbooks(wbnew.Name).Sheets("Sheet1").Range(struse).PasteSpecial xlPasteValues
Application.CutCopyMode = False

'----------------------------
maddress = Array("nsd@eth.net", "nsd@pexcel.com", "pro@pexcel.com")
' if you want to send multiple emails
'For ia = LBound(maddress) To UBound(maddress)
'ActiveWorkbook.SendMail maddress(ia), ActiveSheet.Name & " " & Format(Now, "dd-mm-yy h-mm-ss") & " For our reference"
'Next ia

' Here all address will appear in to box
ActiveWorkbook.SendMail maddress, ActiveSheet.Name & " " & Format(Now, "dd-mm-yy h-mm-ss") & " For our reference"
'Application.Dialogs(xlDialogSendMail).Show
wbnew.Close False
Application.ScreenUpdating = True
End Sub