Macro to save filename with password?


Posted by Matt P on July 11, 2000 4:37 AM

I need to send weekly workbooks to certain managers based on a list of data. However, the data is confidential, so I am hoping to write a macro that does the following:
-Saves the workbook with each Manager's division's name and the manager's password
-Possibly a macro that automatically emails the completed workbook to that manager
Anyone have any advice?
Thanks!!!

Posted by Ryan on July 11, 0100 5:10 AM

Matt,
Sounds like an easy way to do this is to set up a special type. Do this using.

From here you can code the names and passwords into this type and loop trough this and save and email this. I posted a hyperlink to a page with a good example on how to email. If you need some more help, let me know!

Ryan


http://www.mindspring.com/~tflynn/excelvba.html
Sub EmailManager()

Dim MangerList(1 To X) As ManagerInfo ' where x is you put how many mangers you have
MangerList(X).Name = "Bob Smith"
MangerList(X).Email = "bs@aol.com"
MangerList(X).Password = "bsaol"
MangerList(X).Division = "South"
' do this to set up all variable, or you can get info from a sheet

For I = 1 To X
ActiveWorkbook.SaveAs FileName:=MangerList(I).Division, Password:=MangerList(I).Password
' Set up the code here to email the workbook
Next I

End Sub

Posted by Matt P on July 11, 0100 7:46 AM

Ryan,
Thanks for the response, but it seems part of your message is missing! Your message states "Do this using." Can you elaborate or am I missing something? Thanks!

Posted by Ryan on July 11, 0100 12:42 PM

Opps,
Here you go, what I meant was do this using a User Defined Type:

Type Manager Info
Name As String
Password As String
Email As String
Region As String
End Type

This goes in the declarations sections. The other code is still okay. If you need some more explaination let me know, or email me the workbook and I can set it up for you.

Ryan



Posted by Testing on July 12, 0100 6:28 PM