Really need your assistance ..
Attached is example for my problem.
I only know how to set the parameter for the 'status' (using condiitonal formatting), but i dont know how to create the code in VBA to create emails on certain condition .. I'm not used to VBA, and still learn about it (my head was spinning lately).
thanks for the sample.
i'm still new with VBA, but i think i understood a little bit about your code. i have tried adjust some cell reference but the macro still not triggered (maybe i made an error)
and actually there's some differences with my problem. in my case, the email should display items that met certain condition (can display more than 1 agent) ..
Hi have a question.
I assume that under PIC will be the mail addresses fo the receivers.
What if for the same receiver there are more than one agent, different emails of name all the agents?
Another one, what do the red cells stand for are those the ones that already have been mailed?
yes, that's correct. Under 'PIC' will be the mail addresses for receiver.
if agent A or B or C is in 3 days period before maturity date, then an email will be sent to xxx.
Let's say for agent C and P. their maturity date are 22 Feb 2012 and 21 Feb 2012. Today is 21 Feb 2012. So, today there are two emails will be sent, 1 for xxx and 1 for ppp.
But let's see yesterday case. Yesterday is 20 Feb 2012. So there are also two emails will be sent.
1 will be sent to xxx, that explain there will be 2 agents (B and C) who has entered the 3 days period before maturity date ==> this is the main cause i've been dizzy whole day. an email could display more than 1 agent.
1 will be sent to ppp, that explain there will be 1 agent (agent I) who has entered the 3 days period before maturity date.
Since the notification is 3 days before maturity date, then three emails will be sent to receiver (repeat 3 times)
the red cells stand for those that already reach their maturity date ( if today > maturity date then cell become red, but if today = maturity date then cell still green).
okay, understood,
About the automatic sending you should check for an application named ClickYes, if "presses" YES for you when a mail needs to be sent without human iteraction
Themacro now prompts if you want to mail xxx a message etc.
The code for ClickYes is present but you will need ClickYes itsself before it works. The VBA code needs changing once that is present.
But that's later.
You have a working sample to test.
Macro to use MaturityNotification() and the shortcut key to this macro is Ctrl + m
i think it's ok if we dont use the ClickYes for automatic reply for now.
But when i press the ctrl + m, the box will display email notification about agent A, D, E, H.
Both of them have already terminated, because their maturity date was 14, 12, 1, 17 Feb, so we dont need to send the email.
The email is needed for agent who still active (not terminated yet) but reaching their maturity date (3 days before maturity date).
So, when i see your code, seems the rng.Value need to be redefined, considering the maturity date (for agent who still active) and also the current day. I dont know how to write the syntax in VB, but if in excel, i think the formula should be like this : if ( 0 <= maturity date - today ( ) <= 3 ) then "we can use ctrl + m to send email" else "ctrl + m is disabled".
sorry if this matter took your time ... but i also tried to modified the code (still got error occured ... )
Who colours the cells red?
Do you use conditional formatting?
You can use this column based upon the colouring to send yes or no.
Is this an option?
Who decides the maturity?
What if no mail was sent?
Great !
From your suggestion I've tried to modify the code and i got what i want.
and i added new column and new coloring method because i think it is needed so people who use the spreadsheet could understand easier about the code.
Thank you for all of your support. :up: you're really a great person.
and now i need to search about the "ClickYes" application for automation.
Ah, I forgot to ask 1 thing.
in the file i attached before, if i press ctrl + m, for Mr xxx, he will receive 2 emails. first email about agent B, and second about agent C.
So, actually, is there any way, so Mr xxx only receive 1 email, that will display like this :
Herewith list of agents that almost reach their maturity date :
Agent Maturity Date
B 2/22/2012
C 2/24/2012
So I think, in the VB code, there should be an iteration or looping (i dont know if this is the correct term) for mTxt (IF function for mTxt ? I dont know also ..)
correct, a loop is waht you need to build the message test and then mail.
I thought that was a sensible idea when I read your post but you chose to ignore my question about (post #5), that's why I did not do it.
Need it? Or have you been able to work it out?
I checked the sheet you attached earlier, I don't see any provisions in the VBA code for the values in Clolumn H
Do you need something doen with that, like Ignore if the Need to Send Notification is No?
Is the limit 500 rows?
Let me know if you need an extra if statement for column H?
actually it's not that i ignored you, but i've tried to give you an example :
'1 will be sent to xxx, that explain there will be 2 agents (B and C) who has entered the 3 days period before maturity date ==> this is the main cause i've been dizzy whole day. an email could display more than 1 agent.' => this mean for 1 receiver ( for 1 email ) there are more than 1 agent. Sorry if i made you think that i ignored you.
For column H i think its ok if we keep it that way. Using excel formula is enough, so we dont need a VBA code for column H.
I changed the limit into 500 rows, because when i try added new data (new row), i couldnt triggered the macro. So when i looked into the code, i saw your explanation about the ws.Range, that is 'using Row 15 as bottom row'. So i modified the code into 500 rows, so i can add new data.
So the remaining problem is how to display the list of agent (could be more than 1 agent, depend on the maturity date and running period) in 1 email like this :
Herewith list of agents that almost reach their maturity date :
Agent Maturity Date
B 2/22/2012
C 2/24/2012
I'm still unable to work it out ...i've read many thread, but all of them explaining how to create email by using macro without looping code for mTxt. Yes, i need it, because i still dont know how to write the syntax in VBA.
When i try to understand your code, the code was set so for each data in column C that met the condition (Date), an email will be sent to a receiver. But for this remaining problem, before the email will be sent, it need to check the whole data whether there are agent for same receiver that met the condition also. So i'm guessing that the code should to be set up for each data in column E (column PIC). So, for each PIC, the loop will occur to check agent(s) (under that PIC) that met the Date condition.
This is the idea to display more than 1 agent for same receiver. What do you think? But i dont know how to write the syntax. Could you please help me with this? Because I'm still learning about VBA. thanks for your assistance. It's been a great help.
Yes, if the notifications is no, it's no need to send the email.
on post 9, you attached the file with this code :
If rng.Value <= (Date - 3) And rng.Offset(0, 3) = False Then
But this code didnt meet the right condition.
So on post 13, i change the code :
If rng.Value >= Date And rng.Value <= (Date + 3) And rng.Offset(0, 3) = False Then
And that code is the same with the excel formula for column H :
IF(AND(D4="Active";C4-TODAY()<=3);"Yes";"No")
while the formula for column D :
IF(C4>=TODAY();"Active";"Inactive")
When i press ctrl + m, the message box will only display notification for column H with 'Yes' value. When all of the value of column H are "No", then nothing happen when we press ctrl + m. And I'm okay with that.
Are you saying that it is better if all of value in column H are "No" then a message box will appear ?
This code did meet the right condition, because once you sent the mail the value would be TRUE and therefore if you ran the macro again (by accident) this mail would not be resent unless you set the value manually bakc to false or empty
Read my post
i think for your post # 20, i dont need to answer it because you gave me what i need ! great ! it's really amazing !
thanks for all of your assistance ! it's been a great help.
you're welcom, don't forget the 'Mark Solved" buttton
Status
Not open for further replies.
You have insufficient privileges to reply here.
Related Threads
?
?
?
?
?
Tech Support Guy
9.9M posts
859.7K members
Since 1998
A forum community dedicated to tech experts and enthusiasts. Come join the discussion about articles, computer security, Mac, Microsoft, Linux, hardware, networking, gaming, reviews, accessories, and more!