Novice
Windows XP 
Access 2010 32bit
hi,
i was able to write some code that sends out emails to collect data. my boss wanted to create a custom RFQ sheet. it can send the html with all of the required fields. when it's replied, the outlook recognizes it as i can click "export data," but after "successfully exported data," the table hasn't been updated. i have no idea where to find the source of problem. i appreciate any help and i can post anything you guys need. thanks in advance.
Expert
Windows XP Access 2000
Can you post a sample of the data you're trying to import?
Also, from what it sounds like, you seem to be exporting the data directly from Outlook into Access. If that's true, you'd be much better off exporting it to a flat file of some kind (an excel workbook or CSV file for example) and then importing it into Access.
Even though that's most likely more work, that way you'll be able to trap and handle errors allowing you to see exactly what's going wrong during the import process.
Novice
Windows XP Access 2010 32bit
hi rawb,
thanks for the reply. ive attached an image of what it looks like and the html code. what i basically did was try to mimic what the data collection wizard does. i took a look at the html it was sending etc. i think i just need to figure out how html needs to be set but like you said it's a hard problem to find. how would you write vba from access to do what you suggested?
Novice
Windows XP Access 2010 32bit
here's the html code:
<html><body><table id="{A2A3818A-0D41-4B69-8AF2-DBAB7CBB4F78}" name="{A2A3818A-0D41-4B69-8AF2-DBAB7CBB4F78}"><tbody><tr><td></td></tr><tr><td><table rules="all" width="650"><tbody><tr><td bgcolor="black"><style="text-align:left;"><font size="2" color="white"><b>REQUEST FOR QUOTE</b></font></style="text-align:left;"></td><td width="50%"><font size="2"><i>Vendor fills in yellow area.</i></font></td></tr><tr><td><font size="2"></font><div align="right"><font size="2"><a target="_blank" name="table0field1">RFQ DATE:</a></font></div></td><td bgcolor="#99CC66" id="_DataItem_{name:table0field1}" name="_DataItem_{name:table0field1}">Mmmm</td></tr><tr><td><font size="2"></font><div align="right"><font size="2"><a target="_blank" name="table0field2">REQUESTED BY:</a></font></div></td><td bgcolor="#99CC66" id="_DataItem_{name:table0field2}" name="_DataItem_{name:table0field2}">me</td></tr><tr><td><font size="2"></font><div align="right"><font size="2"><a target="_blank" name="table0field3">IMAGE:</a></font></div></td><td bgcolor="#99CC66"><img onload="View.inlineImageLoaded(this,undefined,fals e)" border="0" src="./Workspace Webmail Mail Index Sent Items_files/download.php" width="200" height="200" wbeuser="peter@fringestudio.com"></td></tr><tr><td><font size="2"></font><div align="right"><font size="2"><a target="_blank" name="table0field4">ITEM DESCRIPTION</a></font></div></td><td bgcolor="#99CC66" id="_DataItem_{name:table0field4}" name="_DataItem_{name:table0field4}">item</td></tr><tr><td><font size="2"></font><div align="right"><font size="2"><a target="_blank" name="table0field5">CUSTOMER</a></font></div></td><td bgcolor="#99CC66" id="_DataItem_{name:table0field5}" name="_DataItem_{name:table0field5}">cust</td></tr><tr><td><font size="2"></font><div align="right"><font size="2"><a target="_blank" name="table0field6">QTY PER DESIGN:</a></font></div></td><td bgcolor="#99CC66" id="_DataItem_{name:table0field6}" name="_DataItem_{name:table0field6}">qty</td></tr><tr><td><font size="2"></font><div align="right"><font size="2"><a target="_blank" name="table0field7">NO OF DESIGNS:</a></font></div></td><td bgcolor="#99CC66" id="_DataItem_{name:table0field7}" name="_DataItem_{name:table0field7}">noof</td></tr><tr><td><font size="2"></font><div align="right"><font size="2"><a target="_blank" name="table0field8">GLASS SIZE:</a></font></div></td><td id="_DataItem_{name:table0field8}" name="_DataItem_{name:table0field8}">glass</td></tr><tr><td><font size="2"></font><div align="right"><font size="2"><a target="_blank" name="table0field9">GLASS QUALITY:</a></font></div></td><td id="_DataItem_{name:table0field9}" name="_DataItem_{name:table0field9}">qua</td></tr><tr><td><font size="2"></font><div align="right"><font size="2"><a target="_blank" name="table0field10">COLOR COMPOSITION:</a></font></div></td><td id="_DataItem_{name:table0field10}" name="_DataItem_{name:table0field10}">color</td></tr><tr><td><font size="2"></font><div align="right"><font size="2"><a target="_blank" name="table0field11">ADDITIONAL DETAILS:</a></font></div></td><td id="_DataItem_{name:table0field11}" name="_DataItem_{name:table0field12}">add</td></tr><tr><td><font size="2"></font><div align="right"><font size="2"><a target="_blank" name="table0field12">PACKAGING SPECS:</a></font></div></td><td id="_DataItem_{name:table0field12}" name="_DataItem_{name:table0field12}">pack</td></tr><tr><td><font size="2"></font><div align="right"><font size="2"><a target="_blank" name="table0field13">PAPER QUALITY</a></font></div></td><td id="_DataItem_{name:table0field13}" name="_DataItem_{name:table0field13}">paper</td></tr><tr><td><font size="2"></font><div align="right"><font size="2"><a target="_blank" name="table0field14">DELIVERY:</a></font></div></td><td id="_DataItem_{name:table0field14}" name="_DataItem_{name:table0field14}">deli</td></tr><tr><td><font size="2"></font><div align="right"><font size="2"><a target="_blank" name="table0field15">POLYBAG:</a></font></div></td><td id="_DataItem_{name:table0field15}" name="_DataItem_{name:table0field15}">poly</td></tr><tr><td><font size="2"></font><div align="right"><font size="2"><a target="_blank" name="table0field16">INNER/MASTER PACK REQ:</a></font></div></td><td id="_DataItem_{name:table0field16}" name="_DataItem_{name:table0field16}">mas</td></tr><tr><td><font size="2"></font><div align="right"><font size="2"><a target="_blank" name="table0field17">INNER/MASTER MEASUREMENTS:</a></font></div></td><td id="_DataItem_{name:table0field17}" name="_DataItem_{name:table0field17}">inner</td></tr><tr><td><font size="2"></font><div align="right"><font size="2"><a target="_blank" name="table0field18">DUMMIES REQUIRED:</a></font></div></td><td id="_DataItem_{name:table0field18}" name="_DataItem_{name:table0field18}">dumm</td></tr><tr><td><font size="2"></font><div align="right"><font size="2"><a target="_blank" name="table0field19">APPROVAL SAMPLES REQUIRED:</a></font></div></td><td id="_DataItem_{name:table0field19}" name="_DataItem_{name:table0field19}">app</td></tr><tr><td><font size="2"></font><div align="right"><font size="2"><a target="_blank" name="table0field20">TEST SAMPLES REQUIRED UPON APPROVAL:</a></font></div></td><td id="_DataItem_{name:table0field20}" name="_DataItem_{name:table0field20}">test</td></tr></tbody></table><table rules="all" width="650"><tbody><tr><td><font size="2" color="red"></font><div align="center"><font size="2" color="red">*Please note that all metal components, glue, foil hot stamp, glitter and surface coatings must fall within all established US and California Prop 65 guidelines, in particular lead and phthalates.</font></div></td></tr></tbody></table><table rules="all" width="650"><tbody><tr><td width="50%"><font size="2"></font><div align="right"><font size="2"><a target="_blank" name="table0field21">MOQ:</a></font></div></td><td bgcolor="yellow" id="_DataItem_{name:table0field21}" name="_DataItem_{name:table0field21}">tttt</td></tr><tr><td><font size="2"></font><div align="right"><font size="2"><a target="_blank" name="table0field22">GLASS COST PER UNIT:</a></font></div></td><td bgcolor="yellow" id="_DataItem_{name:table0field22}" name="_DataItem_{name:table0field22}">cos</td></tr><tr><td><font size="2"></font><div align="right"><font size="2"><a target="_blank" name="table0field23">BOX COST PER UNIT:</a></font></div></td><td bgcolor="yellow" id="_DataItem_{name:table0field23}" name="_DataItem_{name:table0field23}">box</td></tr><tr><td><font size="2"></font><div align="right"><font size="2"><a target="_blank" name="table0field24">ONE-TIME CHARGES:</a></font></div></td><td bgcolor="yellow" id="_DataItem_{name:table0field24}" name="_DataItem_{name:table0field24}">one</td></tr><tr><td><font size="2"></font><div align="right"><font size="2"><a target="_blank" name="table0field25">(TOTAL) COST PER SET:</a></font></div></td><td bgcolor="yellow" id="_DataItem_{name:table0field25}" name="_DataItem_{name:table0field25}">total</td></tr><tr><td><font size="2"></font><div align="right"><font size="2"><a target="_blank" name="table0field26">FOB POINT:</a></font></div></td><td bgcolor="yellow" id="_DataItem_{name:table0field26}" name="_DataItem_{name:table0field26}">fob</td></tr><tr><td><font size="2"></font><div align="right"><font size="2"><a target="_blank" name="table0field27">VENDOR:</a></font></div></td><td bgcolor="yellow" id="_DataItem_{name:table0field27}" name="_DataItem_{name:table0field27}">vend</td></tr><tr><td><font size="2"></font><div align="right"><font size="2"><a target="_blank" name="table0field28">QUOTED BY (Name of person):</a></font></div></td><td bgcolor="yellow" id="_DataItem_{name:table0field28}" name="_DataItem_{name:table0field28}">quo</td></tr><tr><td><font size="2"></font><div align="right"><font size="2"><a target="_blank" name="table0field29">VENDOR NOTES:</a></font></div></td><td bgcolor="yellow" id="_DataItem_{name:table0field29}" name="_DataItem_{name:table0field29}">vendor</td></tr><tr><td><font size="2"></font><div align="right"><font size="2"><a target="_blank" name="table0field30">TERMS AND PAYMENT REQUIREMENTS:</a></font></div></td><td bgcolor="yellow" id="_DataItem_{name:table0field30}" name="_DataItem_{name:table0field30}"> </td></tr></tbody></table><table rules="all" width="650"><tbody><tr><td><font size="2" color="red"><br></font><div align="center"><font size="2" color="red">*Please note that quotes must include ALL costs. Any additional fees will not be accepted after final quote is accepted.</font></div></td></tr></tbody></table></td></tr></tbody></table> </div> </blockquote></span></div></td></tr></tbody></table></div><a name="attachments"></a>
Expert
Windows XP Access 2000
Uh, wow. I may be out of my league on this one. . .
It looks like you're basically wanting to treat each E-mail response as a Record in your Table. If that's the case, then you may try copying one of the e-mail's contents into Excel and saving it as a .XLSX (or .XLS) file and then importing it from Access. That may at least show you where the error is.
How I'd handle this (once you figure out the importing issue) is to write up some VBA code that makes use of Office Automation to open Outlook and, one at a time, pull the information straight out of the e-mails. This should be doable as long as you have a consistent way of identifying which e-mails you want to access (like putting them all in a specific folder or having the Subject line all start with the same text).
Novice
Windows XP Access 2010 32bit
thanks rawb. i'll try that.
Novice
Windows XP Access 2010 32bit
hi rawb,
i found the problem. the code was not inserting primary key in the html. thanks for your help. if it wasnt for you i wouldnt have thought outside the box.
Novice
Windows 7 64bit Access 2010 64bit
Noners, I am trying to solve the same issue you have solved. Anyway I can see the VB code on this? I am not real good at VB but am able to make modifications for it to work in my case.Originally Posted by noner22
I already have a table and I am in need to automate a Email Data collection for every Monday and then update the table with the information. The information would be a new record. The email would go out to roughly 20 peers who would update on current project status.
Thanks in advance.
P.S. Your's is the only thread I have fund thus far that was able to find a solution to this issue. Really hoping for your response. Thanks a lot in advance.
Data collection
Posting Permissions
Reply With Quote