Emailing Multiple Worksheets

[UmbilicalNET]

I hope I'm not getting too greedy here with the great help on this site, but....

In the code



Private Sub CommandButton1_Click()
Dim strDate As String
ActiveSheet.Cells.Copy
Workbooks.Add
Selection.PasteSpecial Links = False
strDate = Format(Date, "mm-dd-yy")
ActiveWorkbook.SaveAs strDate & ".xls"
ActiveWorkbook.SendMail "<recipient> (commlink2@charter.net)", _ "Daily Sales Report for " & strDate
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False
End Sub

How would I add an additional worksheet (same workbook) to the same email? This worksheet would never be an "ActiveSheet".

Any takers? TIA

[Zack Barresse]

Hi there!

So are you just wanting to add an extra blank sheet? I guess I'm not understanding if this isn't the case.

For that, all you'd need would be something like this:



With ActiveWorkbook
Worksheets.Add
End With


But the real question is, what is going to be on that sheet...

[UmbilicalNET]

Thanks for the response firefytr.

No, the worksheet won't be blank, and it's within the same workbook.

I've been trying to incorperate it in addition to the activesheet mailing I currently have but nothing I've tried has worked.

[jamescol]

The


ActiveWorkbook.SendMail "<recipient>", _ "Daily Sales Report for " & strDate

line of your code should send the entire workbook contents.

Does the workbook already contain all the worksheets at this point in the code? If not, are you saying that only the active worksheet is being mailed?

Also, what version and SP of Excel are you using?

James

[UmbilicalNET]

True code, James, but I don't want to send the entire workbook, just the active selection and one other worksheet.

I'm still working on this thing and I can't for the life of me figure it out. I fear I'll need to change my present code to accomplish the additional task. :(

[jamescol]

Will you always know the name of the sheet(s) you want to send? If so, I think I have a solution for you.

[jamescol]

Try this sample:



Dim wbOriginal As Workbook
Dim wbNew As Workbook
Dim strDate As String
Dim x As Integer
'Set a variable containing the active workbook to use later
Set wbOriginal = Workbooks.Item(ActiveWorkbook.Name)
'Create the new workbook
Workbooks.Add
strDate = Format(Date, "mm-dd-yy")
ActiveWorkbook.SaveAs strDate & ".xls"
'Set a variable for the new workbook to use later
Set wbNew = ActiveWorkbook
'Activate the original workbook
wbOriginal.Activate
'Moves the sheets in the original workbook to the front of new workbook.
wbOriginal.Worksheets(ActiveSheet).Copy Before:=wbNew.Worksheets(1)
wbOriginal.Worksheets("Name ot the 2nd worksheet").Copy _
After:=wbNew.Worksheets(1)
'Activate the new workbook
wbNew.Activate
'Send the new workbook, etc.
ActiveWorkbook.SendMail Recipients:="recipient"
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False

[UmbilicalNET]

Thanks for the response James.

"wbOriginal.Worksheets(ActiveSheet).Copy Before:=wbNew.Worksheets(1)" keeps erroring out, any ideas?

[Zack Barresse]

Hi,

If you changed this

wbOriginal.Worksheets(ActiveSheet).Copy Before:=wbNew.Worksheets(1)

to this

wbOriginal.ActiveSheet.Copy Before:=wbNew.Worksheets(1)

does that help?

[jamescol]

Could be a version difference. I use Office 2003, so if you are using something earlier the syntax may be different. If you are using an earlier version, press F2 while viewing your code to bring up the Object Browser. Locate the object/collection (worksheets in this case) and drill down into its properties and methods. Likely you'll locate the syntax difference.

You can also highlight or click on the object in your code and press F1 to bring up Contextual Help for the object, which should give you the proper syntax.

Let us know if you can't pin down the cause of the error and we'll see what else we can do to help.

Cheers,
James

[UmbilicalNET]

How sweet. That worked great. (Excel 2000)

Thanks again.

[Zack Barresse]

Great! Can you tell us the solution that was used to overcome the situation?

:)

[UmbilicalNET]

Ultimately, and hopefully in the end, this is the final. Though my code was changed, I didn't spend the days changing it, so James solution was perfect.

