Not a normal "WS Email" case?

[joelle]

Good Friday Everyone,

I have the VBA below, and it works fine with Netscape users.
Its purpose is to email just "one" worksheet in my workbook (that has multiple sheets).
It works fine with Netscape users, but Outlook users get the "whole" workbook !!
Please how do I modify my codes so only "one" ws is email is attached to the popup window for both Netscape and Outlook users.

[VBA]ActiveSheet.Protect Password:="fun"
Dim x As Boolean
ActiveSheet.Copy
x = Application.Dialogs(xlDialogSendMail).Show
End Sub[/VBA]
Many thanks in advance,
Nee

[Justinlabenne]

You may want to check out Ron De Bruins web site for email from Excel examples, or visit the kb here for more:

http://vbaexpress.com/kb/getarticle.php?kb_id=350

http://vbaexpress.com/kb/getarticle.php?kb_id=97

or:

[VBA]Public Sub Send1Sheet()
With ActiveWorkbook
.Sheets("Sheet Name").Copy
.SendMail Recipients:="youraddy@what.com", _
Subject:="Sheet Mail"
.Close SaveChanges:=False
End With
End Sub[/VBA]

[joelle]

Hello,

Maybe I was not clear from my previous post:
What I would like to do is to click a macro button to "attach" (and not send email) an active worksheet to a new, pop-up email window. And my current codes below is doing that, but it just works fine for Netscape mail users.
When Outlook users get the attachment, they get the "whole" workbook.

The tip I beg for is: How to modify the current codes below so both Netscape and Outlook mail users receive my attachment as a single worksheet (extracted).

[VBA]ActiveSheet.Protect Password:="fun"
Dim x As Boolean
ActiveSheet.Copy
x = Application.Dialogs(xlDialogSendMail).Show
End Sub [/VBA]

And please, do not direct me to the rondebruin.com website. I did look and I did also get lost there. I could not find my case!

Thanks a lot.
Nee

[Justinlabenne]

I am using Outlook at home and at work, it seems to work fine for me here at home, I can test again for you when I get to work tonight, but It is only attaching one sheet that is copied out from the workbook with the code and sending it just fine for me. I cannot say right now, I will post back later.

[joelle]

Hello Justin,
Thanks so much for the follow-up post.
And here is the weird part:
* First, I post the workbook on the company intranet.
* If employees access our intranet via Netscape and they run my codes, they are able to attach just the "desired" sheet to a pop-up mail window (and that fulfills my purpose)
* But, if employees access our intranet via IE (Internet Explorer) and when they run the same macro, the "whole" workbook is attached to their pop-up email window.
Still a puzzle to me????

Thanks again for your offer to run the test - very appreciative.

Nee

[Zack Barresse]

If your worksheet uses commands such as custom UDF's or the INDIRECT function (as a couple examples) then copying the worksheet to another workbook (ActiveSheet.Copy) will give you errors on your worksheet. The easiest way to combat this (I think) is to copy the worksheet inside of the workbook it's in, make the values static, then move the worksheet to it's own workbook, save, send, delete.

[joelle]

Hello Zack,

How are you?
Thanks for the post. So far, I'm a bit desperate because readers still dont see that my purpose is to **attach a single ws** to a pop-up email window, no subject required, no recipient required, no close-window required.
To me, my case is simpler than most posted out there, is it?


Most threads are about sending a single ws.
My purpose is *not to email/send the ws then close the workbook.
And if someone like a challenge, I'd like the codes to work with both Netscape and Outlook mail users. Then, we can beer! but until then ... I'm still begging for help.

Nee

[Zack Barresse]

I do this myself. I'll try to get to this in the afternoon, gotta run right now though.

[joelle]

yay, run! but dont get lost on your way back here Zack!

so, coffee time for me....

Nee

[Zack Barresse]

Got most of it done today, my apologies for the hold up. If you don't hear from me by tomorrow, shoot me over a friendly little pm, would you?

[Zack Barresse]

Sorry about the delay. Here is a couple procedures you may use...

[vba]Sub sendStaticSheet()
If ActiveWorkbook Is Nothing Then Exit Sub
If MsgBox("Are you sure you want to send the ActiveWorkSheet?", _
vbYesNo, "Send ActiveWorkSheet") = vbNo Then Exit Sub
Application.ScreenUpdating = False
Dim fName As Workbook, Wb As Workbook, ws As Worksheet, p$
Dim OLApp As Object, OLMsg As Object
Set Wb = ActiveWorkbook
Set ws = Wb.ActiveSheet
ws.Copy after:=Wb.Sheets(Wb.Sheets.Count)
Call MakeStatic(Sheets(Sheets.Count).Name)
Sheets(Sheets.Count).Move
Set fName = ActiveWorkbook
fName.SaveAs "C:\" & Wb.Name & " sheet" & ws.Index
Set OLApp = CreateObject("Outlook.Application")
Set OLMsg = OLApp.CreateItem(0)
p = fName.FullName
With OLMsg
.Attachments.Add p
.Display
End With
fName.Close False
Kill p
Application.ScreenUpdating = True
Set OLApp = Nothing
Set OLMsg = Nothing
Set fName = Nothing
End Sub

Sub MakeStatic(wsName As String)
Dim wsStatic As Worksheet
On Error GoTo errHandler
Set wsStatic = Sheets(wsName)
wsStatic.Cells.Copy
wsStatic.Cells.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
errHandler:
End Sub[/vba]

Let me know if you'd like comments. This will attach a static version of the activeworksheet in the activeworkbook to an email. It will save it temporary as it's own file to the hard drive, then delete it.

Note that the procedure for making the sheet static will fail if A1 is merged.

[joelle]

Hello Zack,

Its for me to say "sorry" to have bugged you ...
So delighted to see your codes. I have full-day training today Friday but I cant wait to apply them.
Just would like to jot down quickly a big "Thanks" to you.

Will let you know how they go.

Best regards,

Nee