Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Aug 2005
    Location
    London/Kingston, Surrey, United Kingdom
    Posts
    518
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro to email sheets

    Hi all,

    I have a workbook with 10 sheets, does anyone know how to create a macro that will email each sheet to every person's email which is recorded on A1?

    Thanks

    Skender

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi

    I assume you have the email address in cell [A1] for each sheet, and you want to send just that sheet to that person.
    So try this:

    Code:
    Sub mailWorksheets()                                  'v1a
    
    Dim w As Worksheet
    
    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 = "update from xxxxxx"                   '<-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
    let me know if that does the trick

    zeddy
    Last edited by RetiredGeek; 2013-06-27 at 10:45. Reason: Fixed end code tag from backslash to forward slash

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

    JeffErickson (2013-06-27)

  4. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi

    For some reason, the [code] tags didn't work in my post.
    I know not why.

    zeddy

  5. #4
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Just seeing if Code Tags are still working . . .

    Code:
    Here's a bit of non-existent code !
    Yep, seems to be OK. Must be at your end, Z ?

  6. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Zeddy,

    I fixed it. You used a backslash in the end tag vs a forward slash.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. The Following User Says Thank You to RetiredGeek For This Useful Post:

    zeddy (2013-06-27)

  8. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi RG

    Many thanks for pointing my virgule in the right direction.
    It's been Monday all week for me.

    zeddy

  9. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi RG

    Just to let you know it's back to Friday for me now.
    I am a happy bunny once more.
    And I shall make sure I get all my slashes pointing forward.

    zeddy

  10. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    MS MVP Ron deBruin has several code examples for emailing at http://www.rondebruin.nl/win/s1/div/mail.htm that you may find useful.

    Steve

Posting Permissions

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