The only remaining question is where do I send the check ;)


Private Sub CommandButton1_Click()
Application.DisplayAlerts = False
Dim wbOriginal As Workbook
Dim wbNew As Workbook
Dim strDate As String
Dim x As Integer
'Set a variable containing the active workbook to use later
Set wbOriginal = Workbooks.Item(ActiveWorkbook.Name)
'Create the new workbook
Workbooks.Add
strDate = Format(Date, "mm-dd-yy")
ActiveWorkbook.SaveAs strDate & ".xls"
'Set a variable for the new workbook to use later
Set wbNew = ActiveWorkbook
'Activate the original workbook
wbOriginal.Activate
'Moves the sheets in the original workbook to the front of new workbook.
wbOriginal.ActiveSheet.PrintOut
wbOriginal.ActiveSheet.Copy Before:=wbNew.Worksheets(1)
wbOriginal.Worksheets("Draft").Copy _
After:=wbNew.Worksheets(1)
'Activate the new workbook
wbNew.Activate
'Send the new workbook, etc.
Sheets("Sheet1").Delete
Sheets("Sheet2").Delete
Sheets("Sheet3").Delete
ActiveWorkbook.SendMail "<recipient (commlink2@charter.net)", _
"Daily Sales Report for " & strDate
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False
Application.DisplayAlerts = True
End Sub

[jamescol]

If only there was a way to suppress those nasty little "Are you sure?" prompts during:



'Send the new workbook, etc.
Sheets("Sheet1").Delete
Sheets("Sheet2").Delete
Sheets("Sheet3").Delete


You can settle up with Dreamboat by clicking the PayPal icon at the top :)

Seriously - glad we could help!

Cheers,
James

[UmbilicalNET]

I'm sorry, I missed something (at the end)


Private Sub CommandButton1_Click()
Application.DisplayAlerts = False
Dim wbOriginal As Workbook
Dim wbNew As Workbook
Dim strDate As String
Dim x As Integer
'Set a variable containing the active workbook to use later
Set wbOriginal = Workbooks.Item(ActiveWorkbook.Name)
'Create the new workbook
Workbooks.Add
strDate = Format(Date, "mm-dd-yy")
ActiveWorkbook.SaveAs strDate & ".xls"
'Set a variable for the new workbook to use later
Set wbNew = ActiveWorkbook
'Activate the original workbook
wbOriginal.Activate
'Prints active worksheet.
wbOriginal.ActiveSheet.PrintOut
'Moves the sheets in the original workbook to the front of new workbook.
wbOriginal.ActiveSheet.Copy Before:=wbNew.Worksheets(1)
wbOriginal.Worksheets("draft").Copy _
After:=wbNew.Worksheets(1)
'Activate the new workbook
wbNew.Activate
'Delete crap
Sheets("Sheet1").Delete
Sheets("Sheet2").Delete
Sheets("Sheet3").Delete
'Send the new workbook, etc.
ActiveWorkbook.SendMail "<recipient> (commlink2@charter.net)", _
"Daily Install and QC Report for " & strDate
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False
Application.DisplayAlerts = True
End Sub



Code kindly forwarded by Jamescol, at the kickarse Vbaexpress board (home of the killer coders).

[UmbilicalNET]

What, me, help?

There is. "Application.DisplayAlerts = False" (only make sure you end the code with "Application.DisplayAlerts = True" to reactivate messages)

[Zack Barresse]

Yup. The same goes for any application.display settings. Namely also ScreenUpdating. Up until XL 2000, once the routine was completed, it returned to default (from what I understand), but with the newer versions you must do this manually. We just need to be more accountable for ourselves with it now.

And we're thrilled we could help! This is what we love to do!

Take care. :)

[jamescol]

Doh! DisplayAlerts is not part of the Application class in Outlook, which is where my object model knowledge is. I only get by with Excel. This is a good property to know, though, for future XL projects.

Thanks,
James

[Anne Troy]

home of the killer codersGeeze. I like that. :)
ALOT.
Hee hee.

[xneurosis]

This is very helpful! Thank you!