Annoying as H#LL !!!!

[WhytheQ]

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

 

[Gman]

I've just been playing with something similar but simpler: I wanted to
have a button to insert plain text (rather than formatted text) when
editing in HTML.

I achieved it using sendkeys for the paste action (yuck!!). However, the
getting the string from the clipboard should work for you nonetheless.
See below code.
HTH
Gman

Sub PasteAsPlainText()
'need to clean out clipboard - so get text and then put it back
fcnLoadStringIntoClipboard (fcnGetStringFromClipboard)
SendKeys "^v"
End Sub
Function fcnLoadStringIntoClipboard(myString As String)

Dim DataObj As MSForms.DataObject

Set DataObj = New MSForms.DataObject
On Error Resume Next 'In case empty
DataObj.SetText Text:=myString
DataObj.PutInClipboard
On Error GoTo 0
Set DataObj = Nothing

End Function
Function fcnGetStringFromClipboard() As String

Dim DataObj As MSForms.DataObject

Set DataObj = New MSForms.DataObject
On Error Resume Next 'In case empty
DataObj.GetFromClipboard
fcnGetStringFromClipboard = DataObj.GetText(1)
On Error GoTo 0
Set DataObj = Nothing
End Function