Results 1 to 6 of 6
  1. #1
    ar0927 is offline Novice
    Windows 7 32bit Access 2010 64bit
    Join Date
    Apr 2017
    Posts
    5

    Access VBA Code to Protect Sheet in existing Excel file, Email as Attachment, then Unprotect Sheet

    I was hoping to get some help with Access VBA code that will go to an existing Excel workbook, then Protect Sheet (not Protect Workbook), then attach it to an email and send, then Unprotect Sheet.



    The code I have right now will open the Excel workbook and call an Excel Macro that will Protect Sheet, then save the workbook and close the excel application. However, I would prefer to use Access VBA code to protect and unprotect the sheet (as opposed to calling the Excel Macro to Protect Sheet), but I am having trouble with writing VBA code to perform this. A

    lso, I have not yet been able to get any emailing code to work for me. I am familiar with the code to email an access query or table, but I am not familiar with how to attach an existing file to an email.

    Any assistance you are able to provide would be appreciated!

    Thank you in advance for your time!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    What does 'having trouble' mean - error message, wrong results, nothing happens? Post your attempted code for analysis.

    Attaching external file to email requires Outlook automation code, many examples on the web. Search forum or Google. Here is one https://forums.aspfree.com/microsoft...ro-447084.html.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ar0927 is offline Novice
    Windows 7 32bit Access 2010 64bit
    Join Date
    Apr 2017
    Posts
    5
    Thank you for the reply, June.

    For the Emailing Issue:
    I have found email code online similar to the link you provided, but no matter which code I try from online it wont go past the "Dim appOutLook As Outlook.Application" line in the code. My Access does not have the Microsoft Outlook 12.0 Object Library that is referenced in the link you provided, which may be the cause of this particular issue. Instead, I have the Microsoft Outlook 14.0 Object Library. Is there a different code needed for the newer version of the Object Library? I have verified that the 12.0 library is not even available for me to "check" in the list of Available References.

    For the Protected Sheet Issue:
    As I mentioned in my original post, the code I am currently using will open the Excel workbook and call an Excel Macro that will Protect Sheet, then save the workbook and close the excel application. However, I would prefer to use Access VBA code to protect and unprotect the sheet (as opposed to calling the Excel Macro to Protect Sheet). Unfortunately I am not sure what the proper code is to Protect Sheet (instead of Protect Workbook). I was hoping someone on here might know how to do this.

    Code:
    Function EmailCountSheets()
    Dim dbs As Database
    Set dbs = CurrentDb
    
    Set excelApp = CreateObject("Excel.application", "")
    excelApp.Visible = False
    Set Workbook = excelApp.Workbooks.Open("file path")  'In my actual code, I have the actual File path, but I chose not to share it here
    'In my actual code, I have it calling an Excel Macro to Protect Sheets, but this is what I would like to change.  This is where I need some help.  What code could I use to have Access Protect the Sheet?
    Workbook.Save
    excelApp.Application.Quit
    'After the Sheet is protected, I want to email it (see post for the issues I had with the email code)
    'After the email is sent, I want to Unprotect the Excel Sheet
    
    End Function

    Thank again for any assistance anyone is able to provide. I appreciate your time and your expertise!

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    I have Access 2010 and set the Outlook 14 library and it all works. The code is the same.

    Example of code I have behind a workbook for protecting/unprotecting sheet:

    Code:
        With Worksheets("Start")
            .Unprotect
            .Select
            .OptionButton11.Visible = False
            .OptionButton12.Visible = False
            .OptionButton13.Visible = False
            .OptionButton14.Visible = False
            .OptionButton15.Visible = False
            .OptionButton11.Value = "True"
            .Range("B9").Value = ""
            .Range("B11:B15").Value = ""
            .btnContinue.Visible = False
            .Protect
        End With
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    ar0927 is offline Novice
    Windows 7 32bit Access 2010 64bit
    Join Date
    Apr 2017
    Posts
    5
    I am not sure how I could use that code? How could I add it to the code that I provided previously?

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    This worked for me.
    Code:
    Sub EmailCountSheets()
    Dim xclApp As Excel.Application, xclWB As Excel.Workbook
    Set xclApp = CreateObject("Excel.application", "")
    xclApp.Visible = False
    Set xclWB = xclApp.Workbooks.Open("C:\Users\Owner\June\MyStuff\Condos.xlsx")
    xclWB.Worksheets("Sheet1").Protect
    xclWB.Save
    Call SendEmail
    xclWB.Worksheets("Sheet1").UnProtect
    xclWB.Close savechanges:=True
    xclApp.Quit
    End Sub
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 03-26-2015, 10:42 AM
  2. Replies: 4
    Last Post: 01-24-2013, 06:30 PM
  3. Replies: 26
    Last Post: 01-08-2013, 04:55 PM
  4. Replies: 1
    Last Post: 05-01-2012, 03:52 AM
  5. Password Protect an Email Attachment in Access 2007
    By amangupts in forum Programming
    Replies: 3
    Last Post: 12-21-2011, 04:37 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums