VB

[krishnaa_kumarr88]

Hi all,

Thanks for looking at my post. I am wondering ,
1. is there is any way of sending automatic warning email if the excel is not opened for 8 days ?
2. Is it possible to delete the complete spreadsheet once in a year automatically and i would like to get automatic PDF document once the spreadsheet is deleted.

Please help me guys,
Thanks.

[Rudi]

Hi,

Not sure about #1

As for #2
I don't think VB will be able to manage this as well as using either the Windows Task Scheduler or a third party application like (for example) Belvedere. As for the PDF file, is that a saved copy of the deleted Excel file, or is it just an info source informing that the file has been deleted. In either event, I doubt that Windwos Task Scheduler or the third party app I referred to could do that!? One could attempt to design a VB (*.exe) procedure to deal with this, but this is beyond the scope of my knowledge.

1.jpg

[krishnaa_kumarr88]

thanks

[Don Wells]

Hi all,

Thanks for looking at my post. I am wondering ,
1. is there is any way of sending automatic warning email if the excel is not opened for 8 days ?

Please help me guys,
Thanks.

1.    Are you interested in the Excel application being opened during the 8 day period? Or a specific workbook?

2.    Is this specific to a single machine? or is the file of interest available to more users than one -- saved on a server?

[krishnaa_kumarr88]

hI DON,

1. I would like to send automatic email to few people in outlook, if the particular excel is not opened for few days.
2. It will be saved on server for around 6 users.

Please help
:)
thanks

[HansV]

I think it would be rather complicated. I'd let those responsible for the workbook set up a recurring task or appointment in their Outlook to remind them to look at the workbook.

[krishnaa_kumarr88]

THANKS :)

[Don Wells]

Hi Krishnaa
    I believe that I see a way forward, but have some more questions that require your input, and assumptions that require your confirmation or clarification.

• It is assumed that there will be a single administrator who's computer will create the e-mail alerts.
• What version of Excel is the administrator running?
• It is assumed that the administrator is running Outlook.
• Do users other than the 6 or so users of interest (UOI), have access to the file of interest (FOI)?
• Do the UOI have write privileges to the FOI?
• Does anyone other than the UOI and administrator have write privileges to the FOI?
• Are you interested in the UOI's access to the workbook only, or is your real interest in their having activated specific sheets?
• What is the extension on the FOI? (xls; xlsx; xlsm; csv; etc)
• It is assumed that the process to determine if any messages are required will be launched each time the administrator logs-on or launches Excel.
• It is assumed that a nag message will be sent on a daily basis if the UOI has not accessed the appropriate workbook/worksheet(s).
• It is assumed that each of the UOI will have their machines set to allow macros in the FOI to run.

Hans is correct. It will be a little complicated, but the maintenance should be minimal.

[krishnaa_kumarr88]

Hi Don,
1. yes. Single administrator e mail alerts
2. EXcel 2010
3. yes. We running outlook
4. May be. Not exactly 6 users may use it. It might be higher or lower than that
5. yes. UOI will have write privileges to FOI
6. No.
7. UOI access to workbook , not for specific sheets
8. Xls
9. Email should be received after 8 days if specific workbook is not update or not opened for 8 days. Email should be received automatically as we might not know when the user will open the spreadsheet again
10. No. Once in 8 days, if user did not update the excel. If the user update the excel before 8 days than we should not receive email. This should be continuous process
11. yes.

Thanks,
Please help

[Don Wells]

The approach I visualize is as follows:

1. Since the administrator may be absent for several days, the notification process will be exercised each time the administrator or one of the UOI open the FOI.
2. The FOI will contain a hidden log of usage. This log will contain:
   1. A list of the username for each UOI (maintained by the administrator);
   2. The e-mail address for each UOI (maintained by the administrator);
   3. The salutation for each UOI (maintained by the administrator);
   4. The date and time each user accessed the FOI (entered automatically when the file is opened by the user);
   5. The date and time that a message was sent to a delinquent user (entered automatically when the code creates the message)
3. The log will be visible only to the administrator
4. The process will also be exercised each time the administrator starts his machine or launches excel.
5. The process will be as follows when the FOI is opened:
   1. The date and time of opening the file will be entered against the user in the log;
   2. The log will be interrogated for each of the UOI for the last access time and date;
      1. If the last access time for a user exceeds 8 days and the time of the last message generated to that user exceeds 24 hours then:
         1. A message is sent to the delinquent user and copied to the administrator;
         2. The date and time is updated in the last message field

Note that this process may not work properly if the FOI is used in a shared mode and the user creates a copy for their session.

Please review this and let me know if you would like me to proceed with creating the code.

Criticism from any and all Loungers is most welcome.

[Don Wells]

Hi Krishnaa

Is there any chance that any of the UOI will be operating from a different time zone from the administrator?

[krishnaa_kumarr88]

I am really glad to hear that you gonna help me.
Thanks a lot for that.
What you mentioned is absolutely right. I would like to get mail each time when the administrator log his machine provided if the previous update was 8 days before.
Thanks a lot .

[Don Wells]

I would like to get mail each time when the administrator log his machine provided if the previous update was 8 days before.

You have managed to confuse me.

1. I have assumed that the only people involved in this process were the UOI and the administrator.
2. In this discussion the administrator is the person responsible for whatever maintenance is required on the usage log as well as overseeing the general operation of whatever data gathering and dissemination is intended through the use of the FOI (the work supported by the intended code).
3. Please explain exactly what you mean by, "the administrator log his machine".
4. You say, "I would like to get mail each time". What role do you play in this scenario?
   1. UOI?
   2. UOI with supervisory interest?
   3. administrator?
   4. nonparticipating onlooker?
5. Am I correct in assuming that the UOI will be updating the data in the FOI?
6. Will the administrator be one of those updating data in the FOI? (Yes/No/Possibly)

Please advise.

[krishnaa_kumarr88]

Sorry for the confusion
1. Yes for number 1
2. Sorry for the confusion about administrator.
4. I am gonna be non participating onlooker
5. Yes. Uoi will be updating the data in the foi
6. Possibly

Sorry for any inconvenience
Thanks

[Don Wells]

Hi Krishnaa
Some outstanding questions and a new request:

From post 137159

Is there any chance that any of the UOI will be operating from a different time zone from the administrator?

Question 3 from post 137165

Please explain exactly what you mean by, "the administrator log his machine".

It would be very useful if you were to describe the workbook and how it is used by the various participants.

[krishnaa_kumarr88]

Hi,
1. NO. UOI will operate in the same time zone
2. Various participants use the workbook to load the data once in a week. If anyone forget to enter data once in a week then i would like to get an automatic email

[Don Wells]

Hi Krishnaa
    The attached workbook should provide your solution, however there are a few tasks that you need to perform first:

1. Copy the following code into the 'ThisWorkbook' module of your workbook;

   Code: Select all

   Private Sub Workbook_BeforeClose(Cancel As Boolean)
     Call modUsageTracking.End_Proc
   End Sub
   
   Private Sub Workbook_Open()
     Call modUsageTracking.Start_Proc
   End Sub
   

2. Copy the two standard modules ('modUsageTracking' and 'Open_Outlook' ), from the attached workbook to your workbook;
3. Copy the 'Usage Log' worksheet from the attached workbook to your workbook. On opening the attached workbook the 'Usage Log' worksheet will be hidden; to make it visible run the macro 'Maint_Proc';
4. Update the 'Usage Log' worksheet in your workbook as follows:
   1. Clear all entries in rows 2:4
   2. For each user:
      1. Enter the username in Column A. It is very important that this be exactly as found on the user's machine as one of the folders in 'C:\Users'
      2. Enter the user's e-mail address in Column B;
      3. Select the appropriate role from the pulldown list in Column C;
      4. In Column D enter the salutation to be used in the e-mail message;
      5. Leave Columns E & F blank as they will be populated automatically.

    The initial message to each user will be tailored from what is shown in the attached image 'First_msg.jpg'. Subsequent messages will be of the form shown in the attached image 'Following_msg.jpg'.

[krishnaa_kumarr88]

brilliant work. Is it possible to check whether code is working or not by reducing 8 days to 5 min.
Example if the spreadsheet is not opened for 5 min then how can i get mail just for checking the code.

thanks

[HansV]

The check for the number of days is in the line

Code: Select all

          If Now - .Cells(RoI, 5) > 8 And Now - .Cells(RoI, 6) > 1 Then

You can change this to

Code: Select all

          If Now - .Cells(RoI, 5) > TimeValue("0:05") And Now - .Cells(RoI, 6) > TimeValue("0:05") Then

[krishnaa_kumarr88]

BRILLIANT WORK. BUT THE PROBLEM IS I AM GETTING MAIL ONLY AFTER OPENING SPREADSHEET. BUT I WOULD LIKE TO GET MAIL EVEN BEFORE OPENING THE SPREADSHEET.
THAT IS IF THE SPREADSHEET IS NOT OPENED FOR 5 MIN AND I SHOULD GET AUTOMATIC EMAIL SAYING THAT I FORGOT TO UPDATE SPREADSHEET/FORGOT TO OPEN THE SPREADSHEET.

THANKS,