Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with email workbook
Hi,
I hope I can explain this well enough for you guys to help me out. I have set up a wb for price changes which is to be emailed to a director to approve or reject. Iam using Excel2003 and Outlook. I am still testing this so it is not a Live wb yet. But the sales managers will all need access. The wb consists of wsheet Input wsheet LookupList wsheet PriceRequest wsheet Approved wsheet Rejected The input sheet is like a form but I am using data validation list from the LookupList sheet. Once all fields have been entered the user clicks a command button and the data on ws Input is copied to ws PriceRequest, the wb gets emailed to the Director for approval. The data is then cleared from the Input ws. That bit works. The Director will open the file within the emailed message and approve/reject the request by clicking on the appropirate check box Yes or No on the Input ws, depending on which option has been selected this will copy the data to either the Approved ws or the Rejected ws, email the wb to the person who sent the request.The data is then cleared from the Input ws. This bit works The problem I have is how do I update the original wb. I have tried to save file by clicking the x on Excel so that it prompts to save but when I say yes, it is not saving to the original path. Infact I do no where it is saving to, probably some temp folder. Any ideas how I get get around this? The other problem I think I may have is if more than one request gets emailed to the director before he has had time to approve or reject, on save (if I get around this) it may overwrite new data. I hope this make sense and apologies for this long winded question. Thanks Winnie |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with email workbook
If you do a File Save As it will show you the default folder where the save
is going to. You probably have to specify the path to save the file. You can save a file to any of the following 3 formats c:\temp\book1.xls \\mynetworkpath\temp\book1.xls http://microsoft.com/myfolder/temp/book1.xls Provided you have permission to save the file in the folder. "winnie123" wrote: Hi, I hope I can explain this well enough for you guys to help me out. I have set up a wb for price changes which is to be emailed to a director to approve or reject. Iam using Excel2003 and Outlook. I am still testing this so it is not a Live wb yet. But the sales managers will all need access. The wb consists of wsheet Input wsheet LookupList wsheet PriceRequest wsheet Approved wsheet Rejected The input sheet is like a form but I am using data validation list from the LookupList sheet. Once all fields have been entered the user clicks a command button and the data on ws Input is copied to ws PriceRequest, the wb gets emailed to the Director for approval. The data is then cleared from the Input ws. That bit works. The Director will open the file within the emailed message and approve/reject the request by clicking on the appropirate check box Yes or No on the Input ws, depending on which option has been selected this will copy the data to either the Approved ws or the Rejected ws, email the wb to the person who sent the request.The data is then cleared from the Input ws. This bit works The problem I have is how do I update the original wb. I have tried to save file by clicking the x on Excel so that it prompts to save but when I say yes, it is not saving to the original path. Infact I do no where it is saving to, probably some temp folder. Any ideas how I get get around this? The other problem I think I may have is if more than one request gets emailed to the director before he has had time to approve or reject, on save (if I get around this) it may overwrite new data. I hope this make sense and apologies for this long winded question. Thanks Winnie |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with email workbook
Thanks,
I have sorted the save issue but can you advise on my 2nd point If there are more than 1 request emailed to the director before he has had time to approve or reject. If he selects to approve lets say the 2nd request first, then when he goes back to approve the first request the file saves but I will lose the information for the 2nd request. Any ideas. I have tried modifying but my lack of knowledge is not that great. I now get an updated file but with no data copied. I can post my code if it helps. This is a sure way to drive me round the bend. Thanks Winnie "joel" wrote: If you do a File Save As it will show you the default folder where the save is going to. You probably have to specify the path to save the file. You can save a file to any of the following 3 formats c:\temp\book1.xls \\mynetworkpath\temp\book1.xls http://microsoft.com/myfolder/temp/book1.xls Provided you have permission to save the file in the folder. "winnie123" wrote: Hi, I hope I can explain this well enough for you guys to help me out. I have set up a wb for price changes which is to be emailed to a director to approve or reject. Iam using Excel2003 and Outlook. I am still testing this so it is not a Live wb yet. But the sales managers will all need access. The wb consists of wsheet Input wsheet LookupList wsheet PriceRequest wsheet Approved wsheet Rejected The input sheet is like a form but I am using data validation list from the LookupList sheet. Once all fields have been entered the user clicks a command button and the data on ws Input is copied to ws PriceRequest, the wb gets emailed to the Director for approval. The data is then cleared from the Input ws. That bit works. The Director will open the file within the emailed message and approve/reject the request by clicking on the appropirate check box Yes or No on the Input ws, depending on which option has been selected this will copy the data to either the Approved ws or the Rejected ws, email the wb to the person who sent the request.The data is then cleared from the Input ws. This bit works The problem I have is how do I update the original wb. I have tried to save file by clicking the x on Excel so that it prompts to save but when I say yes, it is not saving to the original path. Infact I do no where it is saving to, probably some temp folder. Any ideas how I get get around this? The other problem I think I may have is if more than one request gets emailed to the director before he has had time to approve or reject, on save (if I get around this) it may overwrite new data. I hope this make sense and apologies for this long winded question. Thanks Winnie |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with email workbook
When you are doing the SAVEAS it create a new workbook. You have to now use
the New workbook when you make the 2nd change. Set Oldbk = thisworkbook OldBk.Saveas filename:=abc.xls 'the new workbook becomes the active workbook Set Oldbk = activeworkbook "winnie123" wrote: Thanks, I have sorted the save issue but can you advise on my 2nd point If there are more than 1 request emailed to the director before he has had time to approve or reject. If he selects to approve lets say the 2nd request first, then when he goes back to approve the first request the file saves but I will lose the information for the 2nd request. Any ideas. I have tried modifying but my lack of knowledge is not that great. I now get an updated file but with no data copied. I can post my code if it helps. This is a sure way to drive me round the bend. Thanks Winnie "joel" wrote: If you do a File Save As it will show you the default folder where the save is going to. You probably have to specify the path to save the file. You can save a file to any of the following 3 formats c:\temp\book1.xls \\mynetworkpath\temp\book1.xls http://microsoft.com/myfolder/temp/book1.xls Provided you have permission to save the file in the folder. "winnie123" wrote: Hi, I hope I can explain this well enough for you guys to help me out. I have set up a wb for price changes which is to be emailed to a director to approve or reject. Iam using Excel2003 and Outlook. I am still testing this so it is not a Live wb yet. But the sales managers will all need access. The wb consists of wsheet Input wsheet LookupList wsheet PriceRequest wsheet Approved wsheet Rejected The input sheet is like a form but I am using data validation list from the LookupList sheet. Once all fields have been entered the user clicks a command button and the data on ws Input is copied to ws PriceRequest, the wb gets emailed to the Director for approval. The data is then cleared from the Input ws. That bit works. The Director will open the file within the emailed message and approve/reject the request by clicking on the appropirate check box Yes or No on the Input ws, depending on which option has been selected this will copy the data to either the Approved ws or the Rejected ws, email the wb to the person who sent the request.The data is then cleared from the Input ws. This bit works The problem I have is how do I update the original wb. I have tried to save file by clicking the x on Excel so that it prompts to save but when I say yes, it is not saving to the original path. Infact I do no where it is saving to, probably some temp folder. Any ideas how I get get around this? The other problem I think I may have is if more than one request gets emailed to the director before he has had time to approve or reject, on save (if I get around this) it may overwrite new data. I hope this make sense and apologies for this long winded question. Thanks Winnie |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with email workbook
Thanks for the advice.
I managed to work round it another way. The file the sales managers will use, once they send the email the data is deleted from the file.As the email creates a temp file. Then when the director approves/rejects the data is copied from the temp file into a Master file. That way I can ensure no records get overwritten as only 1 row of data is being added at any time. Took me most of yesterday but have got there in the end :-) . Winnie "joel" wrote: When you are doing the SAVEAS it create a new workbook. You have to now use the New workbook when you make the 2nd change. Set Oldbk = thisworkbook OldBk.Saveas filename:=abc.xls 'the new workbook becomes the active workbook Set Oldbk = activeworkbook "winnie123" wrote: Thanks, I have sorted the save issue but can you advise on my 2nd point If there are more than 1 request emailed to the director before he has had time to approve or reject. If he selects to approve lets say the 2nd request first, then when he goes back to approve the first request the file saves but I will lose the information for the 2nd request. Any ideas. I have tried modifying but my lack of knowledge is not that great. I now get an updated file but with no data copied. I can post my code if it helps. This is a sure way to drive me round the bend. Thanks Winnie "joel" wrote: If you do a File Save As it will show you the default folder where the save is going to. You probably have to specify the path to save the file. You can save a file to any of the following 3 formats c:\temp\book1.xls \\mynetworkpath\temp\book1.xls http://microsoft.com/myfolder/temp/book1.xls Provided you have permission to save the file in the folder. "winnie123" wrote: Hi, I hope I can explain this well enough for you guys to help me out. I have set up a wb for price changes which is to be emailed to a director to approve or reject. Iam using Excel2003 and Outlook. I am still testing this so it is not a Live wb yet. But the sales managers will all need access. The wb consists of wsheet Input wsheet LookupList wsheet PriceRequest wsheet Approved wsheet Rejected The input sheet is like a form but I am using data validation list from the LookupList sheet. Once all fields have been entered the user clicks a command button and the data on ws Input is copied to ws PriceRequest, the wb gets emailed to the Director for approval. The data is then cleared from the Input ws. That bit works. The Director will open the file within the emailed message and approve/reject the request by clicking on the appropirate check box Yes or No on the Input ws, depending on which option has been selected this will copy the data to either the Approved ws or the Rejected ws, email the wb to the person who sent the request.The data is then cleared from the Input ws. This bit works The problem I have is how do I update the original wb. I have tried to save file by clicking the x on Excel so that it prompts to save but when I say yes, it is not saving to the original path. Infact I do no where it is saving to, probably some temp folder. Any ideas how I get get around this? The other problem I think I may have is if more than one request gets emailed to the director before he has had time to approve or reject, on save (if I get around this) it may overwrite new data. I hope this make sense and apologies for this long winded question. Thanks Winnie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
cant email workbook thru excel | Excel Discussion (Misc queries) | |||
how to email one sheet only of a workbook | Excel Discussion (Misc queries) | |||
Email Workbook | Excel Programming | |||
Email Workbook | Excel Programming | |||
Email Workbook | Excel Programming |