How to take email response and enter into Excel

Help
1

Hello,

In my automation, emails are sent when invalid or missing account information was submitted through company’s website. The email contains a “VSA” number that was assigned when the entry on the website was created. The email requests that the entry creator reply back with the correct account and cost center in the example format: 123456789/98765432.

The issue I am running into is how to extract the account and cost center, then enter this data on the excel document in a specific column for the specific VSA number. I am unsure if the requestor will reply back with additional text, for example “Please see below. 132456789/98765432” I need the automation to extract ONLY the account and cost center information, regardless if the entry creator replies with additional comments/text.

What I need this automation to do is:

  1. Read email replies to find VSA number and the account and cost center from the reply
  2. On Excel document, on the “Pending” tab, filter to the VSA number from the email and enter the account and cost center information in column M, “Reclass Expense Acc.”

If there is another information needed please let me know. Thank you in advance for the support of this community!

2

After completion of read emails you will be having list of mail messages.

  1. Use for each item in mail messages and get the item.body(body of the email)
  2. Use regex to match vsa number d+/d+
  3. Use append range in excel/write cell Passing with cell values…
2 Likes
3

Hi @Divyashreem,
Sorry for my delay in response as I am just now getting to your suggestion. I am struggling with a couple things I would like to ask for further explanation and examples of.

  1. How do I set up the for each item to only pull the account and cost center from the body of the email assuming it would be in the example format: 123456789/87654321 (account number will contain 9 numbers and cost center will contain 8 numbers)
  2. I am really struggling to understand the regex activity. Could you please provide an example of how to use it in this situation?

Thank you for your support! Look forward to your feedback!:grinning:

4

Never mind! I was able to figure out how to accomplish both items.

  1. Get Outlook Mail Message → firstResponse
  2. For each mail - firstResponse
  3. Assign: emailBody → mail.Body.Tostring
  4. Matches (account & cost center): input–> emailBody Pattern → “\d{9}.\d{8}” result → replyGLCC (IEnumerable match)

5.Matches (VSA number): input–> emailBody Pattern → “VSA[0-9]\d*” result → VSAemail (IEnumerable match)
6. Write line: replyGLCC(0).value & VSAemail(0).value

Next I will need to get the info into Excel so I may have some more questions. If you have any other tips or more efficient way please do share!!

Thx,
Mel

2 Likes