Results 1 to 7 of 7

Thread: Emailing Files

  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Emailing Files

    I have recently upgraded to Windows 10 and using office 2010

    My macro used to email email each sheet on a workbook perfectly before upgrading to Windows 10. I am still using my previous version of Excel

    I now get an error message -see attached

    See my code below


    It would be appreciated if someone could kindly assist me

    Code:
     Sub mailWorksheets()                                  'v1a
    
    
    Application.ScreenUpdating = False               'freeze display for speedup
    
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    For Each w In ThisWorkbook.Worksheets       'loop through all sheets
    If w.[a1].Value Like "*@*" Then                   'check for email address in cell [A1]
    w.Copy                                                       'copy worksheet to new workbook
    
    ActiveWorkbook.SaveAs "Sheet " & w.Name & " of " & ThisWorkbook.Name & ".xls"
    zSendTo = ActiveSheet.[a1].Value                 'email recipient
    zSubject = "Admin Comm File from Howard"                   '<-edit subject line here as required
    ActiveWorkbook.SendMail zSendTo, zSubject   'send email to recipient
    
    ActiveWorkbook.ChangeFileAccess xlReadOnly  'change workbook to read-only for deletion
    Kill ActiveWorkbook.FullName                         'delete saved copy of temporary workbook
    ActiveWorkbook.Close False                            'close temporary workwook
    
    End If                                                          'end of test for email address in cell [A1]
    Next w                                                         'process next worksheet
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    
    Application.ScreenUpdating = True           'refresh display
    
    End Sub
    Attached Images Attached Images

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Howard

    Whoever did that code has documented it nicely.

    It might be that under Windows10 Excel processes vba code faster.
    The error message you are seeing seems to show the temp file that Excel is creating for each worksheet hasn't finished yet. So we can either add a pause in the routine, or perhaps a 'DoEvents' will do the trick.
    Try this code:
    Code:
    Sub mailWorksheets()                         'v1b
    
    Application.ScreenUpdating = False           'freeze display for speedup
    
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    For Each w In ThisWorkbook.Worksheets       'loop through all sheets
    If w.[a1].Value Like "*@*" Then             'check for email address in cell [A1]
    w.Copy                                      'copy worksheet to new workbook
    ActiveWorkbook.SaveAs "Sheet " & w.Name & " of " & ThisWorkbook.Name & ".xls"
    DoEvents                                    'wait till Excel catches up
    zSendTo = ActiveSheet.[a1].Value            'email recipient
    zSubject = "Admin Comm File from Howard"    '<-edit subject line here as required
    ActiveWorkbook.SendMail zSendTo, zSubject   'send email to recipient
    ActiveWorkbook.ChangeFileAccess xlReadOnly  'change workbook to read-only for deletion
    Kill ActiveWorkbook.FullName                'delete saved copy of temporary workbook
    ActiveWorkbook.Close False                  'close temporary workwook
    End If                                      'end of test for email address in cell [A1]
    Next w                                      'process next worksheet
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    
    Application.ScreenUpdating = True           'refresh display
    
    End Sub
    zeddy

  3. The Following User Says Thank You to zeddy For This Useful Post:

    HowardC (2015-09-10)

  4. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy


    Thanks for the help. I still get the same error message and the following code is highlighted


    Code:
     ActiveWorkbook.SaveAs "Sheet " & w.Name & " of " & ThisWorkbook.Name & ".xls"
    It would be appreciated if you would kindly advise on how to resolve this

  5. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,635
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Howard,

    Try including the path. Replace in blue with your path and make sure there is a "\" at the end

    Code:
    ActiveWorkbook.SaveAs "C:\Users\Maudibe\Desktop\" & w.Name & " of " & ThisWorkbook.Name & ".xls"
    path.png

    HTH,
    Maud
    Last edited by Maudibe; 2015-09-10 at 15:16.

  6. The Following User Says Thank You to Maudibe For This Useful Post:

    HowardC (2015-09-10)

  7. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Thanks for your help Maud

  8. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Howard

    ..is it fixed?

    zeddy

  9. #7
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    The problem is fixed thanks

Posting Permissions

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