I have a small fence company where my leads are generated on the internet and sent off a form with 11 fields. I receive the form via email, each email has all 11 field titles and the information for each field. What I want to do sounds simple but I have not been able to figure out how to import that information into either an excel spreadsheet or a filemaker DB. If someone can either lead me in the right direction, or wants to write the applescript to do that I would very much appreciate it. I am willing to make a donation for your time. If you want to see the form go to http://www.raleighfences.com/formest.html You can also email me at the raleighfences.com using krp@.
Model: Sawtooth G4
Browser: Safari 419.3
Operating System: Mac OS X (10.4)
set AppleScript's text item delimiters to ""
set fld to {"First: ", "Last: ", "street: ", "City: ", "State: ", "Zip: ", "phone: ", "Vinyl: ", "Project Details: ", "contact: ", "referred by: "}
-- here the email would be read (how depends on mail application)
set msg to "First: Testy
Last: McTester
street: 1805 Sabrina drive
City: Raleigh
State: nc
Zip: 27604
phone: 9195551515
Vinyl: on
Project Details: Need my pool enclosed
contact: days
referred by: Referral"
-- now grab the parts
set tid to AppleScript's text item delimiters
set msgParts to {}
repeat with k from 1 to count fld
set AppleScript's text item delimiters to (fld's item k)
set temp to (last text item of msg)
set AppleScript's text item delimiters to return
set end of msgParts to first text item of temp
set AppleScript's text item delimiters to tid
end repeat
-- now have a list of the parts:
msgParts --> {"Testy", "McTester", "1805 Sabrina drive", "Raleigh", "nc", "27604", "9195551515", "on", "Need my pool enclosed", "days", "Referral"}
-- These would then be loaded into the appropriate receiving application. I don't have a copy of Excel 2004 or FileMaker, so I stop here.
One more question! Where it says vinyl, this could also read wood, chain link, steel, aluminum, other. There are several buttons and it just depends on which one is turned on.
you could solve your vinyl/wood/chain … problem like so:
set fld to {"First", "Last", "street", "City", "State", "Zip", "phone", "Project Details", "contact", "referred by"}
set msg to "First: Testy
Last: McTester
street: 1805 Sabrina drive
City: Raleigh
State: nc
Zip: 27604
phone: 9195551515
Vinyl: on
Project Details: Need my pool enclosed
contact: days
referred by: Referral"
set resultList to {}
set {olddelims, text item delimiters} to {text item delimiters, ": "}
repeat with theLine in paragraphs of msg
if text item 1 of theLine is in fld then
set resultList to resultList & (text item 2 of theLine)
else if theLine ends with "on" then
set resultList to resultList & (text item 1 of theLine)
end if
end repeat
set text item delimiters to olddelims
end
get resultList
→ results:
{“Testy”, “McTester”, “1805 Sabrina drive”, “Raleigh”, “nc”, “27604”, “9195551515”, “Vinyl”, “Need my pool enclosed”, “days”, “Referral”}
Or like this, which also pays attention to the form of the return used in the email:
set Ret to {ASCII character 13, ASCII character 10, (ASCII character 10) & (ASCII character 13)}
set Mat to {"Wood: ", "Chain Link: ", "Steel: ", "Aluminum: ", "Vinyl: ", "Other: "}
set fld to {"First: ", "Last: ", "street: ", "City: ", "State: ", "Zip: ", "phone: ", "Matl: ", "Project Details: ", "contact: ", "referred by: "}
set msg to "First: Testy
Last: McTester
street: 1805 Sabrina drive
City: Raleigh
State: nc
Zip: 27604
phone: 9195551515
Vinyl: on
Project Details: Need my pool enclosed
contact: days
referred by: Referral"
-- Get form of return
repeat with aR in Ret
if msg contains contents of aR then set rtn to contents of aR
end repeat
-- Figure out the Material (Matl)
repeat with aMatl in Mat
if msg contains contents of aMatl then set item 8 of fld to contents of aMatl
end repeat
-- now grab the parts
set tid to AppleScript's text item delimiters
set msgParts to {}
repeat with k from 1 to count fld
set AppleScript's text item delimiters to (fld's item k)
set temp to (last text item of msg)
set AppleScript's text item delimiters to rtn
set end of msgParts to first text item of temp
set AppleScript's text item delimiters to tid
end repeat
if item 8 of msgParts is "on" then set item 8 of msgParts to item 8 of fld & "on"
-- now have a list of the parts:
msgParts --> {"Testy", "McTester", "1805 Sabrina drive", "Raleigh", "nc", "27604", "9195551515", "Vinyl: on", "Need my pool enclosed", "days", "Referral"}
-- These would then be loaded into the appropriate receiving application. I don't have a copy of Excel 2004 or FileMaker, so I stop here.
On my way home from work today I remembered a great application that does this sort of thing. I believe DanB ( a frequent contributor to these forums) was involved in this one.
It didn’t suit my needs entirely, but it sounds like its right up your alley.
Moving a little bit further
This works on select mail.
set Ret to {ASCII character 13, ASCII character 10, (ASCII character 10) & (ASCII character 13)}
set Mat to {"Wood: ", "Chain Link: ", "Steel: ", "Aluminum: ", "Vinyl: ", "Other: "}
set fld to {"First: ", "Last: ", "street: ", "City: ", "State: ", "Zip: ", "phone: ", "Matl: ", "Project Details: ", "contact: ", "referred by: "}
tell application "Mail"
set theMessages to selection
repeat with eachMessage in theMessages
set msg to content of eachMessage
-- Get form of return
repeat with aR in Ret
if msg contains contents of aR then set rtn to contents of aR
end repeat
-- Figure out the Material (Matl)
repeat with aMatl in Mat
if msg contains contents of aMatl then set item 8 of fld to contents of aMatl
end repeat
-- now grab the parts
set tid to AppleScript's text item delimiters
set msgParts to {}
repeat with k from 1 to count fld
set AppleScript's text item delimiters to (fld's item k)
set temp to (last text item of msg)
set AppleScript's text item delimiters to rtn
set end of msgParts to first text item of temp
set AppleScript's text item delimiters to tid
end repeat
if item 8 of msgParts is "on" then set item 8 of msgParts to item 8 of fld & "on"
end repeat
end tell
-- now have a list of the parts:
--set the colw to column width of range "B:B"
tell application "Microsoft Excel"
make new document
set horizontal alignment of range "B:B" to horizontal align right
set column width of range "A:A" to 15
set column width of range "B:B" to 20
end tell
repeat with i from 1 to number of items in fld
tell application "Microsoft Excel"
set value of cell ("$A$" & i as string) to item i of fld
set value of cell ("$B$" & i as string) to item i of msgParts
end tell
end repeat
ok this last one works and brings it into excel. Now i just have some massaging to do in order to get it workable but this goes a long way toward resolving my initial problem. You guys are great