Help with email workbook

[winnie123]

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

 

[joel]

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]

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]

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]

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