Hey guys/gals,
Can this be done? I have a spreadsheet with a suspense date in column F. What I want to do is when the date in F is 30 days from the current date, I want Excel to send a generic email (Not the sheet itself) saying "Hey Bonehead! You are about to blow your suspense date!"
Can Excel automatically do this?
Thanks in advance.
Hi,
Yes ... it can be done ...
http://www.j-walk.com/ss/excel/tips/tip86.htm
Okay... I see that it can be done, but I don't understand how to format this. It's a little above my head. Let me make sure I am explaining this right...
In the book, on the sheet PFW, cell F:10 is what I want to focus on for my example.
I write a report on SSgt Guppy that is due on the date in cell F:10. When F:10 gets whitin 30 days, an email is generated that says "Hey Bonehead, you are about to blow your suspense." and sent to the person in cell C:10. The email for the person in C:10 is housed at I:4 on Sheet "List". Excel needs to pull the email address from Sheet "List". I don't need it to send unless the date is within 30 days.
I am attaching a sampling of my spreadsheet.
I hope I have explained this enough. I appreciate any help I can get on this.
Can anyone help me get this coding figured out? I really appreciate the help.
This might help with the sending part
http://www.rondebruin.nl/mail/change.htm
VBA Noob
_________________________________________
Credo Elvem ipsum etian vivere
_________________________________________
A message for cross posters
Please remember to wrap code.
Forum Rules
Please add to your signature if you found this link helpful. Excel links !!!
Hello,
Well, I finally got Outlook to send an email. I still have a few problems:
- The macro that resides on the PFW Sheet, the formula has to be wrong. It's sending an email, even if the date is 100 days away. It wouldn't work with < (today+31).
- The macro PFWEmail has to have a dedicated email address to it. What would my formula be to pull the email from Sheet 'List' column I that matches the person in column C for the person. (That was confusing, but for example, Guppy (Row 10 on sheet 'PFW') when column F is less than 31 days away, Solomon would get an email. I am sure the macro would have to do either a lookup or match function, right?
- Finally, how do I tell the person in the body of the email who the PFW is due on? In the PFWEmail macro, in the body is XXXXXXX. I would like this to change to the name of the person the email is referencing.
Any help is greatly appreciated. (File attached for reference)
Hi,
Let's tackled one issue after the other ...
What is it that should trigger the event macro and run the email macro ...?
Hey Carim...
The data in column F is what will be triggering the macro. But, another issue that I found is that I only want it to send 1 email, not one each time the sheet is reopened.
Chris
Hi,
Got it ... Column F is basically the Target which is changed manually ...
What is it, compared to Column H which is always 6 months later, that will trigger the email ... ?
Vice versa... Column H changes column F which triggers the macro. I manually change H. Formula in column F updates the date to 6 months from the date I put in col H. When col F hits 45 days out, I'd like the macro to fire off the email to the person listed in col C. The email for the person in col C can be found on the Sheet "List" col I.
By the way, Col H is the date the last evaluation was conducted, and Col F is the date the next evaluation is due.
Ok ...
compared to what ... to today()'s date ... ?When col F hits 45 days out
are you using this worksheet every single day ?
By the way, do we agree there is no vice versa between column F and H ...
Sorry, yes, the sheet gets opened at least once per day. So, when Col F is = or less than 45 days, and an email has not been sent out yet, it should fire the macro to send the person a reminder.
As far as F & H, they cannot be used interchangeably. I was just stating that I manually enter data in Col H, and a formula adjusts the data in Col F.
Event macro fires fine, but will it refire when the date changes tomorrow? Like, if I reopen the document tomorrow, will it try to send an email on the same person? Additionally, if I just open this, will it not do an initial search to see if anyone needs an email and fire teh event macro? I mean, I can put a button that will run the macro if we need to for this application.
Hi again,
It will not fire again the following day ... since there is already a date "Sent" located in column N which prevents the email macro from runing twice ...
I'm an idiot sometimes! Teach me to question an excel master! I need to learn to scroll around a little before I make statements!
Well .. it does happen to all of us ... not to fully test things ....
By the way, is your email macro itself OK or not ...?
It seems to happen to me more than others sometimes!
No, it's broken too. I mean, it will email with Outlook like I want it, but what it won't do is look up the email on the list sheet for the person in Col C and email it to them. If you look in the Email Macro, you'll see that I had to set it to a single email (my work email right now) so I could test the emailing of the letter. Additionally, I need to figure out how to get the name from Col A into the body of the email where the XXXXX is at.
One question about the theory of this, what puts the date in the date sent. I looked through all the macros and couldn't find anyhting that had anything to do with Col N.
Hi,
In the event macro ... the following instruction ...
Please Login or Register to view this content.
Hi again ...
To send personalised email ...
http://www.j-walk.com/ss/excel/tips/tip86.htm
Corrected Worksheet ...
Carim,
Sorry it took so long to get back to this thread... I am battling the flu. Anyway, was there supposed to be something different from the first & last file you uploaded? They seem to be the same to me. I have seen that site before, that's where I got the idea to put the name in the body of the email, but like I said, I just can't get my brain to figure it out. Please advise about the file.
Thanks again!
Chris
Hi Chris,
No difference ... file was just cleaned up ...
Have you solved your email problem ...? or what is your current obstacle ...?
I haven't been in the capacity to work on it. This flu has wiped me out. I am maybe awake for 2 hours a day right now! I am starting to feel better, but not in the mindset to work on excel right now. I will probably try to work on it a little tomorrow (Tuesday). Thanks for the update.
Okay Carim,
Here is my file... I have 2 problems that I have found:
1 - When I open the file for the first time each day, I want it to send an email to anyone due if they haven't already been sent one. Is there a way to fire the macro to check for due dates upon opening?
2 - When someone comes due, it sends to everyone... weather they due or not. This equates to everytime I change someone's data, I have to weed through 10+ emails to find the one I need to actually send.
In the attched file, there is a word mispelled in the email on purpose to prevent it from automatically sending the emails. I'm lost, and don't know where to go next.
Any ideas?
Chris
Hi Chris,
1. If I am not mistaken, this issue is already solved with the Private Sub Worksheet_Change event macro in sheet PFW ...
2. Regarding the Email macro, why do you have a loop which sends emails to everyone ... I have just removed it ...
Actually, I didn't realize I had a loop in it. That solved that issue. As for it automatically checking as soon as the sheet is opened, no, it doesn't. I changed the date on one for the Date Conducted (Col H) to 10 Oct 06. This would make Col F change to 10 Apr 07. I then saved & closed the sheet. I adjusted the computer date to 28 Feb 07... which is within the window to cause an email to be sent. I reopened the sheet, but it didn't send. It did show that he was within the 45 day window though. I think I need a statement like this:
Private Sub Workbook_Open()
Run "MyMacro"
End Sub
but I don't know what that would be called, "Worksheet_Change"? Do I need the "Run" statement since I am trying to run a Private Sub Function?
Last edited by kingsolo; 02-21-2007 at 11:10 PM.
Hi,
Have replaced worksheet change by worksheet activate event ...
Hey Carim,
Okay, that doesn't work for the simple reason it doesn't activate the row. Everything is based off the row... as I found out last night. Last night, I changed the date to where it would fire off the macro to show my boss, and pressed the down arrow key. This caused the date to go to the right place, but it sent the email to the person who's row was active... since I pressed the down arrow, it was the person in the rate column directly below who I wanted it to go to. It was creating a false report basically. With this one, if you delete the dates in Col H, and make cell H6 4 sep 06, it will put that person in the window. Next, make cell H7 active, save & close the file. When you reopen the file, cell H7 is still active with the sheet PFW active. If you click List Sheet, then back to PFW sheet, it will generate an email to TSgt Elephant on TSgt Solomon. It should generate the email to SSgt Lion on TSgt Elephant.
I apologize for this taking so long to get figured out.
Thanks immensely for all your help.
I am a bit with your explanations ...
Have you done all these tests with version 3 ...?
Yeah, I just did the test on v.3. I have attached the book again to show you what happens. It's sending to the person who's cell is active. In this case that is already set up in the book, it should be SSgt Lion getting an email saying that TSgt Elephant is due for a report. But, what happens is TSgt Elephant gets an email syaing TSgt Solomon is due for a report because the active cell is H7, not anything on row 6. If cell A1 was selected when the sheet opens, the email would come up with none of the things that the macro should fill in. Hopefully you'll be able to see what I am saying with the workbook set up already. When you open the file, click to tab List, then back to tab PFW to activate the sheet. I intentionally misspelled a word so Outlook wouldn't send the email and you can see what I am talking about.
Let me know what you get.
Chris
Hi,
There is no apparent reason for this confusion of lines to exist ...
Try this amended version ...
Carim,
Same thing. You had the active cell set at B6, which would send the email to the proper person. I deleted the date, set clicked on B7, and clicked off the sheet then back on the sheet to re-activate the macro, and it wanted to send an email to the people on row 7... not row 6 like it should be. It put the date on row 6, but used the wrong data set. Logically thinking, it has to be something in the PFWEmail macro, not the worksheet_activate event coding.
Chris
Hi,
It is quite mysterious, since attached file works fine at my end ...
Yes, mysterious indeed. I just don't see what's different between my 3 different computers that I have tried it on and yours. I wonder if it's this...
(From Module1 - PFWEmail Macro)
Sub PFWEmail()
Dim Email As String, Subj As String
Dim Msg As String, URL As String
Dim r As Integer, x As Double
r = ActiveCell.Row
' Get the email address
Email = Cells(r, 13)
Couldn't this be my problem? See, what is happening is when it fires the macro, it takes the data from whatever row has the active cell on it. So, if row 4 is the one that is needing the email sent, but the active cell is in row 22, it will use the data from row 22, not row 4 like it should.
I know my explanations have been a little confusing. I am still rather new at Excel, but I am learning.
Hopefully this will shed some light on this problem.
As always, thanks for your outstanding assistance!
Chris
Chris,
Let me quickly recap the logic which is built in the event macro ...
When you activate the second worksheet ...PFW, a loop checks each date in column F, starting on row 6, against the today's date located in cell A4 ...
which triggers you email macro ...
Therefore r = ActiveCell.Row is the right instruction for your email macro ...
It seems to me there is may be a misunderstanding on how to use the event macro ... which is now 100% automatic ... with no user intervention ...
Let me know if this clarifies the issue for you ...
Okay, I understand the logic, but what is not happening, at least on my end, is the active cell is being see as wherever the highlight box is at on the sheet. If cell A1 was the last cell selected before you closed the sheet, then you reopen the sheet, A1 is still active, and when the sheet activates, it sees only cell A1 as being active.
So, let me walk you through my steps using the latest version of the file here.
I open the file and make sure there is no date in cell N6.
I click on cell H7 and type 1 Jan 07.
I close & save the file.
When I reopen, I click on List sheet, then back on PFW sheet.
What exactly happens on your end? Who is the email sent to? In the body of the email, what is the name & date?
In mine, it's sent to TSgt Elephant. The name & date in the body of the email are TSgt Christopher Solomon (XS) & 1 Jul 07. This is the wrong data. It should be sent to SSgt Lion, with TSgt Elephant & 4 Mar 07 in the body.
I apologize tremendously for this not working as easy as it seemed like it would, and again, I thank you for your help.
Hi Chris,
Thanks for the step-by-step explanation ...
Think I now understand what was happening at your end ...
Version 4 (with a tiny change) should solve it all ...
Okay, that worked, but it only works for the first entry.
On version 4, do the following:
In cell H10 put the date 5-sep 06Click on cell D12
Click on the List Sheet
Click back to PFW Sheet
You should get 2 emails now:
1st to Lion about Elephant
2nd to Solomon about Guppy
What I am getting is 2 emails to Lion about Elephant, but the date is going in the right squares. The active cell is basically sticking on cell F6. To see this do the following:
In H6 put the date as 1 Jan 07
In cell H7 put the date as 4 Sep 06
In cell H 10 put the date 5 Sep 06
Delete any dates in col N
Click List Sheet
Click PFW Sheet
Thanks again for your help.
Last edited by kingsolo; 02-24-2007 at 11:16 AM.
see attached ...
THANK YOU!
Tell me where you live so I can send you a gift certificate to the beer store... you deserve it after putting up with me!
Okay, now that it's all working proper, can you tell me the theory behind the functions (If it's not too much trouble) so I can understand this stuff & turn all this into a real learning experience?
Once again, thanks!
I am quite glad you finally reached your solution ...
Thanks a lot for your King size THANKS ...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks