Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / January 2006

Tip: Looking for answers? Try searching our database.

Macro works in one workbook but not in another

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
sungen99 - 26 Jan 2006 14:44 GMT
This program will take a sheet and send it as an embedded email.  It
works perfectly in a workbook with worksheets (sheet1)(sheet2)(sheet3)

For some reason when I enter this code into a workbook I actually need
to use it for whos worksheet name is (Recap) it sends the email just
fine BUT the contents of the mail are blank.  In other words it does
not show the worksheet.

Any ideas?

Sub Mail_ActiveSheet_Body()
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = "ken@homel.com"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.HTMLBody = RangetoHTML2
.Send   'or use .Display
End With
Application.ScreenUpdating = True
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Signature

sungen99

sungen99 - 26 Jan 2006 15:28 GMT
Shellfish bump.

Sorry I have been working on fixing it. and I’ll be…… it just wont work
right.

Signature

sungen99

Ron de Bruin - 26 Jan 2006 15:53 GMT
If you want to send the whole sheet use this
http://www.rondebruin.nl/mail/folder3/mail2.htm

The example you use is for sending the selection
This is the page with the code
http://www.rondebruin.nl/mail/folder3/mail4.htm

Signature

Regards Ron de Bruin
http://www.rondebruin.nl

> This program will take a sheet and send it as an embedded email.  It
> works perfectly in a workbook with worksheets (sheet1)(sheet2)(sheet3)
[quoted text clipped - 24 lines]
> Set OutApp = Nothing
> End Sub
sungen99 - 26 Jan 2006 17:26 GMT
Ron when i use that code i get a compile error.

Sub or Function not defined.

the code its pointing too is:

.HTMLBody = SheetToHTML(ActiveSheet)

it does not like the "SheetToHTML(ActiveSheet)" part.

am i missing a refenrence perhaps?

Signature

sungen99

Ron de Bruin - 26 Jan 2006 17:36 GMT
You miss the function that is on the webpage
Copy it also in the module

Like this

Sub Mail_ActiveSheet_Body()
   Dim OutApp As Outlook.Application
   Dim OutMail As Outlook.MailItem
   Application.ScreenUpdating = False
   Set OutApp = CreateObject("Outlook.Application")
   Set OutMail = OutApp.CreateItem(olMailItem)
   With OutMail
       .To = "ron@debruin.nl"
       .CC = ""
       .BCC = ""
       .Subject = "This is the Subject line"
       .HTMLBody = SheetToHTML(ActiveSheet)
       .Send   'or use .Display
   End With
   Application.ScreenUpdating = True
   Set OutMail = Nothing
   Set OutApp = Nothing
End Sub

Public Function SheetToHTML(sh As Worksheet)
'Function from Dick Kusleika his site
'http://www.dicks-clicks.com/excel/sheettohtml.htm
'Changed by Ron de Bruin 04-Nov-2003
   Dim TempFile As String
   Dim Nwb As Workbook
   Dim myshape As Shape
   Dim fso As Object
   Dim ts As Object
   sh.Copy
   Set Nwb = ActiveWorkbook
   For Each myshape In Nwb.Sheets(1).Shapes
       myshape.Delete
   Next
   TempFile = Environ$("temp") & "/" & _
   Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
   Nwb.SaveAs TempFile, xlHtml
   Nwb.Close False
   Set fso = CreateObject("Scripting.FileSystemObject")
   Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
   SheetToHTML = ts.ReadAll
   ts.Close
   Set ts = Nothing
   Set fso = Nothing
   Set Nwb = Nothing
   Kill TempFile
End Function

Signature

Regards Ron de Bruin
http://www.rondebruin.nl

> Ron when i use that code i get a compile error.
>
[quoted text clipped - 7 lines]
>
> am i missing a refenrence perhaps?
sungen99 - 26 Jan 2006 18:19 GMT
AHHHHHHHHHH  THANK YOU!!!!!!

i understand.  been working on this for 4 hours now.. hehe.
Ron de Bruin - 26 Jan 2006 18:25 GMT
Hi sungen99

Instead of
.HTMLBody = SheetToHTML(ActiveSheet)

You can also use this

.HTMLBody = SheetToHTML(Sheets("YourSheetName"))

Signature

Regards Ron de Bruin
http://www.rondebruin.nl

> AHHHHHHHHHH  THANK YOU!!!!!!
>
> i understand.  been working on this for 4 hours now.. hehe..
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.