PDA

View Full Version : Solved: All-in-One: specialcopy/attach/close



joelle
02-21-2006, 12:16 PM
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 ....).

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


Pls see my test spreadsheet attached.
Many many THANKS.

Nee

XLGibbs
02-21-2006, 01:56 PM
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.


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

joelle
02-21-2006, 02:26 PM
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
02-21-2006, 02:30 PM
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

That should do it. Just uncomment the On error line if it works okay..

joelle
02-21-2006, 02:31 PM
Oops - timing error with my post.

Nee

XLGibbs
02-21-2006, 02:34 PM
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
02-21-2006, 02:42 PM
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
02-21-2006, 02:49 PM
sorry, should be:


ActiveWorkbook.SaveAs "Copy of " & shtname


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

joelle
02-21-2006, 02:57 PM
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
02-21-2006, 02:59 PM
It should work, I have outlook and it did.

joelle
02-21-2006, 03:01 PM
I trust you 101%.
Will be rite back (again, you're quite fast -- I cant beat that).

Nee

XLGibbs
02-21-2006, 03:18 PM
I have like 200 moduleswith stuff like that. I just copy/paste quick edit if I have one appropriate..

joelle
02-21-2006, 03:28 PM
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
02-21-2006, 03:32 PM
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
02-21-2006, 03:34 PM
Oh, my bad, I should have mentioned it.

The highlight was on:
Application.DisplayAlerts = False

Thanks,
Nee

XLGibbs
02-21-2006, 03:45 PM
well, you can try it by commenting out those lines...but that is what will disable the warning prompt on the saveas...

Bob Phillips
02-21-2006, 03:58 PM
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
02-21-2006, 04:05 PM
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
02-21-2006, 05:11 PM
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
02-24-2006, 12:27 PM
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

Sub attach_it()
Application.Dialogs(xlDialogSendMail).Show
End Sub

XLGibbs
02-24-2006, 02:54 PM
Nee, you may want to start a new thread, since the issue here is more compatibility with the IE browser than the actual code. Unfortunately, I am not that familiar with what may be needed to adjust for the often troublesome IE..I don't dabbel in the web component stuff yet, so i wouldn't even know where to start with it!

joelle
02-24-2006, 03:07 PM
Hello XLGibbs,

Thanks for forgetmenot ... you can forgetmenow.

Good suggestion about a new thread -- and if site admins blame me for the dup guess whom I'd point them to ... just kidding.

Regards,
Nee

XLGibbs
02-24-2006, 03:12 PM
Don't get me wrong, I am trying to figure it out nonetheless..but you may get more experienced responses with a new thread..it is primarily a new issue..

appropriate tile might be "Send Mail attachment IE Problem"

joelle
02-24-2006, 03:30 PM
Oh no XLGibbs - how could I get you wrong (or maybe my poor English did)
You work too hard helping people so I was just teasing you :p

Like said, it is a good idea to start a new thread - I'm sorry to confuse people so far with my unclear question.

Later,

Nee