PART II. Automating Data Entry from Email to Excel (Receiving emails from my business website where, customers sign up. Getting the customer's details from the email's body content and save the information to my customer's database which is in Excel)
Receiving multiple emails from a registration form you have put on your site and need to keep a record of all this information coming in to your excel worksheet, but do not have enough time to do all this copy and pasting? Well.. no need to worry no more! WinAutomation provides you with a automated solution!
(Please note that in order to get this job to work you need to have Thunderbird and Autoextract running and also created the job described in the INTRO part of this Topic)
INTRO. Using the FileMonitorTrigger in combination with Thunderbird to start jobs according to emails received based on subject, email, email body etc.
Creating a Child Job - Parsing Specific Text from Content using Regular Expressions
Once our parent job is set (check INTRO), we can now use any information taken from within that job by using the prefix "ext:" within the variable. So I will use the parse text action, and in the text to parse field i will type %ext:FileContents%
I need to parse specific fields, such as the customer's fullname, the email address and the customer id. So I will run the parse action 3 times and store each value retrieved in a new variable. For example, In order to retrieve the customer's fullname i need to type in a regular expression that will allow me to grab only the piece of information that corresponds to that part of text that includes the fullname and make sure that it only grabs that and nothing more. Good news is that each registration form has a specific Field Name for each Input Field, and this makes it easy for us to locate each piece of information we need without hardly any trouble at all! We will only need to get the first match since we can only have one fullname per registration, there's no need in having the whole text being searched. And we store the match using a name for the variable that will allows us to easily understand what the variable holds. So, I will name my match %MatchCustomerFullname% to store the fullname of the registered customer within. I repeat the same action for each piece of information i want to get.
Sending the Information retrieved from the email to the Excel Worksheet
Once i'm done, I run the Launch Excel Action to inform the job which excel I want to use and I continue by using the Set Active Worksheet action to inform which worksheet i want to store the values i got from my email.
Get First Free Column/Row & Write to Excel Actions
In order to get the first free line in my worksheet so I can start sending in my values, I will use the Get First Free Column/Row value and as you can see we get two new variables ready to use (%FirstFreeColumn% and %FirstFreeRow%). Our final stage will be to start sending the information to the cells they belong, so using the Write to Excel Action, we choose to write to the %ExcelInstance% which is our excel file, the value that we want for example %MatchCustomerId% and that should go on Column 2 and as for the row, it should go on the %FirstFreeRow%.
The %GetFirstFreeRow% action runs once before we start moving any values to the fields, so once it knows which row it should write on, it will only use that row for all the values on that email (note that you cannot have more than one customer registrations in one registration form so there is no need to put a loop in this procedure)
Once we have repeated the Write to Excel action for all the values we want to save, we use the Close Excel Αction in order to close the excel file and also save it.
You can also check out and Parts I and III to get a clue about how jobs can connect one to the other.
PART I. Automating Registration Confirmation (Receiving emails from a website that requires confirmation of the user's email address, by prompting user to click on a confirmation link contained in the email to confirm details and registration)
PART III. Automating email replies and auto forwarding specific information from email's body to other emails (Receiving an order via email, sent from my business website order form. Sending the information found in the email's body to my Sales department to process the order, Sending email to the customer to thank him for purchasing the products)
Post new comment