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

    Code to email Sheets

    I have the following code to email several sheets to different people based on the email address on the respective sheets


    I want to amend the code so that it appears in my outbox so that I can then hit the send/receive button to email the files


    Currently I have to hit allow several times in order for the sheets to be emailed-see screen shot


    Code:
     Sub mailWorksheets()                                 
    
    Application.ScreenUpdating = False               
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    For Each w In ThisWorkbook.Worksheets       
    If w.[a1].Value Like "*@*" Then                   'check for email address in cell [A1]
    w.Copy                                                       'copy worksheet to new workbook
    
    ActiveWorkbook.SaveAs "C:\temp\" & w.Name & " of " & ThisWorkbook.Name & ".xls"
    zSendTo = ActiveSheet.[a1].Value                 'email recipient
    zSubject = "Admin data File"                   '<-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
    New Lounger
    Join Date
    Oct 2015
    Posts
    2
    Thanks
    0
    Thanked 1 Time in 1 Post
    Howard, I solved this in the past by using a program called ClickYes. It interrupt handles the Outlook popup dialogs on its own so you don't have continue to process them. I just checked the website and it's still free up to Outlook 2003. Later versions require the paid version but they do have a free trial. http://www.contextmagic.com/express-clickyes/

    Bill

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

    Maudibe (2015-10-09)

  4. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Howard,

    You might also try in Outlook going to File > Options > >Trust Center > Pragrammatic Access > check the option button to Warn warn about suspicious activity when Anti-virus soft ware is disabled or out of date. You might also select to turn it off all together

    HTH,
    Maud

    warning.png
    Last edited by Maudibe; 2015-10-09 at 16:45.

  5. #4
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Thanks Bill & Maud for your input

Posting Permissions

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