I'm trying to do something which surely isn't that difficult!

It will be part of a macro in Excel and what I want to do is copy a
range from the activesheet and then paste this into the body of an
Outlook e-mail.Here's the code:

Private OutApp As Object
Private OutMail As Object
Private NS As Object

Sub UpdateMail()

Application.screenupdating = False
Application.EnableEvents = False

'<<<<put info on the clipboard<<<<
ThisWorkbook.Sheets("Pivots").Range("B5:C6").Copy
'


Call OpenOutlook

Set OutMail = OutApp.CreateItem(0)

With OutMail
.To = "Tester"
.Subject = "Solve this one"

'<<<<take info from the clipboard<<<<
.body = ClipBoard.GetText
'

.Display
End With

Set OutMail = Nothing
Set OutApp = Nothing
Set NS = Nothing

Application.screenupdating = true
Application.EnableEvents = True

End Sub


Private Function OpenOutlook()
On Error Resume Next
Set OutApp = GetObject(, "Outlook.Application")
If Err.Number = 429 Then
Set OutApp = CreateObject("Outlook.Application")
Set NS = OutApp.GetNamespace("MAPI")
NS.Logon
End If
On Error GoTo 0
End Function


I'm happy that the copy method in Excel moves the info to the clipboard
but how do I extract it and paste it into the body of the mail.

Any help greatly appreciated
Jason

See http://www.rondebruin.nl/mail/folder2/mail4.htm

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"WhytheQ" wrote in message
ups.com...
I'm trying to do something which surely isn't that difficult!

It will be part of a macro in Excel and what I want to do is copy a
range from the activesheet and then paste this into the body of an
Outlook e-mail.Here's the code:

Private OutApp As Object
Private OutMail As Object
Private NS As Object

Sub UpdateMail()

Application.screenupdating = False
Application.EnableEvents = False

'<<<<put info on the clipboard<<<<
ThisWorkbook.Sheets("Pivots").Range("B5:C6").Copy
'


Call OpenOutlook

Set OutMail = OutApp.CreateItem(0)

With OutMail
.To = "Tester"
.Subject = "Solve this one"

'<<<<take info from the clipboard<<<<
.body = ClipBoard.GetText
'

.Display
End With

Set OutMail = Nothing
Set OutApp = Nothing
Set NS = Nothing

Application.screenupdating = true
Application.EnableEvents = True

End Sub


Private Function OpenOutlook()
On Error Resume Next
Set OutApp = GetObject(, "Outlook.Application")
If Err.Number = 429 Then
Set OutApp = CreateObject("Outlook.Application")
Set NS = OutApp.GetNamespace("MAPI")
NS.Logon
End If
On Error GoTo 0
End Function


I'm happy that the copy method in Excel moves the info to the clipboard
but how do I extract it and paste it into the body of the mail.

Any help greatly appreciated
Jason