Challenge!

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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!
 

Attachments

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.
 

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?
 

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.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,639
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.
 

Attachments

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,639
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. :)
 

Attachments

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?
 

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?
 

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?
 

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.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,639
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?
 

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.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,639
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.
 

Attachments

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Top