Zipping the current Excel Spreadsheet with PkZip

[dantee]

Hi Ron.

No, it is not. I'm trying something simpler... just emailing the contents
on a spreadsheet worksheet, and your code makes the email the but is failing
to put the contents of the sheet on the email body...

..HTMLBody = RangetoHTML(rng)

doesn't seem to be working (I did copy the RangetoHTML function into the
same module as the macro). Perhaps the problem may be my settings in
Outlook? Any help would be great. Here is your code, which I am using:

Sub Mail_Sheet_Outlook_Body()
' 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 = "(e-mail address removed)"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.HTMLBody = RangetoHTML(rng)
.Display 'or use .Send
End With
On Error GoTo 0

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

Set OutMail = Nothing
Set OutApp = Nothing
End Sub




Sincerely,
Dante

 

[Ron de Bruin]

Download the workbook (body) example from my mail page
Let me know if this is working or not
http://www.rondebruin.nl/sendmail.htm

 

[dantee]

Hi again.

I downloaded the Outlook Object Model (Body) example workbook.

Got stuck/Debug at

Set OutMail = OutApp.CreateItem(0)

error message was new to me...

"Run-time error '-2147287035 (80030005)':
You don't have appropriate permission to perform this operation."

Dante

 

[dantee]

Ron...

One of the programmers here helped me... told me a little trick that makes a
big difference. He showed me that I actually needed to extract the Excel
file from the zip file to allows the code to work properly. Just opening the
Excel file was not yielding the proper results. Now it is.

I'm going to go ahead and modify it for my purposes and see if I don't get
stuck somewhere again. Thanks for posting all this great time-saving code!

Dante

 

[Ron de Bruin]

Ahaa, you are not the only one that make that mistake <g>

Good luck and if you need help post back