vb

K

krishnaa_kumarr88

New member
Joined
Sep 30, 2014
Messages
26
Reaction score
0
Points
0
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.
 
In theory, yes, but it leads to some questions...

The Excel file needs to be open to run it's code. So how are you going to make sure that the host file gets opened? Are you going to open it yourself every day to have it check, or are you going to try and run it on a machine and schedule it?

  • If it's the first, then no big deal, you could just create a file to check the last opened date/time and run it every time you open the file. But if you (or someone else) isn't there to open the original file, the routine won't run.
  • If the latter, then you might be better served building a VBScript routine and scheduling it to run on a server.

With regards to the automatic PDF and deletion, again yes it's possible. Again, what is the trigger point?

While I wouldn't hesitate to code the routine to email users automatically, I would really think twice on the second. If I'm clearing history, I always make users click a button so that they know that it was done and can verify that it completed correctly. I'd rather code a routine to check if the file still exists and tell them to do so.
 
First of all thanks for your reply
1. I would like the machine to run the code and schedule it
2. Regarding pdf I think it's better to create manually rather than automatically

Do you have any code for number 1 please
Please help thanks
 
So are you going to have a computer that will be on all the time then, who also has access to an email software? If so, which specific software... is it Outlook?
 
Will Outlook be opened whenever the computer is on? I'm trying to figure out if we could set it up to just run and check the file every time Outlook is started, or will the computer run never opening Outlook at all?
 
Outlook need to be opened manually.
The aim of the excel is to indicate head of team that the excel is not updated within 8 days.
Because I would like to update excel once in 8 days. If i forget to update or open the excel for some reason then it should automatically send mail to the head of team in outlook.
Please help me
 
I'll help you, but I just want to make sure we're going to build the right solution first. All of these questions are pointed at finding the best outcome.

My thought right now is to create a routine like this:
-Runs (once) every time you launch Outlook
-Checks the timestamp on the file
-If Today()-File Timestamp > 8 days then it will send the email

Does that trigger sound correct?
 
Okay, give this a go...

  • Open Outlook, and press Alt+F11 to enter the VBE
  • Expand the Microsoft Outlook Objects --> ThisOutlookSession module
  • Paste the following code inside:

Code: 
Private Sub Application_Startup()
Dim fso As Object
Dim oFile As Object
Dim sFilePath As String
Dim dDays As Double
Dim oMail As MailItem
Dim sMailTo As String
Dim sMailSubject As String

'Set the details here
sFilePath = "C:\Temp\MyFile.xlss"
sMailTo = "[EMAIL="someone@somedomain.com"]someone@somedomain.com[/EMAIL]"
sMailSubject = "File has not been modified"

'This find out how long it's been since the file was last accessed
Set fso = CreateObject("Scripting.FileSystemObject")
Set oFile = fso.GetFile(sFilePath)
dDays = Now() - oFile.DateLastModified
Set oFile = Nothing
Set fso = Nothing

'Send the mail
Set oMail = CreateItem(olMailItem)
With oMail
.To = sMailTo
.Subject = sMailSubject
.Body = sFilePath & " has not been updated in " & CInt(dDays) & " days!"
.Send
End With

End Sub


  • Modify the email address, file path and subject near the top of the code
  • Save your Outlook project

Now, I'm not a huge fan of this part, but... once you've closed the VBE, you need to allow Outlook to run the macro at startup. There are two options for this (I'm on 2013, but I believe it's close to the same for 2010):

Easiest/most dangerous
  • Go to File --> Options --> Trust Center --> Trust Center Settings
  • Go to Macro Settings
  • Choose Enable all macros

Be aware that this will enable any macro to run, so it's not really advisable.

Slightly harder/more secure
You'd need to:
  • Change the setting above to "Notifications for digitally signed macros, all others disabled"
  • Create a digital certificate using SelfCert
  • Sign your project with the self cert certificate
  • Install it as a trusted certificate so that it shows in Trust Center --> Trusted Publishers

This would mean that your macro would run, but others wouldn't without telling you.

If you want to go the latter route, let me know and I'll see if I can find you a link on how to create a SelfCert certificate.
 
Hi,

Thanks for your help. I am just wondering how can i check whether the above code is working or not. I am just getting confused in the following steps,
1. When will i receive email?
2. If the file is not opened for few days will i get email?

thanks
 
Or

Code: 
Private Sub Application_Startup()
   If Date - FileDateTime("G:\adress.xlsx") > 7 Then
    With CreateItem(0)
        .To = "xxx@yyy.com"
        .Subject = "G:\adress.xlsx has not been modified"
        .Send
    End With
  End If
End Sub

I don't think your confusion will melt away, because the confusion is in the reasoning.
The assumption that Outlook will be opened regularly is crucial. If it's not the whole procedure fails.
 
Thanks a lot for your reply.
Shall i check the above code by changing it to 5 min. That is if the excel is not opened for 5 min then i should get email.
Please check whetehr the following code is right.

Thanks

Private Sub Application_Startup()
If Now() - FileDateTime("G:\adress.xlsx") > 5 Then
With CreateItem(0)
.To = "xxx@yyy.com"
.Subject = "G:\adress.xlsx has not been modified"
.Send
End With
End If
End Sub
 
Please, use code tags !!

Code: 
Private Sub Application_Startup()
   If datediff("n", FileDateTime("G:\adress.xlsx"),now) > 5 Then
      With CreateItem(0)
         .To = "xxx@yyy.com"
         .Subject = "G:\adress.xlsx has not been modified"
         .Send
      End With
   End If
End Sub
 
I don't think you know what you are asking ...
 
You must log in or register to reply here.
Back
Top