PDA

View Full Version : [SOLVED] VB Code to Copy a table and place it in an email.



jamesg
04-11-2011, 08:45 PM
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
04-11-2011, 09:21 PM
Maybe this can help you get started. works for Outlook

Create a command button under Control

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

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

mancubus
04-11-2011, 11:01 PM
check this.

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

jamesg
04-11-2011, 11:19 PM
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
04-12-2011, 12:55 AM
To find the last data filled row (X in your case) would be:
Dim X as long
X = Range("A" & Rows.count).End(xlUp).Row
Range("B4:R" & X).Select