Data scrape from emails

[dnorm]

Hi All

I am wanting to scrape data from emails to automatically generate an excel table.
i.e. I get one email stating "backup of storage 1 success", "backup of storage 2 failed".
these are from the same account to me i.e. backups@yourstorage.c...

Doing a simple "Record actions" will not do this task. so I am after help with getting the VBA started.

Thanks all!

 

[Kerryx]

any chance of an example of some data( dummy data) to work with, it also helps clarify the request, what you need v what you want. Is it just a single email with a block of data, delimiters etc or are you talikng multiple emails

 

[dnorm]

Hi Kerryx

The Email subject will always state (where "*" is a number):

"#TSK***** - New Support Ticket received"

The data comes in this format in the email body ("Success" is switched out to either "Warning" or "Failed"):

Ticket ID: TSK38553
Summary: [Success] Backup Configuration Job (1 machines)
Created: 27/11/2023 10:05:28
User: Backup
Priority: Middle
Impact: Workgroup
Urgency: Malfunction
Service Level Agreement: Tasks
Reaction Point: 06/12/2023 10:06:00
Solution Point: 15/12/2023 10:06:00
Status: New
Responsible Role: Support

I am looking to extract the summary data to compile the various states of each backup (there are 12 in total).
I intend on making a tab for each "machine" then have an email generate is one machine fails too many times in succession (that part I can do).

I hope this makes this easier to understand. I have not generated the sheet yet so "MYDATA.xlsm" in a C:\Temp\ would be great!

 

[Kerryx]

maybe use something like this

Power Automate

 

[Kerryx]

or you can use Power query which will allow you to transform the data before finally importing to excel, here is a decent walkthrough
How to Import Emails From OUTLOOK to EXCEL