glaupie
01-18-2013, 05:12 AM
Hi There,
Every day I get a system email that confirms the upload of a bulk SMS file to an FTP server, from where another company will collect it and send out the SMS's throughout the day.
The file confirms the upload and filename in 2 lines, the shows all errors (usually invalid cell phone numbers), with ~ as delimiter between the cell number, SMS Message and Reject Code.
I need a way to export ONLY the errors into excel, .csv or .txt as the email arrives, and then send it to our Customer Service guys. SO I need a script to look at all incoming emails from that address and run a script to copy all text AFTER the word "Errors" to excel, then (maybe) run an excel macro to do text to columns and send the email?
Example of email message:
Hi Gerhard Laubscher, you uploaded a file with username : EMC-Gerhard
File processed (eventid - 172888944) : Tenacity_ZA_20130118033040.txt
Errors:
27000000000~Mr Ndzo, we are trying to contact you re. your Dunns account. Payment required: R650.70. Ref: 10201102120001069561. Tel: 0860992222.~PROD-2543464 - Number Incorrect
27000000000~Mrs Sekhonyane, we are trying to contact you re. your Dunns account. Payment required: R395.89. Ref: 1944210005484474. Tel: 0860992222.~PROD-2543234 - Number Incorrect
27000000000~Mr Gezula, we are trying to contact you re. your Dunns account. Payment required: R427.29. Ref: 1944210005220795. Tel: 0860992222.~PROD-2543566 - Number Incorrect
Every day I get a system email that confirms the upload of a bulk SMS file to an FTP server, from where another company will collect it and send out the SMS's throughout the day.
The file confirms the upload and filename in 2 lines, the shows all errors (usually invalid cell phone numbers), with ~ as delimiter between the cell number, SMS Message and Reject Code.
I need a way to export ONLY the errors into excel, .csv or .txt as the email arrives, and then send it to our Customer Service guys. SO I need a script to look at all incoming emails from that address and run a script to copy all text AFTER the word "Errors" to excel, then (maybe) run an excel macro to do text to columns and send the email?
Example of email message:
Hi Gerhard Laubscher, you uploaded a file with username : EMC-Gerhard
File processed (eventid - 172888944) : Tenacity_ZA_20130118033040.txt
Errors:
27000000000~Mr Ndzo, we are trying to contact you re. your Dunns account. Payment required: R650.70. Ref: 10201102120001069561. Tel: 0860992222.~PROD-2543464 - Number Incorrect
27000000000~Mrs Sekhonyane, we are trying to contact you re. your Dunns account. Payment required: R395.89. Ref: 1944210005484474. Tel: 0860992222.~PROD-2543234 - Number Incorrect
27000000000~Mr Gezula, we are trying to contact you re. your Dunns account. Payment required: R427.29. Ref: 1944210005220795. Tel: 0860992222.~PROD-2543566 - Number Incorrect