| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Email on a condition
Hi, hopefully someone can help me, i have searched the forums but not sound
anything that fits. I have a spread sheet which has a problem ref, prob description, prob owner, prob next update date fields I review the problems each morning with the owners and update the spreadsheet then send it out. what i would like to do is on the day before the problem next update date, send the owner a mail notifying him that he needs to attend the meeting the following morning and he will need to provide and update. Is this possible? Rgds Paul |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Email on a condition
Hi phocused
You can add a formula that display yes in a column if it is one day before the problem next update date Date column = for example B =IF(TODAY()=B1-1,"yes","no") and copy down Then if you run this macro every day it will create the mails for you http://www.rondebruin.nl/mail/folder3/message.htm post back if you have problems -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "phocused" wrote in message ... Hi, hopefully someone can help me, i have searched the forums but not sound anything that fits. I have a spread sheet which has a problem ref, prob description, prob owner, prob next update date fields I review the problems each morning with the owners and update the spreadsheet then send it out. what i would like to do is on the day before the problem next update date, send the owner a mail notifying him that he needs to attend the meeting the following morning and he will need to provide and update. Is this possible? Rgds Paul |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Email on a condition
Ron,
Thanks for this, it worked like a dream. I have a further question. Is there a way to make it run once only ? Make it send the mails when the condition is yes. Reset the 'Yes' to 'No' and sned a copy of itself (the whole workbook) to another list of addresses. Let me explain further. At the moment the trigger is for me to manually run the macro. I would like this to work dynamically if possible. If it is dynamic then the issue here would be that I then send the report out to senior management and i dont want the macro running for every incident of the report opening. Does that make any sense :-) rgds Paul and thanks again for your help "Ron de Bruin" wrote: Hi phocused You can add a formula that display yes in a column if it is one day before the problem next update date Date column = for example B =IF(TODAY()=B1-1,"yes","no") and copy down Then if you run this macro every day it will create the mails for you http://www.rondebruin.nl/mail/folder3/message.htm post back if you have problems -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "phocused" wrote in message ... Hi, hopefully someone can help me, i have searched the forums but not sound anything that fits. I have a spread sheet which has a problem ref, prob description, prob owner, prob next update date fields I review the problems each morning with the owners and update the spreadsheet then send it out. what i would like to do is on the day before the problem next update date, send the owner a mail notifying him that he needs to attend the meeting the following morning and he will need to provide and update. Is this possible? Rgds Paul |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Email on a condition
Hi Paul
You can add for example "Send" in another column with code when you have send the mail and test this value in the macro If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) = "yes" And cell.Offset(0, 2).Value = "" Then Then before <Set OutMail = Nothing insert a value in the cell cell.Offset(0, 2).Value = "send" Set OutMail = Nothing -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "phocused" wrote in message ... Ron, Thanks for this, it worked like a dream. I have a further question. Is there a way to make it run once only ? Make it send the mails when the condition is yes. Reset the 'Yes' to 'No' and sned a copy of itself (the whole workbook) to another list of addresses. Let me explain further. At the moment the trigger is for me to manually run the macro. I would like this to work dynamically if possible. If it is dynamic then the issue here would be that I then send the report out to senior management and i dont want the macro running for every incident of the report opening. Does that make any sense :-) rgds Paul and thanks again for your help "Ron de Bruin" wrote: Hi phocused You can add a formula that display yes in a column if it is one day before the problem next update date Date column = for example B =IF(TODAY()=B1-1,"yes","no") and copy down Then if you run this macro every day it will create the mails for you http://www.rondebruin.nl/mail/folder3/message.htm post back if you have problems -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "phocused" wrote in message ... Hi, hopefully someone can help me, i have searched the forums but not sound anything that fits. I have a spread sheet which has a problem ref, prob description, prob owner, prob next update date fields I review the problems each morning with the owners and update the spreadsheet then send it out. what i would like to do is on the day before the problem next update date, send the owner a mail notifying him that he needs to attend the meeting the following morning and he will need to provide and update. Is this possible? Rgds Paul |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Email on a condition
There is also a example on the same webpage
http://www.rondebruin.nl/mail/folder3/message.htm Example 2 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi Paul You can add for example "Send" in another column with code when you have send the mail and test this value in the macro If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) = "yes" And cell.Offset(0, 2).Value = "" Then Then before <Set OutMail = Nothing insert a value in the cell cell.Offset(0, 2).Value = "send" Set OutMail = Nothing -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "phocused" wrote in message ... Ron, Thanks for this, it worked like a dream. I have a further question. Is there a way to make it run once only ? Make it send the mails when the condition is yes. Reset the 'Yes' to 'No' and sned a copy of itself (the whole workbook) to another list of addresses. Let me explain further. At the moment the trigger is for me to manually run the macro. I would like this to work dynamically if possible. If it is dynamic then the issue here would be that I then send the report out to senior management and i dont want the macro running for every incident of the report opening. Does that make any sense :-) rgds Paul and thanks again for your help "Ron de Bruin" wrote: Hi phocused You can add a formula that display yes in a column if it is one day before the problem next update date Date column = for example B =IF(TODAY()=B1-1,"yes","no") and copy down Then if you run this macro every day it will create the mails for you http://www.rondebruin.nl/mail/folder3/message.htm post back if you have problems -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "phocused" wrote in message ... Hi, hopefully someone can help me, i have searched the forums but not sound anything that fits. I have a spread sheet which has a problem ref, prob description, prob owner, prob next update date fields I review the problems each morning with the owners and update the spreadsheet then send it out. what i would like to do is on the day before the problem next update date, send the owner a mail notifying him that he needs to attend the meeting the following morning and he will need to provide and update. Is this possible? Rgds Paul |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Email on a condition
Ron,
Will try this out tomorow at work, many thanks. I hope you dont mind my picking your brain like this but i have further questions. Is it possible to have multiple conditions data to one recipient. So 1 person may be responsible for multiple updates but not all on the same day. So he may have 1 yes today and 3 no's, 3 yes's tommorow and 1 no and so on. How do i set this, do i need a matrix of some description. Also IS it possible to have the mail sent as a single mail to multiple recipients or does it have to be 1 mail per name on the list? Rgds Paul "Ron de Bruin" wrote: There is also a example on the same webpage http://www.rondebruin.nl/mail/folder3/message.htm Example 2 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi Paul You can add for example "Send" in another column with code when you have send the mail and test this value in the macro If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) = "yes" And cell.Offset(0, 2).Value = "" Then Then before <Set OutMail = Nothing insert a value in the cell cell.Offset(0, 2).Value = "send" Set OutMail = Nothing -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "phocused" wrote in message ... Ron, Thanks for this, it worked like a dream. I have a further question. Is there a way to make it run once only ? Make it send the mails when the condition is yes. Reset the 'Yes' to 'No' and sned a copy of itself (the whole workbook) to another list of addresses. Let me explain further. At the moment the trigger is for me to manually run the macro. I would like this to work dynamically if possible. If it is dynamic then the issue here would be that I then send the report out to senior management and i dont want the macro running for every incident of the report opening. Does that make any sense :-) rgds Paul and thanks again for your help "Ron de Bruin" wrote: Hi phocused You can add a formula that display yes in a column if it is one day before the problem next update date Date column = for example B =IF(TODAY()=B1-1,"yes","no") and copy down Then if you run this macro every day it will create the mails for you http://www.rondebruin.nl/mail/folder3/message.htm post back if you have problems -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "phocused" wrote in message ... Hi, hopefully someone can help me, i have searched the forums but not sound anything that fits. I have a spread sheet which has a problem ref, prob description, prob owner, prob next update date fields I review the problems each morning with the owners and update the spreadsheet then send it out. what i would like to do is on the day before the problem next update date, send the owner a mail notifying him that he needs to attend the meeting the following morning and he will need to provide and update. Is this possible? Rgds Paul |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Email on a condition
Everything is possible Paul (almost)
After you try example 2 post back -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "phocused" wrote in message ... Ron, Will try this out tomorow at work, many thanks. I hope you dont mind my picking your brain like this but i have further questions. Is it possible to have multiple conditions data to one recipient. So 1 person may be responsible for multiple updates but not all on the same day. So he may have 1 yes today and 3 no's, 3 yes's tommorow and 1 no and so on. How do i set this, do i need a matrix of some description. Also IS it possible to have the mail sent as a single mail to multiple recipients or does it have to be 1 mail per name on the list? Rgds Paul "Ron de Bruin" wrote: There is also a example on the same webpage http://www.rondebruin.nl/mail/folder3/message.htm Example 2 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi Paul You can add for example "Send" in another column with code when you have send the mail and test this value in the macro If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) = "yes" And cell.Offset(0, 2).Value = "" Then Then before <Set OutMail = Nothing insert a value in the cell cell.Offset(0, 2).Value = "send" Set OutMail = Nothing -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "phocused" wrote in message ... Ron, Thanks for this, it worked like a dream. I have a further question. Is there a way to make it run once only ? Make it send the mails when the condition is yes. Reset the 'Yes' to 'No' and sned a copy of itself (the whole workbook) to another list of addresses. Let me explain further. At the moment the trigger is for me to manually run the macro. I would like this to work dynamically if possible. If it is dynamic then the issue here would be that I then send the report out to senior management and i dont want the macro running for every incident of the report opening. Does that make any sense :-) rgds Paul and thanks again for your help "Ron de Bruin" wrote: Hi phocused You can add a formula that display yes in a column if it is one day before the problem next update date Date column = for example B =IF(TODAY()=B1-1,"yes","no") and copy down Then if you run this macro every day it will create the mails for you http://www.rondebruin.nl/mail/folder3/message.htm post back if you have problems -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "phocused" wrote in message ... Hi, hopefully someone can help me, i have searched the forums but not sound anything that fits. I have a spread sheet which has a problem ref, prob description, prob owner, prob next update date fields I review the problems each morning with the owners and update the spreadsheet then send it out. what i would like to do is on the day before the problem next update date, send the owner a mail notifying him that he needs to attend the meeting the following morning and he will need to provide and update. Is this possible? Rgds Paul |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Email on a condition
Ron,
Again workd like a dream. I have set it so the conditions are set dynamicaly as a result of the date calculation. Rgds and thank you. Paul "Ron de Bruin" wrote: There is also a example on the same webpage http://www.rondebruin.nl/mail/folder3/message.htm Example 2 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi Paul You can add for example "Send" in another column with code when you have send the mail and test this value in the macro If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) = "yes" And cell.Offset(0, 2).Value = "" Then Then before <Set OutMail = Nothing insert a value in the cell cell.Offset(0, 2).Value = "send" Set OutMail = Nothing -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "phocused" wrote in message ... Ron, Thanks for this, it worked like a dream. I have a further question. Is there a way to make it run once only ? Make it send the mails when the condition is yes. Reset the 'Yes' to 'No' and sned a copy of itself (the whole workbook) to another list of addresses. Let me explain further. At the moment the trigger is for me to manually run the macro. I would like this to work dynamically if possible. If it is dynamic then the issue here would be that I then send the report out to senior management and i dont want the macro running for every incident of the report opening. Does that make any sense :-) rgds Paul and thanks again for your help "Ron de Bruin" wrote: Hi phocused You can add a formula that display yes in a column if it is one day before the problem next update date Date column = for example B =IF(TODAY()=B1-1,"yes","no") and copy down Then if you run this macro every day it will create the mails for you http://www.rondebruin.nl/mail/folder3/message.htm post back if you have problems -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "phocused" wrote in message ... Hi, hopefully someone can help me, i have searched the forums but not sound anything that fits. I have a spread sheet which has a problem ref, prob description, prob owner, prob next update date fields I review the problems each morning with the owners and update the spreadsheet then send it out. what i would like to do is on the day before the problem next update date, send the owner a mail notifying him that he needs to attend the meeting the following morning and he will need to provide and update. Is this possible? Rgds Paul |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Email on a condition
Thanks for the feedback Paul
-- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "phocused" wrote in message ... Ron, Again workd like a dream. I have set it so the conditions are set dynamicaly as a result of the date calculation. Rgds and thank you. Paul "Ron de Bruin" wrote: There is also a example on the same webpage http://www.rondebruin.nl/mail/folder3/message.htm Example 2 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi Paul You can add for example "Send" in another column with code when you have send the mail and test this value in the macro If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) = "yes" And cell.Offset(0, 2).Value = "" Then Then before <Set OutMail = Nothing insert a value in the cell cell.Offset(0, 2).Value = "send" Set OutMail = Nothing -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "phocused" wrote in message ... Ron, Thanks for this, it worked like a dream. I have a further question. Is there a way to make it run once only ? Make it send the mails when the condition is yes. Reset the 'Yes' to 'No' and sned a copy of itself (the whole workbook) to another list of addresses. Let me explain further. At the moment the trigger is for me to manually run the macro. I would like this to work dynamically if possible. If it is dynamic then the issue here would be that I then send the report out to senior management and i dont want the macro running for every incident of the report opening. Does that make any sense :-) rgds Paul and thanks again for your help "Ron de Bruin" wrote: Hi phocused You can add a formula that display yes in a column if it is one day before the problem next update date Date column = for example B =IF(TODAY()=B1-1,"yes","no") and copy down Then if you run this macro every day it will create the mails for you http://www.rondebruin.nl/mail/folder3/message.htm post back if you have problems -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "phocused" wrote in message ... Hi, hopefully someone can help me, i have searched the forums but not sound anything that fits. I have a spread sheet which has a problem ref, prob description, prob owner, prob next update date fields I review the problems each morning with the owners and update the spreadsheet then send it out. what i would like to do is on the day before the problem next update date, send the owner a mail notifying him that he needs to attend the meeting the following morning and he will need to provide and update. Is this possible? Rgds Paul |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Email on a condition
Hi Paul
If you have time can you test this new page for me http://www.rondebruin.nl/mail/folder2/saveatt.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "phocused" wrote in message ... Ron, Again workd like a dream. I have set it so the conditions are set dynamicaly as a result of the date calculation. Rgds and thank you. Paul "Ron de Bruin" wrote: There is also a example on the same webpage http://www.rondebruin.nl/mail/folder3/message.htm Example 2 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi Paul You can add for example "Send" in another column with code when you have send the mail and test this value in the macro If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) = "yes" And cell.Offset(0, 2).Value = "" Then Then before <Set OutMail = Nothing insert a value in the cell cell.Offset(0, 2).Value = "send" Set OutMail = Nothing -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "phocused" wrote in message ... Ron, Thanks for this, it worked like a dream. I have a further question. Is there a way to make it run once only ? Make it send the mails when the condition is yes. Reset the 'Yes' to 'No' and sned a copy of itself (the whole workbook) to another list of addresses. Let me explain further. At the moment the trigger is for me to manually run the macro. I would like this to work dynamically if possible. If it is dynamic then the issue here would be that I then send the report out to senior management and i dont want the macro running for every incident of the report opening. Does that make any sense :-) rgds Paul and thanks again for your help "Ron de Bruin" wrote: Hi phocused You can add a formula that display yes in a column if it is one day before the problem next update date Date column = for example B =IF(TODAY()=B1-1,"yes","no") and copy down Then if you run this macro every day it will create the mails for you http://www.rondebruin.nl/mail/folder3/message.htm post back if you have problems -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "phocused" wrote in message ... Hi, hopefully someone can help me, i have searched the forums but not sound anything that fits. I have a spread sheet which has a problem ref, prob description, prob owner, prob next update date fields I review the problems each morning with the owners and update the spreadsheet then send it out. what i would like to do is on the day before the problem next update date, send the owner a mail notifying him that he needs to attend the meeting the following morning and he will need to provide and update. Is this possible? Rgds Paul |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Email on a condition
Ron,
Followed your instructions and was able to get it working of a sorts. I couldnt get the VBA to create the Folder on the C: drive, I had to do that manualy. Also I get a message telling me that a programme is trying to access out look and that it could be a virus, do i want to continue. I am sure this is a security level setting issue but havent been able to play yet and check. I cant get the thing to run from the spreadsheet, I have to be in the VBA editor for it to execute. I get nothing when i do <<alt F8 but then it might be me pressing the wrong key sequence. Would be nice to be able to set the arguments from Cells so you could decide what you wanted to move, where to and to which folder. So .Docs one day to a folder, .XLS to another and so on. Interesting idea though. Let me know what i am doing wrong and I will try again. Rgds Paul "Ron de Bruin" wrote: Hi Paul If you have time can you test this new page for me http://www.rondebruin.nl/mail/folder2/saveatt.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "phocused" wrote in message ... Ron, Again workd like a dream. I have set it so the conditions are set dynamicaly as a result of the date calculation. Rgds and thank you. Paul "Ron de Bruin" wrote: There is also a example on the same webpage http://www.rondebruin.nl/mail/folder3/message.htm Example 2 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi Paul You can add for example "Send" in another column with code when you have send the mail and test this value in the macro If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) = "yes" And cell.Offset(0, 2).Value = "" Then Then before <Set OutMail = Nothing insert a value in the cell cell.Offset(0, 2).Value = "send" Set OutMail = Nothing -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "phocused" wrote in message ... Ron, Thanks for this, it worked like a dream. I have a further question. Is there a way to make it run once only ? Make it send the mails when the condition is yes. Reset the 'Yes' to 'No' and sned a copy of itself (the whole workbook) to another list of addresses. Let me explain further. At the moment the trigger is for me to manually run the macro. I would like this to work dynamically if possible. If it is dynamic then the issue here would be that I then send the report out to senior management and i dont want the macro running for every incident of the report opening. Does that make any sense :-) rgds Paul and thanks again for your help "Ron de Bruin" wrote: Hi phocused You can add a formula that display yes in a column if it is one day before the problem next update date Date column = for example B =IF(TODAY()=B1-1,"yes","no") and copy down Then if you run this macro every day it will create the mails for you http://www.rondebruin.nl/mail/folder3/message.htm post back if you have problems -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "phocused" wrote in message ... Hi, hopefully someone can help me, i have searched the forums but not sound anything that fits. I have a spread sheet which has a problem ref, prob description, prob owner, prob next update date fields I review the problems each morning with the owners and update the spreadsheet then send it out. what i would like to do is on the day before the problem next update date, send the owner a mail notifying him that he needs to attend the meeting the following morning and he will need to provide and update. Is this possible? Rgds Paul |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Email on a condition
Ron,
Additional to the last update. I redid the thing from scratch and was able to trigger the event from a Button. So that part worked. I wouldnt advise anyone useing the time stamp method as it has the potential to fill your HDD with folders. SOme people may want that I suppose though :-) As I said, works a treat, would be nice to set the arguments in the spreadsheet though. Rgds Paul "Ron de Bruin" wrote: Hi Paul If you have time can you test this new page for me http://www.rondebruin.nl/mail/folder2/saveatt.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "phocused" wrote in message ... Ron, Again workd like a dream. I have set it so the conditions are set dynamicaly as a result of the date calculation. Rgds and thank you. Paul "Ron de Bruin" wrote: There is also a example on the same webpage http://www.rondebruin.nl/mail/folder3/message.htm Example 2 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi Paul You can add for example "Send" in another column with code when you have send the mail and test this value in the macro If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) = "yes" And cell.Offset(0, 2).Value = "" Then Then before <Set OutMail = Nothing insert a value in the cell cell.Offset(0, 2).Value = "send" Set OutMail = Nothing -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "phocused" wrote in message ... Ron, Thanks for this, it worked like a dream. I have a further question. Is there a way to make it run once only ? Make it send the mails when the condition is yes. Reset the 'Yes' to 'No' and sned a copy of itself (the whole workbook) to another list of addresses. Let me explain further. At the moment the trigger is for me to manually run the macro. I would like this to work dynamically if possible. If it is dynamic then the issue here would be that I then send the report out to senior management and i dont want the macro running for every incident of the report opening. Does that make any sense :-) rgds Paul and thanks again for your help "Ron de Bruin" wrote: Hi phocused You can add a formula that display yes in a column if it is one day before the problem next update date Date column = for example B =IF(TODAY()=B1-1,"yes","no") and copy down Then if you run this macro every day it will create the mails for you http://www.rondebruin.nl/mail/folder3/message.htm post back if you have problems -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "phocused" wrote in message ... Hi, hopefully someone can help me, i have searched the forums but not sound anything that fits. I have a spread sheet which has a problem ref, prob description, prob owner, prob next update date fields I review the problems each morning with the owners and update the spreadsheet then send it out. what i would like to do is on the day before the problem next update date, send the owner a mail notifying him that he needs to attend the meeting the following morning and he will need to provide and update. Is this possible? Rgds Paul |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Email on a condition
When copying and pasting an e-mail address into excel it does not appear as,
or export as, an e-mail address. However, when I type the same address in a cell it appears as and acts as an e-mail address. Is there a way I can change a group of imported cells that contain e-mail addresses into active e-mail addresses??? "Ron de Bruin" wrote: Everything is possible Paul (almost) After you try example 2 post back -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "phocused" wrote in message ... Ron, Will try this out tomorow at work, many thanks. I hope you dont mind my picking your brain like this but i have further questions. Is it possible to have multiple conditions data to one recipient. So 1 person may be responsible for multiple updates but not all on the same day. So he may have 1 yes today and 3 no's, 3 yes's tommorow and 1 no and so on. How do i set this, do i need a matrix of some description. Also IS it possible to have the mail sent as a single mail to multiple recipients or does it have to be 1 mail per name on the list? Rgds Paul "Ron de Bruin" wrote: There is also a example on the same webpage http://www.rondebruin.nl/mail/folder3/message.htm Example 2 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi Paul You can add for example "Send" in another column with code when you have send the mail and test this value in the macro If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) = "yes" And cell.Offset(0, 2).Value = "" Then Then before <Set OutMail = Nothing insert a value in the cell cell.Offset(0, 2).Value = "send" Set OutMail = Nothing -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "phocused" wrote in message ... Ron, Thanks for this, it worked like a dream. I have a further question. Is there a way to make it run once only ? Make it send the mails when the condition is yes. Reset the 'Yes' to 'No' and sned a copy of itself (the whole workbook) to another list of addresses. Let me explain further. At the moment the trigger is for me to manually run the macro. I would like this to work dynamically if possible. If it is dynamic then the issue here would be that I then send the report out to senior management and i dont want the macro running for every incident of the report opening. Does that make any sense :-) rgds Paul and thanks again for your help "Ron de Bruin" wrote: Hi phocused You can add a formula that display yes in a column if it is one day before the problem next update date Date column = for example B =IF(TODAY()=B1-1,"yes","no") and copy down Then if you run this macro every day it will create the mails for you http://www.rondebruin.nl/mail/folder3/message.htm post back if you have problems -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "phocused" wrote in message ... Hi, hopefully someone can help me, i have searched the forums but not sound anything that fits. I have a spread sheet which has a problem ref, prob description, prob owner, prob next update date fields I review the problems each morning with the owners and update the spreadsheet then send it out. what i would like to do is on the day before the problem next update date, send the owner a mail notifying him that he needs to attend the meeting the following morning and he will need to provide and update. Is this possible? Rgds Paul |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Email on a condition
You're a friggin genius, but one more question. I was doing that to import
the info into a Palm Pilot, which only recognizes .csv and .txt files. It doesn't seem that the e-mail addresses stay intact when saved as a .csv file...any further suggestions?? "Ron de Bruin" wrote: You can use a macro this example change all mail addresses to links in column B Sub test() For Each myCell In Columns("B").Cells.SpecialCells(xlCellTypeConstant s) If myCell.Value Like "?*@?*.?*" Then ActiveSheet.Hyperlinks.Add Anchor:=myCell, _ Address:="mailto:" & myCell.Value, TextToDisplay:=myCell.Value End If Next End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Jupiter Jim" <Jupiter wrote in message ... When copying and pasting an e-mail address into excel it does not appear as, or export as, an e-mail address. However, when I type the same address in a cell it appears as and acts as an e-mail address. Is there a way I can change a group of imported cells that contain e-mail addresses into active e-mail addresses??? "Ron de Bruin" wrote: Everything is possible Paul (almost) After you try example 2 post back -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "phocused" wrote in message ... Ron, Will try this out tomorow at work, many thanks. I hope you dont mind my picking your brain like this but i have further questions. Is it possible to have multiple conditions data to one recipient. So 1 person may be responsible for multiple updates but not all on the same day. So he may have 1 yes today and 3 no's, 3 yes's tommorow and 1 no and so on. How do i set this, do i need a matrix of some description. Also IS it possible to have the mail sent as a single mail to multiple recipients or does it have to be 1 mail per name on the list? Rgds Paul "Ron de Bruin" wrote: There is also a example on the same webpage http://www.rondebruin.nl/mail/folder3/message.htm Example 2 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi Paul You can add for example "Send" in another column with code when you have send the mail and test this value in the macro If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) = "yes" And cell.Offset(0, 2).Value = "" Then Then before <Set OutMail = Nothing insert a value in the cell cell.Offset(0, 2).Value = "send" Set OutMail = Nothing -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "phocused" wrote in message ... Ron, Thanks for this, it worked like a dream. I have a further question. Is there a way to make it run once only ? Make it send the mails when the condition is yes. Reset the 'Yes' to 'No' and sned a copy of itself (the whole workbook) to another list of addresses. Let me explain further. At the moment the trigger is for me to manually run the macro. I would like this to work dynamically if possible. If it is dynamic then the issue here would be that I then send the report out to senior management and i dont want the macro running for every incident of the report opening. Does that make any sense :-) rgds Paul and thanks again for your help "Ron de Bruin" wrote: Hi phocused You can add a formula that display yes in a column if it is one day before the problem next update date Date column = for example B =IF(TODAY()=B1-1,"yes","no") and copy down Then if you run this macro every day it will create the mails for you http://www.rondebruin.nl/mail/folder3/message.htm post back if you have problems -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "phocused" wrote in message ... Hi, hopefully someone can help me, i have searched the forums but not sound anything that fits. I have a spread sheet which has a problem ref, prob description, prob owner, prob next update date fields I review the problems each morning with the owners and update the spreadsheet then send it out. what i would like to do is on the day before the problem next update date, send the owner a mail notifying him that he needs to attend the meeting the following morning and he will need to provide and update. Is this possible? Rgds Paul |
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Email on a condition
Hi Jupiter Jim
A csv file is a txt file so there is no formatting. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Jupiter Jim" wrote in message ... You're a friggin genius, but one more question. I was doing that to import the info into a Palm Pilot, which only recognizes .csv and .txt files. It doesn't seem that the e-mail addresses stay intact when saved as a .csv file...any further suggestions?? "Ron de Bruin" wrote: You can use a macro this example change all mail addresses to links in column B Sub test() For Each myCell In Columns("B").Cells.SpecialCells(xlCellTypeConstant s) If myCell.Value Like "?*@?*.?*" Then ActiveSheet.Hyperlinks.Add Anchor:=myCell, _ Address:="mailto:" & myCell.Value, TextToDisplay:=myCell.Value End If Next End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Jupiter Jim" <Jupiter wrote in message ... When copying and pasting an e-mail address into excel it does not appear as, or export as, an e-mail address. However, when I type the same address in a cell it appears as and acts as an e-mail address. Is there a way I can change a group of imported cells that contain e-mail addresses into active e-mail addresses??? "Ron de Bruin" wrote: Everything is possible Paul (almost) After you try example 2 post back -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "phocused" wrote in message ... Ron, Will try this out tomorow at work, many thanks. I hope you dont mind my picking your brain like this but i have further questions. Is it possible to have multiple conditions data to one recipient. So 1 person may be responsible for multiple updates but not all on the same day. So he may have 1 yes today and 3 no's, 3 yes's tommorow and 1 no and so on. How do i set this, do i need a matrix of some description. Also IS it possible to have the mail sent as a single mail to multiple recipients or does it have to be 1 mail per name on the list? Rgds Paul "Ron de Bruin" wrote: There is also a example on the same webpage http://www.rondebruin.nl/mail/folder3/message.htm Example 2 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi Paul You can add for example "Send" in another column with code when you have send the mail and test this value in the macro If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) = "yes" And cell.Offset(0, 2).Value = "" Then Then before <Set OutMail = Nothing insert a value in the cell cell.Offset(0, 2).Value = "send" Set OutMail = Nothing -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "phocused" wrote in message ... Ron, Thanks for this, it worked like a dream. I have a further question. Is there a way to make it run once only ? Make it send the mails when the condition is yes. Reset the 'Yes' to 'No' and sned a copy of itself (the whole workbook) to another list of addresses. Let me explain further. At the moment the trigger is for me to manually run the macro. I would like this to work dynamically if possible. If it is dynamic then the issue here would be that I then send the report out to senior management and i dont want the macro running for every incident of the report opening. Does that make any sense :-) rgds Paul and thanks again for your help "Ron de Bruin" wrote: Hi phocused You can add a formula that display yes in a column if it is one day before the problem next update date Date column = for example B =IF(TODAY()=B1-1,"yes","no") and copy down Then if you run this macro every day it will create the mails for you http://www.rondebruin.nl/mail/folder3/message.htm post back if you have problems -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "phocused" wrote in message ... Hi, hopefully someone can help me, i have searched the forums but not sound anything that fits. I have a spread sheet which has a problem ref, prob description, prob owner, prob next update date fields I review the problems each morning with the owners and update the spreadsheet then send it out. what i would like to do is on the day before the problem next update date, send the owner a mail notifying him that he needs to attend the meeting the following morning and he will need to provide and update. Is this possible? Rgds Paul |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| ***** PLEASE HELP **** Send an email from Excel to outlook with an automatic macro | Excel Discussion (Misc queries) | |||
| Email from excel.. please help?? | Excel Discussion (Misc queries) | |||
| sending email from excel not working | Excel Discussion (Misc queries) | |||
| Move a Column of 500 Email Addresses into BCC Field of an Email | Excel Worksheet Functions | |||
| body of email disappears when I send an email from Excel | Excel Discussion (Misc queries) | |||
Linear Mode
