Page 1 of 2 1 2 LastLast
Results 1 to 10 of 17

Thread: vb

  1. #1

    vb



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,267
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    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

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,267
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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?
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Yea it's outlook
    Computer will not be on all the time
    Thanks

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,267
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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?
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  7. #7
    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

  8. #8
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,267
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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?
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  9. #9
    That's what I am looking at
    Brilliant

  10. #10
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,267
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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 = "someone@somedomain.com"
    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.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •