1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Script to check column of excel and send email.

Discussion in 'Business Applications' started by karanik, Oct 20, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. karanik

    karanik Thread Starter

    Joined:
    Oct 20, 2012
    Messages:
    8
    Hello ,

    I have one excel file with one column with expires date.
    I want script or something else to check every day this excel and if one cell is small than 10 then
    run a batch file (i have it) with blat emailer (command line emailer) to send me email.
    Is it possible ?
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,507
    First Name:
    Hans
    Hi welcome to the forum,

    Yes it can you can call your batch file using VBA ahen the consition is there.
    You could record a macro for this.

    The macro checks you columns anf the value is that what you need call the BlatMail

    The blatt macro
    Code:
    Function BlatMail()
        Dim myShell As Object
        Set myShell = CreateObject("WScript.Shell")
        On Error Resume Next
        myShell.Run <complete path to where the batch file is>  & Application.PathSeparator & < batchfile name> 
        On Error GoTo 0
        Set myShell = Nothing
    End Function
    
     
  3. karanik

    karanik Thread Starter

    Joined:
    Oct 20, 2012
    Messages:
    8
    tnx for fast answer
    but in this code i think miss the operation open excel file and check this column or cell from here to here.

    sorry for my English.. i try to write.
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,507
    First Name:
    Hans
    Of courso you miss the open Excel code, that's not possible inside Excel.
    You will have to use a scheduler and write an Auto_Open macro that does the work in Excel.
     
  5. karanik

    karanik Thread Starter

    Joined:
    Oct 20, 2012
    Messages:
    8
  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,507
    First Name:
    Hans
    A macro, any knowledge of macro's? Do you know what macros are?
    Please tell us what version of Excel you're using too.
     
  7. karanik

    karanik Thread Starter

    Joined:
    Oct 20, 2012
    Messages:
    8
    No i don't know how to use macros. My version of office is 2010 (desktop) & 2003 (laptop)
     
  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,507
    First Name:
    Hans
    Of course we can write things for you but with 0 knowledge of macro's you'll always have to denpend on somebody else.
    Can you post a sample with some non private data.
    I suggest a macro that is not specific for 2010 or 2003 so that it wil always work.
    The scheduling etc is your task.
     
  9. karanik

    karanik Thread Starter

    Joined:
    Oct 20, 2012
    Messages:
    8
    can you send me example ?
     
  10. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,507
    First Name:
    Hans
    You are the one asking, you are the one that has a sample / example. attach it and I'll be able to help.

    I'm have no idea what your file or data looks like.

    I'm sure you understand that part :)
     
  11. karanik

    karanik Thread Starter

    Joined:
    Oct 20, 2012
    Messages:
    8
  12. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,507
    First Name:
    Hans
    I'll look tomorrow. Looks okay.
    You could also just attach it to the post you don'r need rapid share and so.
     
  13. karanik

    karanik Thread Starter

    Joined:
    Oct 20, 2012
    Messages:
    8
    ok tnx
     
  14. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,507
    First Name:
    Hans
  15. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,507
    First Name:
    Hans
    Here is a simple code using the Auto_Open macro

    You will have to change it as the Msgbox tells you.

    The VBA code must be changed to run your batch file and you will have to use the Windows Scheduler to open the Excel file at the times you want it to open.

    If you need to you can select SaveAS and excel 95-2003 type file and it will work with all versions of Excel

    I suggest some basic VBA knowledge will be needed so you still have your homework to do.;)
     

    Attached Files:

  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1073448

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice