Solved: 50% Excel, 50%Outlook, 100% fail

[Gingertrees]

I would like to embed a small excel worksheet into the body of an email in outlook, in such a way that it will be FUNCTIONAL when the recipient hits reply. Currently, the worksheet is converted to a picture whenever it's mailed. I've attached a picture of what I'd like the worksheet to look like. The email would go something like this:

To: my employees
From: me@me.com
Subject: reply to this!
=================
Please hit REPLY and fill in the table below with your clients' information:
[Excel worksheet goes here, and works as a worksheet when the worker replies]

Sincerely,
Me.
=================

Ideas, anyone?

[Benzadeus]

Try Ron de Bruin's site: http://www.rondebruin.nl/mail/folder1/mail2.htm

[mdmackillop]

Consider the case that a recipient does not have Excel. How could your email could contain Excel functionality?

BTW, Please do not crosspost without advising us.

[Gingertrees]

Sorry about the cross-post - I was beginning to think no one looked at the Outlook board...

This is a monthly email just for my employees, all of whom have the same version (2003) of Office Suite on their computers. I think the calculations are too complex for the simple tables I know how to create in Outlook messages, so I thought Excel could help.

[rbrhodes]

Is there a reason for it not to be an attachment?

[Gingertrees]

dr: I might have to do that, but it would provide easier browsing at later dates (like when I have to compile yearly data) if all the info is in the email itself.

I don't understand what Ron de Bruin's site is trying to accomplish. The code does nothing (replacing email addresses, of course) that I can see. I'm still puzzled.

[Benzadeus]

Well, the title of the link is "Mail one Sheet with SendMail". I believe that it's what you are looking for.

[Gingertrees]

Close, but not quite. I do not want the worksheet to be an attachment, I want it embedded in the body of the email. This way people can reply, fill in their info, and email back to me, so I can just scroll through my emails comparing numbers, without having to open up individual excel files.

In my mind this seems like it should be easy, but it is turning out to be much more elusive that I imagined...

[Benzadeus]

http://www.rondebruin.nl/mail/folder3/mail2.htm

[Gingertrees]

Whew! Now we're getting somewhere. Users can now type in the worksheet when they reply, but the DataVal and SUM functions no longer work. That was kind of the purpose of this ill-begotten pairing to begin with. Ideas?

Here's Ron d.b.'s code (Thanks Benzedeus!):
[vba]
Sub Mail_Sheet_Outlook_Body()
' in sheet 1 module
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2007
Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
With Application
.EnableEvents = False
.ScreenUpdating = False
End With

Set rng = Nothing
Set rng = ActiveSheet.UsedRange
'You can also use a sheet name
'Set rng = Sheets("YourSheet").UsedRange
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = "me@someemail.com"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.HTMLBody = RangetoHTML(rng)
.Send 'or use .Display
End With
On Error GoTo 0

With Application
.EnableEvents = True
.ScreenUpdating = True
End With

Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Function RangetoHTML(rng As Range)
' Changed by Ron de Bruin 28-Oct-2006
' Working in Office 2000-2007
Dim fso As Object
Dim ts As Object
Dim TempFile As String
Dim TempWB As Workbook

TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

'Copy the range and create a new workbook to past the data in
rng.Copy
Set TempWB = Workbooks.Add(1)
With TempWB.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Cells(1).PasteSpecial xlPasteFormats, , False, False
.Cells(1).Select
Application.CutCopyMode = False
On Error Resume Next
.DrawingObjects.Visible = True
.DrawingObjects.Delete
On Error GoTo 0
End With

'Publish the sheet to a htm file
With TempWB.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=TempWB.Sheets(1).Name, _
Source:=TempWB.Sheets(1).UsedRange.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With

'Read all data from the htm file into RangetoHTML
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
RangetoHTML = Replace(RangetoHTML, "align=center xublishsource=", _
"align=left xublishsource=")

'Close TempWB
TempWB.Close savechanges:=False

'Delete the htm file we used in this function
Kill TempFile

Set ts = Nothing
Set fso = Nothing
Set TempWB = Nothing
End Function
[/vba]

[Benzadeus]

Well, you want to use some Excel functions in the body of the e-mail... I have no idea how to do it.

[rbrhodes]

Hi,

If its on a network would a hyperlink to the file itself work? No attachments, just click on the hyperlink and it's there.

[Gingertrees]

Is this impossible? ;-( I thought VBA could do anything...

[Benzadeus]

I bet that it is not impossible.

I would say more... I think that it has a simple solution.

[rbrhodes]

I'll take another look at it, time permitting

[mdmackillop]

Interactive email? I won't hold my breath!!!!

[Gingertrees]

OK, I wondered if this was possible, and consensus from lots of folks smarter and more experienced than I is that it is not. Oh well. Thanks anyway.

[Marcster]

Have you tried to imbed an Excel sheet object in the mail?.
Insert > Object > Excel Sheet
You can then use formulas etc in the 'Excel file' by double clicking on it.