Is it possible to use VBA to automatically upload data captured in an email into an Excel spreadsheet by saving that email as an Outlook item and then using VBA to select that saved email?
We have a website where applicants enter their information and once they submit it, we receive an email with the captured data. The field headers are always the same and are in bold on our form. The data collected is under each header and not boldfaced. We’d like to be able to just select the Outlook item from a location where it is saved on the network and have the data (not necessarily the headers) be imported into predetermined cells on a spreadsheet. During the upload, we need to have the text data converted to proper case where it makes sense (name, address, etc.) as some applicants enter all their information in all upper case letters or all lower case letters. Is this possible to do? Can anyone help? Thanks.
Below is a sample of the email as we receive it:
You've just received a new submission to your Financing Approval Form.
Submitted Information:
Applicant Name
Joe Tester
Birthday MM/DD/YYYY
02/28/1972
Applicant Email Address
joe@tester.ca
Title
Mr.
Marital Status
Single
Primary Phone Number
905 - 508 - 9874
Secondary Phone Number
647 - 898 - 7913
Current Address
123 Penny Lane
Unit 12
Toronto, Ontario M6G 2W4
How long have you lived there?
less than a year
How much is your monthly rent/mortgage?
500
What is your gross monthly income?
4500
What is your current job title or description?
Tester
Are you self employed?
No
Current Employer
Capital4 Cars
How long have you worked there?
one to two years
Work Address
456 Uptown Court
Unit 12
Mississauga, Ontario L7T 2S4
Additional helpful comments about applicant
This applicant also receives tip works nights at a restaurant
Year
2008
Make
Dodge
Model
Ram
Trim
1500 Hemi
Colour
Black
Kilometres
130251
Notable Options
21" Wheels, Sunroof
Vehicle VIN#
A3DF567BH78856
Was the car ever registered outside Ontario?
No
Have there ever been accidents or claims on the vehicle, regardless of amount?
No
Are you a co-signer?
No
Work Phone Number
905 - 502 - 0122
Have you ever declared an involuntary automotive repossession?
No
Have you ever declared a consumer proposal?
No
Have you ever declared bankruptcy?
No
Vehicle Cash Price Before Tax
10000
Desired Payment
150
Cash Downpayment
1000
Select Frequency
Bi-Weekly
Value of Trade-In
500
Dealership Name
Cosmos Motors
Salesperson Name
Slim Shady
Salesperson Email Address
slim@cosmosmotors.ca
Dealer Phone Number
416-586-1414
We have a website where applicants enter their information and once they submit it, we receive an email with the captured data. The field headers are always the same and are in bold on our form. The data collected is under each header and not boldfaced. We’d like to be able to just select the Outlook item from a location where it is saved on the network and have the data (not necessarily the headers) be imported into predetermined cells on a spreadsheet. During the upload, we need to have the text data converted to proper case where it makes sense (name, address, etc.) as some applicants enter all their information in all upper case letters or all lower case letters. Is this possible to do? Can anyone help? Thanks.
Below is a sample of the email as we receive it:
You've just received a new submission to your Financing Approval Form.
Submitted Information:
Applicant Name
Joe Tester
Birthday MM/DD/YYYY
02/28/1972
Applicant Email Address
joe@tester.ca
Title
Mr.
Marital Status
Single
Primary Phone Number
905 - 508 - 9874
Secondary Phone Number
647 - 898 - 7913
Current Address
123 Penny Lane
Unit 12
Toronto, Ontario M6G 2W4
How long have you lived there?
less than a year
How much is your monthly rent/mortgage?
500
What is your gross monthly income?
4500
What is your current job title or description?
Tester
Are you self employed?
No
Current Employer
Capital4 Cars
How long have you worked there?
one to two years
Work Address
456 Uptown Court
Unit 12
Mississauga, Ontario L7T 2S4
Additional helpful comments about applicant
This applicant also receives tip works nights at a restaurant
Year
2008
Make
Dodge
Model
Ram
Trim
1500 Hemi
Colour
Black
Kilometres
130251
Notable Options
21" Wheels, Sunroof
Vehicle VIN#
A3DF567BH78856
Was the car ever registered outside Ontario?
No
Have there ever been accidents or claims on the vehicle, regardless of amount?
No
Are you a co-signer?
No
Work Phone Number
905 - 502 - 0122
Have you ever declared an involuntary automotive repossession?
No
Have you ever declared a consumer proposal?
No
Have you ever declared bankruptcy?
No
Vehicle Cash Price Before Tax
10000
Desired Payment
150
Cash Downpayment
1000
Select Frequency
Bi-Weekly
Value of Trade-In
500
Dealership Name
Cosmos Motors
Salesperson Name
Slim Shady
Salesperson Email Address
slim@cosmosmotors.ca
Dealer Phone Number
416-586-1414
