Solved: All-in-One: specialcopy/attach/close

[joelle]

Hello Experts:

I have the following ambition but not skilled enough to carry it out:

I have a test workbook w/ 3 sheets (in real life, there are 15). The challenge is, when the macro on an active sheet is clicked, how to make it obey to the followings:

1. Copy the whole active page to the clipboard
2. Open a new spreadsheet
3. Do a Paste-Special of the info to "Sheet1" of the new spreadsheet that includes :
3a. column width, format, row height
3b. margin (from source info) and any header and footer info (huge challenge to me)
4. Attach this "Sheet1" to an email window (no recipient name or subject needed.)
5. Close the new ws (after Sheet1 is attached to an email window)
6. Leave the source workbook open so sales person can go on with another attachment.

So far, I have a code to attach the active sheet to an email window and still leaves the source wb open. But this code only works for Netscape mail. To other users, it attaches the "whole" workbook and not one single selected ws.
So I figure the trick above (though more steps) should do it for any email client (netscape, outlook, eudora ....).

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

Pls see my test spreadsheet attached.
Many many THANKS.

Nee

[XLGibbs]

This will do all of that. First it will copy the activesheet when the code is fired to a new book. Then copy/pastespecial values only.
Then it will allow the user to save the sheet, before bringing up the send mail dialog with the new sheet/file as an attachment. I think.

Hope it helps.

[VBA]
Sub MailSheet()
'On Error GoTo Terminator
Dim shtName As String
Application.EnableEvents = False


shtName = ActiveSheet.Name
ActiveSheet.Copy 'creates a new workbook from the active sheet, which becomes active workbook.activesheet

With ActiveWorkbook
.Sheets(1).Cells.Copy
.Sheets(1).Cells.PasteSpecial (xlValues) 'make all the cells value only
End With


ActiveWorkbook.SaveAs _
Filename:=Application.GetSaveAsFilename("Copy of " & shtName, "Microsoft Excel File, *.xls")
'above saves a copy of the sheet with the sheet name

Application.DisplayAlerts = False
Application.Dialogs(xlDialogSendMail).Show

With ActiveWorkbook
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Terminator:
Application.DisplayAlerts = True
Application.EnableEvents = True

End Sub
[/VBA]

[joelle]

Hello XLGibbs,

Awesome! Nice codes and they do all of those!
(to me its quite un-achievable)

For some frosting (on the cake ), is there a way to just save the extracted ws with the sheetname without asking for user to agree or confirm?
Then, life is more than beautiful.

You're fast and I'm so much appreciative.

Nee

[XLGibbs]

[VBA] Sub MailSheet()
'On Error GoTo Terminator
Dim shtName As String
Application.EnableEvents = False


shtName = ActiveSheet.Name
ActiveSheet.Copy 'creates a new workbook from the active sheet, which becomes active workbook.activesheet

With ActiveWorkbook
.Sheets(1).Cells.Copy
.Sheets(1).Cells.PasteSpecial (xlValues) 'make all the cells value only
End With


Application.DisplayAlerts = False
ActiveWorkbook.SaveAs "Copy of " & shtName & ".xls")
'above saves a copy of the sheet with the sheet name


Application.Dialogs(xlDialogSendMail).Show

With ActiveWorkbook
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Terminator:
Application.DisplayAlerts = True
Application.EnableEvents = True

End Sub
[/VBA]
That should do it. Just uncomment the On error line if it works okay..

[joelle]

Oops - timing error with my post.

Nee

[XLGibbs]

Okay, if it is all good to go, if you could just pop back and mark the thread solved for us..Happy to help out.

[joelle]

Thanks again.
I plugged the modified codes in and this line below is immediately
hilighted in red.

ActiveWorkbook.SaveAs "Copy of " & shtName & ".xls")

When run, it says:
Compile error
Syntax error

What did I miss?

Thank you for your patience.

Nee

[XLGibbs]

sorry, should be:

[VBA]
ActiveWorkbook.SaveAs "Copy of " & shtname
[/VBA]

The & ".xls" part is not necessary...

[joelle]

XLGibbs,

It runs sooo smooth now -- Thanks a million. I'm so happy.
Now, I'm gonna deal with the Outlook mail (fingers crossed!)

IOU,
Nee

[XLGibbs]

It should work, I have outlook and it did.

[joelle]

I trust you 101%.
Will be rite back (again, you're quite fast -- I cant beat that).

Nee

[XLGibbs]

I have like 200 moduleswith stuff like that. I just copy/paste quick edit if I have one appropriate..

[joelle]

Ok, I'm back. Not that I tried to test your code (like said, it works beautifully).

But what I tried to test the code from Netscape browser, then test it with IE browser (I made a mistake when I mentioned Outlook). I do not trust IE; it is so flaky.

Ok, so I posted the test wb to my company internal website; opened it using Netscape and found that everything works great.

Then came what I feared -- IE browser. I opened the wb from the internal website using IE. My fear then is confirmed: I ran the macro and I got an error msg saying:
Run-Time Error "1004"
So, is this something with browser incompatibility? Since the codes work so well when I opened the wb using Netscape?
(pulling hair).

Nee
Method 'DisplayAlerts' of object '_Application' failed.

[XLGibbs]

Internet Explorer is by far the worst browser interface.

Do you know which line it errored out in? Or simply opening the file produced that..

[joelle]

Oh, my bad, I should have mentioned it.

The highlight was on:
Application.DisplayAlerts = False

Thanks,
Nee

[XLGibbs]

well, you can try it by commenting out those lines...but that is what will disable the warning prompt on the saveas...

[Bob Phillips]

Oh, my bad, I should have mentioned it.

The highlight was on:
Application.DisplayAlerts = False

Thanks,
Nee

This is guessing, but I have often found that running an Excel app in IE tends to lose its context, so maybe, just maybe, it will work with

Excel.Application.DisplayAlerts = False

[joelle]

XLGibbs,

Thank you for your patience this far. Actually, I can live off the Netscape version of the wb since Netscape is the default browser my company uses.

Again, thanks for your generosity for posting your codes and tailored it for me.
I'm very thankful for that.

Best regards,
Nee

[XLGibbs]

You are very welcome! Happy to help. Just pop back and mark the thread solved if you can.

Whoops. You already did..Thanks Nee!

[joelle]

Hello,

Yes, I'm back ... (hate to give up to IE browser).
And I leave this thread "solved" because it is in fact solved by XLGibbs and his code works perfectly with Netscape browser.

My ambition is to get IE browser run as well. So please how to arrange the simple code below to do these tricks:

1. Save the open wb as a temp file but leave the source wb open.
2. Name the temp file "workbook1."
3. Leave only the active sheet from the temp workbook1 and kill all the other sheets without confirmation from users.
4. Attach this single sheet left to an email window (no code needed for recipient name or subject (in this case, the subject would be the temp wb name).

Is this doable? Pls see donated code attached. And of course I'd have the brightest weekend if I can achieve the above.
Nee

[VBA]Sub attach_it()
Application.Dialogs(xlDialogSendMail).Show
End Sub[/VBA]