Getting Contents of email into Access from

H

hdpink

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?
 
T

Tom Wickerath

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
__________________________________________
 
T

Tom Wickerath

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
__________________________________________
 
D

David W. Fenton

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).
Click to expand...

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.
 
D

David W. Fenton

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.
Click to expand...

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.
 
T

Tom Wickerath

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
__________________________________________
 
D

David W. Fenton

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.
Click to expand...

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.
 
D

David W. Fenton

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.
Click to expand...

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?
 
Top