How can I capture info in cells and generate a text field

[mithrandrir]

Hi, still being a "newbie" to Excel I need some help please. I have several formulae that calculate overtime payments for staff. I would like to be able to have a button, that when selected, captures all the data in the cells and strings them in a text format, ready to be pasted in an email. I know this must be easy but I am having difficulties. Any help woul dbe appreciated. I am using Excel 97 SR2.
Thanks

 

[TommyGun]

Re: How can I capture info in cells and generate a text fiel

Okay, I'm not sure if I follow exactly what you want, but see if this example helps.

A1 = TommyGun
B1 = $10,000,000.00
C1 = Test description
D1 = "Name: "&A1&" - Amount: "&Text(B1,"$###,###,##0.00")&" - Description: "&C1

The result in D1 would be the following string: "Name: TommyGun - Amount: $10,000,000.00 - Description: Test description"

NOTE: I assumed you pay very high overtime amounts as in column B.

 

[Marc]

Re: How can I capture info in cells and generate a text fiel

mithrandrir, How about one of the two. Regards, Marc

1)
Public Sub GrabData()
Range("A1:D32").Copy
End Sub

2) Set the Outlook reference in VBE and the following code will grab your data and stuff it into the body of an Outlook email before mailing.

Private Sub CommandButton1_Click()
Dim objOL As New Outlook.Application
Dim objMail As MailItem
Dim sRg As Range 'Your range to send
Dim sbody As String 'The actual string to send
Dim c As Range

Set sRg = Range("A1:H34")
For Each c In sRg
sbody = sbody & " " & c.Text
Next

Set objOL = New Outlook.Application
Set objMail = objOL.CreateItem(olMailItem)

With objMail
.To = "marc@letsdrinksomebeer.com"
.Subject = "Here is the Overtime Payment Data"
.Body = sbody
.Display
End With

objMail.Save 'USE .send if you want it mailed automatically

Set objMail = Nothing
Set objOL = Nothing
Set sRg = Nothing


End Sub

 

[mithrandrir]

Re: How can I capture info in cells and generate a text fiel

Wow!!!!!!

Thanks guys. :D