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.

Challenge!

Discussion in 'Business Applications' started by jcwagner, Oct 31, 2011.

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

    jcwagner Thread Starter

    Joined:
    Jul 26, 2011
    Messages:
    25
    Hey,

    In the spread sheet attached, there are multiple programs that certain people run, and dates in which they must take place. There are dates spread though out the spread sheet. This is what I want it to do:

    1. Create a search system that recognizes the dates for certain programs that will occur in the upcoming working week.

    2. When the system recognizes if certain programs will occur through out the following monday-friday, it will copy all of the programs along with the entire row of information linked to the program to sheet 2. The entire row for the program should be copied. This can be with a press of a button if it cannot be automated.

    (I will open the spread sheet on the thursday before the upcoming work week and hit a button that will copy all of the info to sheet 2.)

    THEN,

    Once all of the programs that will have a review for that upcomming week are copied to the new sheet2, I would like to run another macro that will automatically send an email (using our default email OUTLOOK) to each person that is involved with that copied sheet 2 along with a message saying "Reminder: You have an upcoming review. Please check attached chart"

    Could someone help me with this? I have researched other forums on the reminder system but this is a little different and more specific to my project. I will be more than willing to donate if someone can help me. Thanks!
     

    Attached Files:

  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    This would of course be much better and easier in Access.
    Your Spreadsheet does not have any upcomming review dates anywhere near now to test with.
    It also does not have any email addresses for the second part of the macro.
     
  3. jcwagner

    jcwagner Thread Starter

    Joined:
    Jul 26, 2011
    Messages:
    25
    Thanks for your reply. I just posted the template for the actual spread sheet. I figured who ever could help me could just plug in random dates to test. They could also just use made up email addresses. Can this be done in excel though?
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Yes it can be done in Excel, I have done similar things to the first part on this Forum.
    I have not used Excel as an emailing trigger though, but lots of other Excel gurus have posted on here on how to do it.
    I can't post anything today as I have already been on the Forum over 8 hours, but I will try and post the first part tomorrow.
     
  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Hi, jcwagner and OBP,
    Just a suggestion, to make it all work you will need an extra sheet that holds the person's name or team name with the corresponding email address in the column next to it. (see editted attachment)
    Then you have a link for the email message, and yes, I agree with OBP that access would be the most efficient application. but Excel will do too.
    I'm just butting in but will not be available for the next 2 1/2 weeks.
     

    Attached Files:

  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Here is the first part based on the Workbook posted by Hans.
     

    Attached Files:

  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I updated the file with a Function name MailData() that will require some parameters and can be used to send a messag via Outlook.

    The default is open Outlook and the user presses Send or you can send directly but have to confirm and the last case you need ClickYes and the corresponding module to automatically click yes for you.
    Teh data to use to mail has to be completed to use the MailData() function.

    we are nearer to completion. :)
     

    Attached Files:

  8. jcwagner

    jcwagner Thread Starter

    Joined:
    Jul 26, 2011
    Messages:
    25
    This looks great so far! How would I be able to test this out so far?
     
  9. jcwagner

    jcwagner Thread Starter

    Joined:
    Jul 26, 2011
    Messages:
    25
    Just kidding I figured it out...now is it already programmed to detect the dates for the monday-friday when I run the macro on thursday the week before?
     
  10. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Yes press Ctrl & a.
     
  11. jcwagner

    jcwagner Thread Starter

    Joined:
    Jul 26, 2011
    Messages:
    25
    Sweet! Now, how can we make it so that we can send the emails from sheet 2 to the names listed down the row of the program? Also, since their are multiple names in one cell, is it possible to send the email to all names?
     
  12. jcwagner

    jcwagner Thread Starter

    Joined:
    Jul 26, 2011
    Messages:
    25
    Also, when the email is sent out to those recipients, will it only include sheet 2? or all sheets? I would like to just have sheet 2 send to them.
     
  13. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Well I think only sheet 2, but you will have to include the email addresses so that these van be used.
    Mail it to all the names in the cell, right?
     
  14. jcwagner

    jcwagner Thread Starter

    Joined:
    Jul 26, 2011
    Messages:
    25
    Shoudl I just make up emails for now. I saw a post saying I would get alot of junk mail if I used the real ones. And Yes, mail sheet two as an attachment for just that sheet to all name slisted for that program. The column named "Review Team" will have mulpiple name sin one cell.
     
  15. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Here's an example how it works, fake emails to test is okay,
    The final step has to be doen still but it's a beginnen.

    Run the macro OBP made and the run the macro CollectandMail just to see

    The names in column G must only be the name and separated by a , and space, not extra info like (Struc Only)

    Re-attached sheet, was incomplete.
     

    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!

Thread Status:
Not open for further replies.

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

  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