VBA To PAste Excel Data into Outlook Body

[rss01]

I'm stuck, I need to paste the data from an excel sheet into the body of an outlook email. I dont want to send it as an attachment, just paste it into the body.

I already have the code for everything except pasting the data, any ideas.

 

[Chattin]

This what you mean?

http://www.danielklann.com/Excel/sending_a_range_as_the_body.htm

HTH

 

[rss01]

Cant get the web link to work.

 

[Chattin]

This any better? Look at dk's post

http://www.mrexcel.com/board2/viewtopic.php?t=8251&highlight=outlook+range

 

[Chattin]

To save some time here's the code

Sub SendRange()

'Sends a specified range in an Outlook message and retains Excel formatting

'Written by Daniel Klann
'2002

'Dimension variables
Dim oOutlookApp As Object, oOutlookMessage As Object
Dim oFSObj As Object, oFSTextStream As Object
Dim rngeSend As Range, strHTMLBody As String, strTempFilePath As String

'Select the range to be sent
On Error Resume Next
Set rngeSend = Application.InputBox("Please select range you wish to send.", , , , , , , 8)
If rngeSend Is Nothing Then Exit Sub 'User pressed Cancel
On Error GoTo 0


'Get the temp folder path
Set oFSObj = CreateObject("Scripting.FilesystemObject&quot
strTempFilePath = oFSObj.GetSpecialFolder(2)
strTempFilePath = strTempFilePath & "\XLRange.htm"


'Now create the HTML file - NOTE! xlSourceRange and xlHtmlStatic have been replaced by their
'numeric values due to a potential error (unexplained) noted by Ivan F Moala 15/5/03
ActiveWorkbook.PublishObjects.Add(4, strTempFilePath, _
rngeSend.Parent.Name, rngeSend.Address, 0, "", "&quot.Publish True

'Create an instance of Outlook (or use existing instance if it already exists
Set oOutlookApp = CreateObject("Outlook.Application&quot

'Create a mail item
Set oOutlookMessage = oOutlookApp.CreateItem(0)

'Open the HTML file using the FilesystemObject into a TextStream object
Set oFSTextStream = oFSObj.OpenTextFile(strTempFilePath, 1)

'Now set the HTMLBody property of the message to the text contained in the TextStream object
strHTMLBody = oFSTextStream.ReadAll

'By default the range will be centred. This line left aligns it and you can
'comment it out if you want the range centred.
strHTMLBody = Replace(strHTMLBody, "align=center", "align=left", , , vbTextCompare)

oOutlookMessage.HTMLBody = strHTMLBody

oOutlookMessage.Display


End Sub

and dk's comments from the fisrt link I posted:

Sending a range as the body of an Outlook message

Using the code (below) you insert formatted worksheet data into an Outlook message item - you don't need to send the entire workbook and you can refer to items in your data without the recipient having to switch backwards and forwards between your message and your workbook. Also, if someone copies the data from the message and pastes it into Excel, all of the formulas will be retained!


HTH