 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / May 2007Getting Contents of email into Access from
I have an excel spreadsheet that people fill in with their order and email to me - they either do this as an attachment or some put it in the body of their email. It would be great if i could put a button on my access order form which lifts the product id and quantity from that email rather than what i do at the moment which is cut and paste it. I briefly looked at transferspreadsheet but it didn't seem to be the answer Any thoughts? You would likely be better off to set up a web page where people could enter their orders. A more advanced web page could be database driven, so that people's orders were automatically entered into your database. Attempting to read the contents of spreadsheet files that folks send back to you will be likely fraught with problems. You cannot really control whether people add or remove columns or rows from your spreadsheet. So, predictibility goes out the window, as far as knowing exactly which cell range to query. I suppose you could interrogate a named range, as long as that had not been changed (less likely). A less advanced web page (ie. not tied directly to a database) could be used where, once the customer pressed a submit button, an e-mail message would be generated behind the scenes and sent to you. This message would be very "well formed", since it would be created by the PC, and never seen or touched by the customer. In other words, you'd have control over exactly where the order information was placed in the body of the e-mail message. If you used Outlook to receive your messages (not Outlook Express), then you can write code in Access to get the customer name, ship to address, billing info., and items ordered from such a well formed message. If you'd like to explore this method further, I have a sample that I can share with you. Tom Wickerath Microsoft Access MVP https://mvp.support.microsoft.com/profile/Tom http://www.access.qbuilt.com/html/expert_contributors.html __________________________________________ > I have an excel spreadsheet that people fill in with their order and email to > me - they either do this as an attachment or some put it in the body of their [quoted text clipped - 7 lines] > > Any thoughts? > Attempting to read the contents of spreadsheet files that folks > send back to you will be likely fraught with problems. You cannot [quoted text clipped - 3 lines] > interrogate a named range, as long as that had not been changed > (less likely). Why not? Excel spreadsheets can be protected from editing except in the areas that you allow, and this feature can be password-protected. Yes, someone could probably crack the password if they wanted to, but who is going to bother with that in this kind of situation? Protection is something that goes back to Lotus 123 because I was writing spreadsheets for others to use back in 1986 that used protection. Of course, it doesn't change your main point -- it makes more sense to be database-driven on the website. I just wanted to point out that the problem you adduced for Excel spreadsheets doesn't really exist if you use Excel properly.  SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ I admit that I haven't checked in several versions of Excel, but I seem to recall that worksheet protection could be easily thwarted by using File > Save As, and giving it a new name. Then delete the original, and rename your new copy to the original name. Perhaps Microsoft has improved this in more recent versions, because, it has been several years since I checked. Tom Wickerath Microsoft Access MVP https://mvp.support.microsoft.com/profile/Tom http://www.access.qbuilt.com/html/expert_contributors.html __________________________________________ > Why not? Excel spreadsheets can be protected from editing except in > the areas that you allow, and this feature can be [quoted text clipped - 11 lines] > I just wanted to point out that the problem you adduced for Excel > spreadsheets doesn't really exist if you use Excel properly. > I admit that I haven't checked in several versions of Excel, but I > seem to recall that worksheet protection could be easily thwarted > by using File > Save As, and giving it a new name. Then delete the > original, and rename your new copy to the original name. Perhaps > Microsoft has improved this in more recent versions, because, it > has been several years since I checked. I don't know, but why would someone who is trying to interact with your website go to the trouble of doing that? And if you wanted to avoid that problem, surely it's possible through Excel automation to check if protection is still active. If it's not, then reject it, or do some checks to see if it's still formatted correctly before rejecting it. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ > I admit that I haven't checked in several versions of Excel, but I > seem to recall that worksheet protection could be easily thwarted > by using File > Save As, and giving it a new name. Then delete the > original, and rename your new copy to the original name. Perhaps > Microsoft has improved this in more recent versions, because, it > has been several years since I checked. There is no such loss of protection in Excel 97 or Excel 2000, the only two versions of Excel I have installed. Perhaps there's a step in the instructions you're leaving out? SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ > A less advanced web page (ie. not tied directly to a database) > could be used where, once the customer pressed a submit button, an [quoted text clipped - 8 lines] > message. If you'd like to explore this method further, I have a > sample that I can share with you. You could use any email program to receive the messages as long as it used a mailbox format that Access via file I/O could open and read. Then all you'd have to do is figure out the message delimiter and process it line-by-line. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ Perhaps you could do this. My code is specifically written for Outlook. That's what I volunteered to give to the OP. I did not volunteer to give code that I'd later be asked to modify for using with xyz Email program. Tom Wickerath Microsoft Access MVP https://mvp.support.microsoft.com/profile/Tom http://www.access.qbuilt.com/html/expert_contributors.html __________________________________________ > You could use any email program to receive the messages as long as > it used a mailbox format that Access via file I/O could open and > read. Then all you'd have to do is figure out the message delimiter > and process it line-by-line. You would likely be better off to set up a web page where people could enter their orders. A more advanced web page could be database driven, so that people's orders were automatically entered into your database. Attempting to read the contents of spreadsheet files that folks send back to you will be likely fraught with problems. You cannot really control whether people add or remove columns or rows from your spreadsheet. So, predictibility goes out the window, as far as knowing exactly which cell range to query. I suppose you could interrogate a named range, as long as that had not been changed (less likely). A less advanced web page (ie. not tied directly to a database) could be used where, once the customer pressed a submit button, an e-mail message would be generated behind the scenes and sent to you. This message would be very "well formed", since it would be created by the PC, and never seen or touched by the customer. In other words, you'd have control over exactly where the order information was placed in the body of the e-mail message. If you used Outlook to receive your messages (not Outlook Express), then you can write code in Access to get the customer name, ship to address, billing info., and items ordered from such a well formed message. If you'd like to explore this method further, I have a sample that I can share with you. Tom Wickerath Microsoft Access MVP https://mvp.support.microsoft.com/profile/Tom http://www.access.qbuilt.com/html/expert_contributors.html __________________________________________ > I have an excel spreadsheet that people fill in with their order and email to > me - they either do this as an attachment or some put it in the body of their [quoted text clipped - 7 lines] > > Any thoughts? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.