Copy and Paste from Excel into Outlook Email message

[TroyB]

Hi!

I've got the following example spreadsheet and below code that works ok.



Spreadsheet


A
B
C
D

1
10 Nov 2004
Name
Email
PayAmount

2

Joe Bloggs
(e-mail address removed)
$254,365.23

3

Bart Simpson
(e-mail address removed)
$325.35

4

Wilma Rubble
(e-mail address removed)
$2,253.35




VBA Code

'Using Outlook2003 and Excel2003

'For this code to work, you need to Select Tools--> References -->

' Tick box - Microsoft Outlook 11.0 Object Library



Sub Send_EmailMsg()

Dim objOL As New Outlook.Application

Dim objMail As MailItem

Dim NoOfStaff As Integer, a As Integer, rwIndex As Integer, colIndex As
Integer



NoOfStaff = Range("Email").Rows.Count

rwIndex = 2

colIndex = 2



For a = 1 To NoOfStaff



Set objOL = New Outlook.Application

Set objMail = objOL.CreateItem(olMailItem)



PayPerson = Cells(rwIndex, colIndex).Value

PayAmount = Format(Cells(rwIndex, colIndex + 2).Value, "$ #,###.#0")

PayEmail = Cells(rwIndex, colIndex + 1).Value



With objMail

.To = PayEmail

.Subject = PayPerson & " - PaySlip for Paydate " &
Format(Range("A1").Value, "dd mmm yyyy")

.Body = "Please find below payslip information. A value of " & _

PayAmount & " has been deposited into your selected account."



.Display



End With



rwIndex = rwIndex + 1



Set objMail = Nothing

Set objOL = Nothing



Next a



End Sub





What i would prefer, if it could send each person in the Range("Email") an
email that;

1. Copies and pastes cells (say B2:L15) from Sheet2 into the email body
of the first email recipient

2. Then copies and pastes cells (say B17:L30) from Sheet2 into the
email body of the second email recipient

3. etc etc



By "copying" from Excel into the email, all the required formatting, values
etc accompanies the email message, which is what i require.



Alternatively, is it possible to load a specific form in Outlook and the
data from Excel is imported in the fields required?



Thank you in advance

Boeky