Excel 2010 file with VBA save and send code works-but file can't be found.

**carlton.clay** · 03-13-2013, 09:16 AM

I have created a form at work with "save and send" functionality that works, except for if you try and find the file you saved to your computer- it can't be found. Specifically, I'm searching in Windows 7 using the search bar above the "start" icon- it will actually display the file you just saved- but when you click on it- you will get the error "Windows is searching..."....and then later you will get a prompt saying that the shortcut has been changed or moved and no longer works. I'm using a variety of code that I have picked up from different help websites and everything works otherwise.
Any suggestions- here is code:

Please Login or Register  to view this content.

**JBeaucaire** · 03-13-2013, 09:31 AM

Reading your own macro, one quickly sees:

Please Login or Register  to view this content.

So your macro deletes the file after it has been mailed. Right? It's your macro so it's doing what you've allowed.

Now, look for the line of code that deletes your file and remove that, then it won't be deleted. That's pretty easy to spot, too, since it's commented.

Also, since you want to KEEP the file, perhaps you want to save it in a more exact location instead of letting the macro hide it in a TEMP folder. So maybe change this to something you want:

Please Login or Register  to view this content.

Like:

Please Login or Register  to view this content.

**carlton.clay** · 03-13-2013, 09:39 AM

Thanks JB-
I tried deleting various lines below- first just the "Kill...." line. It didn't work.
Then I deleted everythng below- still didn't work.
It continues to save, then it can be found during search, but then when you click on it- can't be found.

Allow me to through in a variable here- because of the nature of this process, it has been a blessing that the file does not save. The email's go to a contractor. If there is an issue with the contractor regarding this issue- we would rather have the user find the original email sent and forward it back to the contractor with their inquiry. Additionally- we have made 5 changes to the form in 3 months. We don't want users going back to an old form they have saved - we want them to always go to the web for the newest version.
Any other ideas?

Please Login or Register  to view this content.

**carlton.clay** · 03-13-2013, 09:44 AM

Looking back through my notes- I see I may have looked at this article to get my code used. I see something now that I didn't see then- they indicate that the file will be removed or erased from the hard disk. Not sure how I can change that- i guess that is the nature of my question:

http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

What would be really cool is if I could let the user save it as part of the save and send VBA--- but attached to the file name would be a string like "....NOT TO BE USED TO CREATE NEW REQ...."