none
Powershell and Excel RRS feed

  • Question

  • Hey everyone! I was wondering if I could get a hand with something.

    I work in a school district as the helpdesk technician. Our teachers are now required to update clearances every three years. One of our users is in charge of keeping track of that SO, I created an excel sheet that the user would input the dates the current subscription are active and it would calculate how many days are left until the clearances have expired. She is also responsible to remind the teachers when they have a certain amount of time left.

    I'd like to know if there is a way to create a powershell script that runs daily to check the excel doc and automatically email the teachers that fall within a certain range of dates (1 year, 90 days and 30 days). I'll probably use Windows Task Manger to ensure that the script runs on a schedule. I've done a little work in PowerShell but only to make a script that calls other .exe or .msi. Please be gentle, I'm fairly new at this.

    The tools I have available are
    Our email is hosted through google BUT we have an application that allows gmail to sync with Outlook
    The current OS is Windows 7 64 bit

    Tuesday, June 16, 2015 7:15 PM

Answers

  • Hi BAVTS,

    >>She is also responsible to remind the teachers when they have a certain amount of time left.

    I'd like to know if there is a way to create a powershell script that runs daily to check the excel doc and automatically email the teachers that fall within a certain range of dates (1 year, 90 days and 30 days). I'll probably use Windows Task Manger to ensure that the script runs on a schedule<<

    We can use Excel object model to manipulate the spreadsheet. For example, we can get the value in A1 with code below:

    Range("A1")
    

    And you can get more detail about hadling cells, ranges and workbboks from link below:
    How Do I... with Cells and Ranges

    How Do I... with Workbooks and Worksheets

    Then we can based on the value we get from spreadsheet to send the email. To send email we can automate the Outlook application and here is an helpful article about it:
    Using Automation to Send a Microsoft Outlook Message

    And here is an helpful blog about handling Excel using PowerShell:
    Excel with PowerShell

    If you have problem developing with PowerShell, I would suggest that you reopen a new thread in PowerShell forum.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, June 19, 2015 2:53 AM
    Moderator

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Office, I'll move your question to the MSDN forum for Powershell

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=officegeneral&filter=alltypes&sort=lastpostdesc

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    George Zhao
    TechNet Community Support


    It's recommended to download and install Configuration Analyzer Tool (OffCAT), which is developed by Microsoft Support teams. Once the tool is installed, you can run it at any time to scan for hundreds of known issues in Office programs.

    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.
    Wednesday, June 17, 2015 6:23 AM
  • AH! I didn't look hard enough and missed that as an option. Thanks for re-posting it!
    Thursday, June 18, 2015 1:52 AM
  • Hi BAVTS,

    >>She is also responsible to remind the teachers when they have a certain amount of time left.

    I'd like to know if there is a way to create a powershell script that runs daily to check the excel doc and automatically email the teachers that fall within a certain range of dates (1 year, 90 days and 30 days). I'll probably use Windows Task Manger to ensure that the script runs on a schedule<<

    We can use Excel object model to manipulate the spreadsheet. For example, we can get the value in A1 with code below:

    Range("A1")
    

    And you can get more detail about hadling cells, ranges and workbboks from link below:
    How Do I... with Cells and Ranges

    How Do I... with Workbooks and Worksheets

    Then we can based on the value we get from spreadsheet to send the email. To send email we can automate the Outlook application and here is an helpful article about it:
    Using Automation to Send a Microsoft Outlook Message

    And here is an helpful blog about handling Excel using PowerShell:
    Excel with PowerShell

    If you have problem developing with PowerShell, I would suggest that you reopen a new thread in PowerShell forum.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, June 19, 2015 2:53 AM
    Moderator