Outlook VBA with api json lookup
Budget: €30 – €250 EUR
I’m looking for an experienced freelancer to create an Outlook VBA script that will help me automate emails.
This script will be triggered by a custom button on the outlook ribbon.
I know how to add this button and how to connect a macro to it myself.
The scipt needs to work only on the selected / active e-mailmessage in outlook.
This script needs to do the following:
Scrape the selected emailmessage for email-addresses in its content.
There are 3 specific email-adresses that needs to be excluded from the next steps.
With the remaining email-adresses please send a http request to an api.
The json reply will let you know if this emailadress already exist.
If the e-mailadress exist, we will need the value of the label "customerID" from the responds
Authentication is through a Bearer authorization.
Example code from the api-documentation:
curl -X 'GET' \
'https://api.example.cloud/api/exampledata/v3/customers/accounts?emailAddress=info%40example.com' \
-H 'accept: text/plain' \
-H 'Authorization: Bearer [token_here]'
I have tested this connection in php. It is working!
The token is more than 1023 characters long. This is important to know and work around in VBA. I experienced problems with this in the past.
There needs to be an escape for a couple of mailadresses.
If mailaddress == test_at_example.com then use customerid 2222
It is allowed to hard-code this.
With this data I would like to see a popup with the found e-mailadresses. If the same mailadress is found multiple time, please process it only one time.
Show the found e-mailaddress and if they exist in the external database and a custom url.
There are 2 variations for this url.
1. If the e-mail exist in the external database use the customerid from responds:
http://example.com/[customerID]
2. If the e-mail does not exist, the url needs to be based on the csv in the attachment of the e-mail:
http://example.com/new?customer={name_from_csv)&zip={zip_from_csv}&housenumber={number_from_csv}
Who can help me with this?
This script will be triggered by a custom button on the outlook ribbon.
I know how to add this button and how to connect a macro to it myself.
The scipt needs to work only on the selected / active e-mailmessage in outlook.
This script needs to do the following:
Scrape the selected emailmessage for email-addresses in its content.
There are 3 specific email-adresses that needs to be excluded from the next steps.
With the remaining email-adresses please send a http request to an api.
The json reply will let you know if this emailadress already exist.
If the e-mailadress exist, we will need the value of the label "customerID" from the responds
Authentication is through a Bearer authorization.
Example code from the api-documentation:
curl -X 'GET' \
'https://api.example.cloud/api/exampledata/v3/customers/accounts?emailAddress=info%40example.com' \
-H 'accept: text/plain' \
-H 'Authorization: Bearer [token_here]'
I have tested this connection in php. It is working!
The token is more than 1023 characters long. This is important to know and work around in VBA. I experienced problems with this in the past.
There needs to be an escape for a couple of mailadresses.
If mailaddress == test_at_example.com then use customerid 2222
It is allowed to hard-code this.
With this data I would like to see a popup with the found e-mailadresses. If the same mailadress is found multiple time, please process it only one time.
Show the found e-mailaddress and if they exist in the external database and a custom url.
There are 2 variations for this url.
1. If the e-mail exist in the external database use the customerid from responds:
http://example.com/[customerID]
2. If the e-mail does not exist, the url needs to be based on the csv in the attachment of the e-mail:
http://example.com/new?customer={name_from_csv)&zip={zip_from_csv}&housenumber={number_from_csv}
Who can help me with this?