Creating Pasword Protected Excel sheets from Access?

[mrf1xa]

I have written a database which produces a list of staff, split by Manager, showing system access rights for regular review. The database creates these as Excel spreadsheets with the managers name in the title for ease of mailing (canlt automate mailing at present due to using Lotus Notes, worse luck!

However, Audit Dept have just said the sheets MUST be password protected before sending. There are over 500 so don't fancy doing this manually. Is it possible to create the password protection from Access, I have no idea how to start?

Thanks

Nigel
Didn't someone say work is supposed to be fun? They didn't have computers then I guess....

 

[PaulBricker]

A couple things come to mind. First, can you create a protected Excel template and then use that template for your data?
Second you can look at this article that shows you how to protect all the worksheets in a workbook.

http://www.ozgrid.com/VBA/excel-worksheet-protect-all.htm

Paul

 

[MikeC14081972]

Access can be used to automate Lotus Notes with no problems.

Dim objNotesDB          As Object ' Notes Database
Dim objNotesDoc         As Object ' Notes Document
Dim objNotesRTF         As Object ' Notes Rich Text Item
Dim objSession          As Object ' Notes Session
Dim SendTo              As String
Dim msgSubject          As String
Dim AutoSend            As Boolean ' True/False is Email automatically sent
       
Public Sub LotusNotesMail()

' Set Variables
AutoSend = False ' False saves to drafts folder True would send straight away
SendTo = "<string of recipient addresses>"
Cc = "<String of cc addresses>"
                
        ' Create Email
        Set objSession = CreateObject("Notes.NotesSession")
        Set objNotesDB = objSession.getdatabase("", "") ' Default Users Notes Account. Insert Server and Database details to use group mailboxes
                
        ' Set Notes Text Styles
        Set header = objSession.CreateRichTextStyle
        Set bodytext = objSession.CreateRichTextStyle
        Set headings = objSession.CreateRichTextStyle
        Set restrict = objSession.CreateRichTextStyle
        Set disclaimer = objSession.CreateRichTextStyle

        ' Build Text Style
            '  Headings
                With headings
                    .NotesFont = FONT_COURIER
                    .FontSize = 10
                    .Bold = -1
                    .Underline = -1
                    .NotesColor = COLOR_BLACK
                End With
            ' Main Text
                With bodytext
                    .NotesFont = FONT_COURIER
                    .FontSize = 10
                    .Bold = 0
                    .Underline = 0
                    .NotesColor = COLOR_BLACK
                End With
                           
            ' Open Notes Mail
             objNotesDB.OPENMAIL 
            
            If (objNotesDB.IsOpen) Then
                                
            msgSubject = "<String of message subject>"
             
            ' Create a new message
            Set objNotesDoc = objNotesDB.CreateDocument
            objNotesDoc.ReplaceItemValue "SendTo", SendTo
            objNotesDoc.ReplaceItemValue "CopyTo", Cc
            objNotesDoc.ReplaceItemValue "Subject", msgSubject
            
            Set objNotesRTF = objNotesDoc.CreateRichTextItem("body")
            Set objAttachRTF = objNotesDoc.CreateRichTextItem("File")
                        
           ' Build Body of email
               With objNotesRTF
                    .AppendStyle (headings)
                    .AppendText "Header"
                    .AppendStyle (bodytext)
                    .AddNewLine 1
                    .AppendText "Text to Appear in Body of Email"
                End With

                       
            ' Get the mail to appear in sent items folder
            objNotesDoc.SaveMessageOnSend = True
            objNotesDoc.Save True, True
            
            'Send the message
            If (AutoSend) Then
                objNotesDoc.posteddate = Now()
                objNotesDoc.Send False
            End If
        
        Else
            MsgBox ("Lotus Notes Could Not Be Opened."), vbInformation
        End If
        
        Set objNotesDB = Nothing
        Set objSession = Nothing
    
End Sub

Hope that helps, as a starting point but you add your emails as attachments etc.