VB Code to Copy a table and place it in an email.

[jamesg]

Hi All,

Am working in Excel 07, but this would need to work in 2000 as well.

Can anyone help with the following. Need a macro that will...
1. Select a range of cells from B4 to RX. X is defined as the last row where Column A has a value.
2. Copy the visible cells
3. Open an email, enter "Submission" into the title, enter "Dear X," insert 2 returns.
4.Paste the copied table into the body the email.

Many Thanks
JamesG

[jenroyce]

Maybe this can help you get started. works for Outlook

Create a command button under Control

[VBA]Sub Send_Range()
' Select the range of cells on the active worksheet.
ActiveSheet.Range("A1:Q30").Select

' Show the envelope on the ActiveWorkbook.
ActiveWorkbook.EnvelopeVisible = True

' Set the optional introduction field thats adds
' some header text to the email body. It also sets
' the To and Subject lines. Finally the message
' is sent.
With ActiveSheet.MailEnvelope
.Introduction = "Subject Title"
.Item.To = "E-Mail_Address_Here"
.Item.Subject = "Email Description"
.Item.Send
End With
End Sub[/VBA]

where: .Range("Range of cell you want to see in your email").

[mancubus]

check this.

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

[jamesg]

Hi,

Working with Excel 2007 in Vista

It is points 1 and 2 that need the most help with.

1. Select a range of cells from B4 to RX. X is defined as the last row where Column A has a value.
2. Copy the visible cells

Thanks

[shrivallabha]

To find the last data filled row (X in your case) would be:
[VBA]Dim X as long
X = Range("A" & Rows.count).End(xlUp).Row
Range("B4:R" & X).Select[/VBA]