Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

LinkBack Thread Tools Display Modes
Old 08-10-2017, 12:47 PM   #1
Join Date: Mar 2017
Posts: 2
bconner is on a distinguished road
Default Open Excel from MS Access and Password Protect Workbook

I am trying to open a excel file password protect it then email out as attachment.
Everything works fine except I can't password protect the workbook. Below is my code.

Private Sub cmdRun_Click()
Dim strSQL As String
Dim strReportLocation As String
Dim qd As QueryDef
Dim db As Database
Dim rs As DAO.Recordset
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Dim xl As New Excel.Application
Dim wkbook As Workbook
Dim fileToOpen As String
On Error GoTo err_handler
User = CreateObject("wscript.Network").UserName
DoCmd.SetWarnings False
'Open the Email Distribution List
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT TAXID, EMAIL FROM LIST")
'Loop through distribution list and send member detail to excel file
If Not (rs.EOF And rs.BOF) Then
Do Until rs.EOF
     On Error Resume Next
     strSQL = "SELECT [MEMBER], [PAID], [email] FROM Rpt WHERE [TAXID] = " & rs![TAXID]
          Set qd = CurrentDb.CreateQueryDef("Email_Provider_Incentive", strSQL)
          strReportLocation = "C:\Users\" & User & "\Desktop\Provider_Incentive" & Format(Date, "mmddyy") & ".xls"
          DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Email_Provider_Incentive", strReportLocation, True
          CurrentDb.QueryDefs.Delete "Email_Provider_Incentive"
          xl.DisplayAlerts = False
          Set wkbook = xl.Workbooks.Open(strReportLocation)
          fileToSaveAs = "Provider_Incentive" & Format(Date, "mmddyy") & ".xls"
          ActiveWorkbook.SaveAs FileName:=fileToSaveAs, Password:="ANTHEM"
          xl.DisplayAlerts = True
    'Send email with attached excel workbook created above
        Set appOutLook = CreateObject("Outlook.Application")
        Set MailOutLook = appOutLook.CreateItem(olMailItem)
        With MailOutLook
            .BodyFormat = olFormatRichText
            .To = rs![email]
            .Subject = "[SECURE] Provider Incentive Auto email TEST"
            .HTMLBody = "This is a TEST of the Provider Incentive Auto Email"
            .Attachments.Add (strReportLocation)
        End With
 'Move to the next TAXID
  MsgBox "There are no Records to process"
End If
rs.Close 'Close the recordset
Set rs = Nothing 'Clean up
Set xl = Nothing
Set wkbook = Nothing
DoCmd.SetWarnings True
MsgBox "The Automated Provider Email process has completed."
Exit Sub
rs.Close 'Close the recordset
Set rs = Nothing 'Clean up
Set xl = Nothing
Set wkbook = Nothing
DoCmd.SetWarnings True
MsgBox Err.Number & Err.Description
End Sub
bconner is offline   Reply With Quote
Old 08-20-2017, 05:27 PM   #2
Join Date: Apr 2014
Location: Mississippi
Posts: 361
charlesdh is on a distinguished road


A bit of code you may try. Not tested.

With ActiveWorkbook
.SaveAs FileFormat:=xlNormal, Password:="YourPassword", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End With
Thanks, Charles
charlesdh is offline   Reply With Quote
Old 09-21-2017, 02:32 AM   #3
Competent Performer
Join Date: Oct 2015
Posts: 143
Debaser is on a distinguished road

You should be using your wkbook variable and not ActiveWorkbook, and you should specify the file format as well:

          wkbook.SaveAs FileName:=fileToSaveAs, FileFormat:=56, Password:="ANTHEM"
Debaser is online now   Reply With Quote

Thread Tools
Display Modes

Similar Threads
Thread Thread Starter Forum Replies Last Post
Protect a workbook without using a password. aussiemight Excel 3 04-26-2017 07:32 PM
how to create a box in word document that takes a password to open an access field kawtharz Word VBA 2 08-13-2015 12:43 AM
Need to open an Excel WorkBook from Word VBA Pierre-Hugues Word VBA 4 10-07-2013 06:27 AM
Unable to open Excel 97-2003 workbook J Partridge Excel 1 11-07-2010 03:26 AM
password protect excel file? Dawg751 Excel 2 01-28-2010 06:23 AM

All times are GMT -7. The time now is 10:14 AM.

Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc. is not affiliated with Microsoft