Consulting

Results 1 to 20 of 20

Thread: Solved: Emailing Multiple Worksheets

  1. #1

    Solved: Emailing Multiple Worksheets

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

    In the code


    [vba]
    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>", _ "Daily Sales Report for " & strDate
    ActiveWorkbook.ChangeFileAccess xlReadOnly
    Kill ActiveWorkbook.FullName
    ActiveWorkbook.Close False
    End Sub[/vba]

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

    Any takers? TIA

  2. #2
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,892
    Location
    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:

    [vba]
    With ActiveWorkbook
    Worksheets.Add
    End With
    [/vba]

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

  3. #3
    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.

  4. #4
    VBAX Tutor jamescol's Avatar
    Joined
    May 2004
    Location
    Charlotte, NC
    Posts
    251
    Location
    The
    [vba]
    ActiveWorkbook.SendMail "<recipient>", _ "Daily Sales Report for " & strDate [/vba]

    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
    "All that's necessary for evil to triumph is for good men to do nothing."

  5. #5
    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.

  6. #6
    VBAX Tutor jamescol's Avatar
    Joined
    May 2004
    Location
    Charlotte, NC
    Posts
    251
    Location
    Will you always know the name of the sheet(s) you want to send? If so, I think I have a solution for you.
    "All that's necessary for evil to triumph is for good men to do nothing."

  7. #7
    VBAX Tutor jamescol's Avatar
    Joined
    May 2004
    Location
    Charlotte, NC
    Posts
    251
    Location
    Try this sample:

    [vba]
    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
    [/vba]
    "All that's necessary for evil to triumph is for good men to do nothing."

  8. #8
    Thanks for the response James.

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

  9. #9
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,892
    Location
    Hi,

    If you changed this
    [vba]wbOriginal.Worksheets(ActiveSheet).Copy Before:=wbNew.Worksheets(1)[/vba]

    to this
    [vba]wbOriginal.ActiveSheet.Copy Before:=wbNew.Worksheets(1)[/vba]

    does that help?

  10. #10
    VBAX Tutor jamescol's Avatar
    Joined
    May 2004
    Location
    Charlotte, NC
    Posts
    251
    Location
    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
    "All that's necessary for evil to triumph is for good men to do nothing."

  11. #11
    How sweet. That worked great. (Excel 2000)

    Thanks again.

  12. #12
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,892
    Location
    Great! Can you tell us the solution that was used to overcome the situation?


  13. #13
    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

    [VBA]

    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", _
    "Daily Sales Report for " & strDate
    ActiveWorkbook.ChangeFileAccess xlReadOnly
    Kill ActiveWorkbook.FullName
    ActiveWorkbook.Close False
    Application.DisplayAlerts = True
    End Sub

    [/VBA]

  14. #14
    VBAX Tutor jamescol's Avatar
    Joined
    May 2004
    Location
    Charlotte, NC
    Posts
    251
    Location
    If only there was a way to suppress those nasty little "Are you sure?" prompts during:

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

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

    Seriously - glad we could help!

    Cheers,
    James
    "All that's necessary for evil to triumph is for good men to do nothing."

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

    [VBA]

    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>", _
    "Daily Install and QC Report for " & strDate
    ActiveWorkbook.ChangeFileAccess xlReadOnly
    Kill ActiveWorkbook.FullName
    ActiveWorkbook.Close False
    Application.DisplayAlerts = True

    End Sub

    [/VBA]

    Code kindly forwarded by Jamescol, at the kickarse Vbaexpress board (home of the killer coders).
    Last edited by Zack Barresse; 06-08-2004 at 09:42 PM.

  16. #16
    What, me, help?

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

  17. #17
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,892
    Location
    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.

  18. #18
    VBAX Tutor jamescol's Avatar
    Joined
    May 2004
    Location
    Charlotte, NC
    Posts
    251
    Location
    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
    "All that's necessary for evil to triumph is for good men to do nothing."

  19. #19
    Site Admin
    The Princess
    VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    home of the killer coders
    Geeze. I like that.
    ALOT.
    Hee hee.
    ~Anne Troy

  20. #20
    This is very helpful! Thank you!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •