Automating Your Microsoft Applications with RPA

Introduction to RPA and Microsoft Applications

Hello, everyone, and welcome to our webinar on automating your Microsoft applications with RPA. In today's session, you'll hear about many of Microsoft's key application technologies and how they can be automated in conjunction with RPA.

My name is Daryl Walker, and I am a solutions engineer here at Fortra, primarily dealing with our automation tools, one of those tools being automate, which we'll be looking at today.

We'll start the session with a quick introduction to Microsoft application automation. Then we're gonna walk through several potential Microsoft automation use cases that your organizations can consider when looking to streamline, you know, Microsoft applications and user workloads. We'll also give an introduction to Fortress Automate, our RPA solution, and also provide a short technology use case demo. If time permits at the end, we'll have a few minutes of q and a. If we don't get to it, we'll go ahead and grab all those questions and send some answers out at the end of the webinar as well. Feel free to enter your questions in the chat window as we go, and we'll address them if time allows at the end. Again, if not, we will send those out after the webinar along with a link to the recording here.

Understanding Microsoft Applications in Daily Work

So let's talk a little about Microsoft applications.

You know, we all use Microsoft application in our daily work lives. You know, most of us, that means we're really working with, Windows desktops or laptops, but we also probably use one of several other Microsoft Office or Office three sixty five applications in our day to day activities. You know, for example, we could be handling correspondence between other coworkers via Teams, maybe responding to emails, dealing with Excel documents, or giving a presentation or a pitch to the management team or doing a webinar like we are today.

Chances are you also have the need to automate some of these most manual processes that are worked on throughout the day. You know, as your workload increases, unfortunately, the number of hours in the day does not. So, ultimately, being able to automate your workflows allows your teams to better serve your vendors, customers, and your employees.

So how can we automate some of our daily tasks within this vast collection of software? Let's take a look at some examples for some of these various Microsoft applications.

Probably one of the top things that we help customers with is the automation of Excel file processing.

Often, we work with users who are performing manual copying and pasting information to and from CSV files or spreadsheets. May also be performing data cleansing activities, such as record validation or cleansing of data by comparing it against an existing database. They also be receiving spreadsheets that contain changes that need to be merged with other data.

So automate contains a built in Excel automation actions to process files without even needing Excel to be installed. No matter what type of CSV, Excel, or other data you're receiving, Automate can help to streamline the process of getting that data entered, validated, and processed efficiently without the need of keying or rekeying data.

You can use any of Automate's built in seven hundred activities or even repurpose existing Excel macros or use other automation techniques like VB scripting and ActiveX automation to create, you know, your Excel automation tasks.

Accessing automating databases such as SQL Server access to other databases is also very popular, especially when need to query or update data on the fly.

Using our database actions, you can write tasks to process data from SQL Server or just about any other database server, including Oracle, IBM I, MySQL, Postgres, etcetera. You can also tap into the power of existing store procedures when needed as well.

You may also consider moving some of the tasks that you're currently scheduling via things like your SQL agent in favor of automate so that all of your jobs SQL jobs can be, you know, easily scheduled and logged in one place. Notifications are sent automatically upon success and failure as well. You can also incorporate any of the other seven hundred plus actions to further enhance the capabilities of your database automations.

Email Automation and Its Benefits

We may also have the need to run SSIS packages for ETL or data exchange, maybe you have to run and schedule SSRS reports. So automate also has a trigger that can catch, insert, update, and delete notifications for SQL Server and Oracle databases that can also be used as a trigger to actually kick off automated tasks and workflows. Many of our customers also use Exchange, but also in ever growing or moving to Office three sixty five for email.

Monitoring email inboxes and capturing those messages is a great way to incorporate email into your automations.

Imagine a vendor invoice coming in being retrieved. You know, we OCR that information placed into a document management system, you know, process that all in one automation task.

Or perhaps, you know, we receive a help desk request. Maybe we're getting a password reset request that comes in. We can have that password reset, that new password distributed out to the user, and all that done automatically.

Automate has a real time mail trigger, which can be used to monitor and exchange mailbox in real time, trigger an automation task to run based off some set criteria, such as a sender domain or an attachment being available. You can then capture that inbound message and parse the data from it, including any attachments. Email automation really is a super strength for automate.

Automating User Onboarding Processes

Onboarding new users with active directory is a very common use for automation as well. So imagine being able to receive info on a new user from your HR system, have that information flow through an automated workflow to create that new user, assign it to any appropriate groups based off departments, and maybe create a mailbox, provision Office three five license, and then add the user to any other related applications automatically without the need for your help desk or IT team to intervene. So automate can help do this today.

The process could even create a help desk ticket and add some details in and then close out that ticket automatically for metrics and tracking purposes. So imagine how much time you can give back to your help desk and support teams by taking away some of these time consuming mundane onboarding tasks via automation.

OneDrive. So this is Microsoft's document storage platform that comes as part of the ops three sixty five subscription. It can be used to store documents in the cloud for easy access from any of your devices. When OneDrive is used with automate, you could simply set up a local network server folder that's connected to OneDrive, and as files arrive in your OneDrive cloud accounts, you know, they could sync automatically to your local PC or server, so Automate can process those. Once the files are processed, they could then be removed from OneDrive by deleting the copy or maybe relocated somewhere else by moving them to a new new OneDrive folder or someone else in your local network.

Leveraging SharePoint for Document Management

SharePoint is also part of the Office three five subscription. Many of our customers do use SharePoint on premise as well.

Imagine that you need to publish a scanned document or some daily reports. So today, you'd have to manually upload that document to SharePoint or engage your development team to do some custom project to perform that integration. So automate allows file and data transfers to happen seamlessly as reports and document files become available for publishing.

Document libraries can be used for publishing documents to a shared location or receiving incoming documents, and SharePoint lists can be used to send and receive data from database tables or online forms. And the best part of all this is that it's built in to automate SharePoint actions so that they're easy to use. And you probably don't need to have a SharePoint developer to anyone or anyone developing these to any of your doc pub document publishing tasks. And I've had several discussions with teams that already know PowerShell quite well and would like to continue utilizing those scripts within automates.

So the nice part there is that automate can be used to quickly put a scheduling, logging, and notification management wrapper around any existing PowerShell script. So, So, also, any of the thousands of individual PowerShell actions can be used within Automate to extend our seven hundred plus built in actions while also allowing you to pass information from Automate into your scripts and also return information from your scripts back into Automate. We don't force you to get the PowerShell if that's what you like to use for automation scripting. But once you've used automate, you'll find that often our drag and drop actions are much easier for most things you might be automating in PowerShell, such as making API calls.

Expanding Automation with Azure Cloud Services

Microsoft is continually expanding its cloud offering. So Azure is Microsoft's hosting platform for Windows or Linux virtual machines as well as file storage, active directory, cloud based SQL Server database access, and more. For our customers who do use Azure cloud, Amazon cloud, or any other public cloud, automate can work to automate some of the work on premise, fully in cloud, or in a hybrid mode to provide automation across your entire spectrum of servers.

Alright. So let's go and take a look at how RPA from Fortress Automate can help automate your various Microsoft tasks.

Automate is a robotic process automation solution used for building, managing, and launching custom business process tasks in Windows.

Task developers use drag and drop actions to build managed tasks, run on a scheduled basis or in response to event triggers.

Automate Windows tasks are built without writing low level scripts or code. And automate gives you all of the predefined steps for over seven hundred common tasks.

Use the actions to build tasks and then schedule the tasks based on triggers such as time, event log entry, startup, file monitoring, and much more.

Building Automations Without Coding

Automate is built to allow even those who are nontechnical to rapidly develop their own automations using our drag and drop form driven development approach. There doesn't have to be any coding involved when building out your automation tasks. There are a few key areas that allows us to achieve this rapid development. You can generally loop those into two categories, front of glass automations and backup glass automations. For front of glass automations, we have our UI automation. Think about any sort of task that involves working with a desktop based application or a web browser.

If you're going out to a website to gather data that needs to be recorded into an Excel workbook or a database. Basically, any sort of automation that mimics exactly what an end user might be doing on the screen, such as clicking on buttons or links within an app or a browser. We can quickly build out these UI automations using our step recorder. The recorder will automatically identify the elements on the screen that you're interacting with, captioning the information to include in your task for automation.

API Integrations and Data Handling

Everything else typically falls into that backup glass category or integrations. Think of any other sort of interaction that we can execute programmatically or without users involved, like working with a database or an API.

Automate has a very robust API integration kit. We can work with both REST and SOAP based APIs, supporting all the modern authentication standards such as OAuth two point o. We can even easily take any sort of API response payload and parse out that XML or JSON and grab any data that we need. Again, there's also over seven hundred native actions built into the platform today. These are prebuilt integrations into different apps and technologies, things like Excel, email, and databases that require no coding to interact with. They're all form driven, so you just have to fill in the needed fields to perform an intended action.

Automate can also read in data from a source, such as Excel file or database, store that data into a variable or dataset, then loop through that data, applying conditional logic and rules based processing to validate that data or form other tasks in your organization. And we'll at all of this in just a moment. We can kinda walk through a couple different demos.

Live Demonstration of Automate Task Builder

And with that, let's go ahead and jump into the task builder and build out a few examples.

Alright. So the first activity is going to demonstrate how Automate can easily manipulate and work with Excel files. And this is probably one of the top applications that we see being used in automations, and so many people deal with Excel files in their day to day work. In this particular example, we're going to be reading in data from an existing Excel file that has some sales data.

We can take a peek at that file here.

And what we're going to do here is parse out this data, and we're going to pull out only the rows that match our specified criteria. And in this case, we're interested in seeing is any sale that has a discount of greater than twenty five percent.

So our Excel actions are a great tool to automate a simple task such as this, where we need to take a large set of data, you know, parse it out, find something specific, and then either create a new Excel file with that data or even put that data into another system like a database or ERP system. So it's gonna walk through how we could build out a task like this.

So for those maybe unfamiliar with the automate task builder, fairly simple setup. What we're gonna be really dealing with for the most part today is gonna be our native actions, which you see listed here in this bottom left hand panel. So you may see those listed in a category view where you have to look through the categories to find the ones you need. I prefer to view it here, all actions in the same panel so we can simply search through and find what we're looking for. There is a search box at the bottom as well. So if I want to deal with Excel, I can simply type that in, and it returns any of the Excel actions here for me.

The first thing to do so, again, we're going to be taking an Excel file. We're gonna read in that data. We're gonna parse it out and find any of the rows in that file that contain a discount value greater than twenty five percent. So the first thing we do is open up that Excel workbook and read in some data. And for that, again, we're gonna use our native actions. We have our Excel native action category over here on the left. I simply drag that into this inner panel.

We see our action properties dialog box pop up. And here, we're just gonna fill in the needed fields for this particular action. And you see all those subactions here available on the left that we could also perform, we can click through these if we need to change which one we selected.

You see all different options, opening a workbook, getting cells, setting cells, running Excel macros. To start this off, though, we're gonna go and open an existing workbook. So I'll use our open create workbook action.

Choose open from the drop down, and we always need to give our things we're opening a file or making connection to a database, we're going to be giving this a session name. Typically, you can leave it as the default, but in this instance, as we are working with multiple different Excel files, to make it a little easier to keep the two straight, I'm gonna go ahead and rename this session. We're gonna call this first one, we'll just call it sales data.

And here we're gonna pick the file that we're interested in opening. We could mainly type this path in, but to make it even simpler, we click this little button here, and we're gonna go ahead and browse out to that folder and grab out that file. And by default, it's gonna look for, the main Excel file formats. We can simply click this drop down. This happens to be a CSV file, which our Excel actions can also work with, so we'll simply select that.

We could optionally specify passwords if we required those to open or modify that particular file. And And one other thing I'll point out. So when dealing with Excel, we have our Excel actions, but we also have our open document spreadsheet actions.

And these are pretty useful, one being that they can form a few different actions that the Excel action category cannot.

But they're also the benefit being that you don't actually have Excel installed on the system where this is running for these actions to work properly. So they don't require Excel to be installed and actually run a little bit faster.

So if you don't actually need the Excel application for your particular automation task, kinda best practice there, we need go ahead and use the open document spreadsheet actions just so you don't have Excel on there and that it does just run a little bit faster there in the background.

Alright. So we have our Excel file opened up. One thing that we're going to do with this particular task is we're going to be writing this information we're gathering back to in a new Excel file. So I'm gonna go ahead and create that here at the beginning as well, again, utilizing our open create workbook action. Instead of the open, we're going to choose create here.

And, again, give this a session name. This is, we'll just call this discount data since, you know, we're dealing with the data that's has the the higher discounts on it. And, again, specify our file name and path here. I'm gonna utilize our file explorer again. And just so I can grab that path because I'm kinda lazy here, and we'll just call it, discount data.

I'm going to put a time stamp on the front. I believe it's one twenty three for that, and we'll select there.

And we can also create this from a template. So if we wanted to create, you know, multiple Excel files, we had some template we wanted to use, we could specify that here as well. And we could also say, you know, if this file happens to exist, go ahead and overwrite it here for us, which I'm a go ahead and select now just in case it does exist here.

Alright. So we got our two workbooks created. The next thing we do is actually read in all of that data from our existing sales document.

Do that, we're gonna use our get sales action here.

As for the source, we're gonna choose by session. So the sessions we created previously here, we got our sales data session and our discount data session.

We want to choose sales data here, so we're grabbing that first work workbook that contains all of our sales information.

And then what are we gonna get? Are we getting a single cell by reference or position or a range of cells by reference or position? So I want everything in this particular document, so I'm gonna choose a range of cells by reference because that's how I like to think about Excel files. So, you know, a one, a two, b two.

We could do it by position where it's, you know, row start and column start and ends. Though it's tougher for me to think about, so I always choose to prefer by reference.

And then we're gonna create and populate a dataset with this information. So a dataset is just an object that exists with within automate to hold all the information that we're reading from this workbook in a tabular format, you know, in memory so we can work with it throughout the rest of the task. So nothing really to to get too fancy beyond that, and we seem to give it a name. I like to prefix my data sets with d s for data sets so I can easily identify, you know, what type of object they are.

And we'll just call this sales data.

We can pick our starting reference again here. We'll start at the beginning. We could pick our ends. If this is a static set of data every time this task runs, we could specify that end reference here, or we could say, auto check the end of this file. So that way it doesn't matter if it is a variable number of rows every time this particular task executes. It's always gonna grab the appropriate amount of data. And similarly here, let's go ahead and grab that first choice column name so we can use those as well.

All right. So we got our two workbooks open. We've read in our data. I'm actually gonna go ahead and close out that initial workbook right now, so always good practice to get into. Anytime you're creating a session, we are done with it, go ahead and end it just to clean things up after yourselves. You have nothing lingering in memory. So I'm a choose our sales data session, and I don't I'm not interested in saving it here, so I'm a say don't save changes.

Alright. So the next thing we need to do is to actually create a dataset here for the information that we're gonna read from the sales data file. So anytime you run into, you know, a row that has a discount greater than twenty five percent, we wanna capture that data, and we're gonna store it in dataset temporarily so that we can write that back to our new Excel workbook. So I'm gonna utilize our dataset action here.

And we use our create.

Give this a name. You know, we'll just call this, DS discounts.

And then here, we need to specify the column names if we want to have those on there. If you don't specify, I'm just gonna call them column one, column two, column three, etcetera.

We can just grab these directly from our existing workbook. I can copy these out.

And we can paste these in.

Paste them in pretty well. So if there's space delimited here, so I'm gonna change this delimiter to a space instead of comma because I'm lazy. I don't wanna change those to commas.

And now we can close that. Close out our workbook here.

Alright. So we got our dataset created now, initialized here with our column headings.

So to kinda keep going here, the next thing we're going to do is actually to loop through all of the data in our sales workbook. But before we get that far, I need to actually go ahead and run this task, and that is to go ahead and populate these datasets with all of their various fields. So anytime we're creating the datasets, we'll need reference any of the various columns within those. We need to go ahead and run through the task once you're in the task builder so that it knows what they are. It can read those in. So I'm gonna run this with the exception of this step here.

We don't need to create our other workbook, but let's go and open up reading our existing data here and just see all that information.

So open up our file. It should create that dataset for us and then close out this workbook here.

Alright.

So that should do that. So as I mentioned, we're gonna loop through this data. So we have a number of different looping mechanisms that we can utilize here within the task builder. So we see our loop category down here.

I'm a drag a loop in, and you see all different loop types that we can utilize. So we can loop through our range or maybe looping through, you know, set up numbers, some index that we're utilizing to to count through things. We could use an expression where we're seeing, looping till something is true. In essence, though, we're doing a dataset loop.

So I'm interested in looping through every row of this sales data from that Excel workbook. So every every row we go through, we're gonna perform some subset of actions on. And I'm gonna uncheck these boxes so we can loop through every single row.

And then for each row, what are we gonna do? Well, we need to test to see if that discount field is greater than twenty five percent. So to do that, I'm gonna utilize our if statement here. So grab an if.

And I'm gonna utilize a condition. And what are we gonna test for? We're gonna test to see if that discount column is over twenty five percent. So we can utilize our expression builder here to access that column.

We see our local variables folder here where we see that discounts dataset here, all of its fields, our sales data dataset with all of its fields. If you got any variables, they would also show up here. So I'm gonna grab our discount field, insert that, and then we wanna see if it's equal to. We wanna see if it's greater than, and the value here is zero points two five.

And we need to put this in quotes so that it recognizes, the value properly here when it's evaluated.

Alright. So now that we see we're testing to see if it's greater than point two five, what are we gonna do? Well, we're gonna capture that data, and we're gonna insert it here into this dataset that we created. So I'm gonna grab our dataset actions again.

And we're going to do an insert row action into our discounts dataset. And we're gonna insert this at the end. You know, we could utilize an index to put it in a certain location, but here, every time we loop through, we're just gonna pop it at the end of this dataset. And then what information we're putting in the datasets?

Well, we can use our load columns here, which pulls all those columns that we added into the the the column headings here when we created it. And then for the values, we need to specify the values from that particular row of the dataset sales data here. So we can utilize our expression builder again. We see all of our column names, so we'll grab the appropriate values here by accessing that expression builder.

So we just need to go down to our sales data, and that first one was sales ID. So we'll select that.

Customer ID.

Product ID.

We have a date.

Our quantity, the unit price, the total price, the discount value that we were interested in seeing, the payment method, and lastly, the sales rep.

Alright. So we have a lot of fields filled in here. We can click okay.

And then now that we have all that information we looped through, we've grabbed any columns or, excuse me, any rows that are, you know, greater than point two five in the discount field, added into our dataset. We need to write that back to our Excel file. So once again, let's grab our Excel actions, use our set cells here, choosing our correct sessions, we're interested in putting in that discount data session.

Source, again, is a dataset. We're gonna write a dataset back. We could write a single value back. We could write a list of values back. We wanna write that DS discounts dataset back to this file, again, by reference into a one, and we're gonna use the column names as well.

And, again, last thing, let's clean up clean up after ourselves. Let's close out that other workbook.

So we'll close out our discount data, and we're gonna save it on completion as well.

Alright. So now if we run this, it should go ahead and pop open both of those workbooks. It'll read in that data. It's gonna loop through it and grab any row where the discount is greater than point two five, and write that to a new workbook.

So let's see. Oops. I think I had my other file open. Let's try this one more time.

Slide this over to the side here so we can kinda see the status and the progress here back in the task builder. So we can see each of the steps as we get through them. So we see the new file open, the sales data file closed, and now it's looping through all of those fields or all of the rows from the initial dataset, finding the ones that are greater than point two five and writing them back to this new file. So as you can see, very simple process as far as, you know, taking a larger set of data, looking through it, parsing it out, you know, and writing that data back to either another file. We could also just as easily wrote this into a database or send it to some other third party system.

Alright. So that kinda completes the task number one here with, Excel to Excel data parsing.

The next activity that we're gonna be working on is gonna show, excuse me show how we can query data from a database.

And, you know, something like SQL Server and capture that data into an Excel file so that we could, you know, potentially further manipulate like we just did or perform some other actions on.

So again, you know, fairly common practice where we're taking data from one source and then moving it to another source.

Here at the top, you see I'm creating some variables. This is something I didn't actually do in the previous example, but something that's good practice to get into. So anytime we are doing things like referencing a file path or a file name or we have something like this, a list of items that we need to maybe loop through, good practice to get into is to create variables here at the top for those. That way when you reference them down in your actual activity, you know, you if you have to change something, you have one spot to change instead of having to search through and find all those various references and update them individually.

So a good habit to get into, create your variables here at the top. So we have our file path. I have a list of states. So this is just a line separated list of of some couple couple states here.

Have variable to hold the current state we're gonna be looping through.

And then I'm gonna establish a connection to a SQL database so we can query some data. So I'm gonna utilize our database actions here. And this doesn't have to be a SQL database. Again, this could just as easily be any other ODBC connected database, Oracle, whatever, what have you.

As far as the open SQL connection action, we have a couple options here as how we can make this connection. If we choose database, we can create this connection right here within this action. We could also utilize a connection string, a data link file, or a predefined connection, you can actually create these connections in the automates management console ahead of time and just specify them from the list here.

Have this connected directly within this particular action. So you would click the build connection here and choose your provider first, and then enter in your server name and instance, your authentication information, and you could select the database from your list here. Test your connection. Make sure everything works properly, and then you're kinda good to go.

Again, give it a session name. Since we're only working with one database session, I'm a leave this as the default here.

In this instance, we're gonna be looping through a list. So we're gonna loop through that list of states here, and we're gonna query some data from our SQL Server for each of those. So I utilized here. See all of our looping mechanisms.

Again, we have a list loop. So, again, utilizing that variable of our states, so the three states we had listed there, I'm gonna loop the reach. And for each iteration, I'm gonna take that state and populate that variable that we created previously with the current one in the list. And just the delimiter here again is that new line, from our list action.

Then we're gonna utilize a SQL query action. So we have the ability to run, you know, any type of SQL query directly from, automate as well.

Again, utilizing it by session, so our connection here is our session we've created previously from this list. And then really just putting in, you know, any common transact SQL statement just like you would have put, you know, in your SQL management console. So we have a a select statement here. We're grabbing a bunch of information from our user data table where our state equals that current state that we're interested in that we have that we're looping through.

So we're gonna loop through each of these and populate any matches into a dataset. Here, we have our DS people dataset.

And for each of those queries, we're gonna go ahead and open and create a new workbook. So you see same thing we saw previously. We're creating a new Excel workbook. I've utilized my variables here from my path, and I've incorporated the state variable into my actual file name.

So it's gonna create a new file for each of the different states. So we have all of the different customers in their own workbook. And I'm a create this my template as well. So I've selected from a template here, selected that template again utilizing that file path variable and overriding if it already exists.

And similar to the last one here, we're gonna set that information into that workbook, again, choosing our session we created above. Putting ting in a dataset, DSP people, and into that top a one corner there.

I've gotta wait in here just so we can see this as it's running so it doesn't close the workbook too quickly, but I do close out that workbook once it writes to it, and then we loop through for the other states in that list. And once again, when we're done, we're gonna close out that SQL connection there.

So let's go ahead and run this, and we can take a look at how this operates.

So looping through here, it's gonna create that first workbook after it performed that SQL query.

See the workbook opening up, and it's writing all of that information in, and it's gonna close that out and proceed on through our loop for the next state, which is California.

And then it'll perform the same actions for DC.

And if we take a look here at our folder here, we see all three of those files that were created, and there's a template file as well that we utilized.

So successfully created all three of those files, taking them from our data from our database source and creating a bunch of various Excel reports with that data that we could then, you know, maybe email out to email out to people, send us some other know, third party if need be.

Okay. Activity number three here is gonna showcase, how we can get an email and its contents from an exchange mailbox, capture data from it, and then send that information to another system. A good example is maybe you have a mailbox where you receive invoices. You want to ta that task to kick off. Anytime an email shows up with an invoice, grab the invoice, parse up your data, put that information to some third party system. So we're gonna show just how we could do that with our exchange and OCR actions.

So common to the previous couple, activities here, we're creating our variables here at the top. I've got my secret for my exchange connection, some variables to hold the information we're reading from our invoice files, and then our datasets to hold all that information as well from, from those invoices. So we're gonna be creating a dataset and putting that into an Excel file like we have done for the past couple, activities here. So we see that dataset. Again, just comma separated delimiter here for our column headers that we've specified.

And then we're gonna create that exchange session. So we're gonna utilize our exchange actions here, our create session subaction. You can see we support web dev, exchange web services, or exchange web services online for Office three sixty five. Again, we're making connection. We're gonna give that a session name. But since we are just making this one session, I'm leaving it as the default again.

Specify the mailbox we're interested in monitoring here, so we're gonna specify mister Alex w's mailbox here and then our connection information to our Office three sixty five tenant.

Optionally down here in the advanced section, we could go ahead and say we're looking for a specific folder. In this instance, I'm just curious with for the inbox itself. Maybe you have a particular folder that these emails may arrive into that you wanna monitor or query, you would specify that here.

Alright. So now we're going to utilize our exchange get objects action. So, again, picking it by session, you can choose that session we created above. For the objects we're gathering, we are gathering emails here, but we could also gather appointments, contacts, or tasks. And the way we're doing with the information, again, creating a dataset with it, DS emails.

And here's how we're gonna filter this out to only grab the information that we need. So we have the ability to not just grab every email in that particular folder. We can actually query for specific things within there. So we can utilize either our custom filter approach here, or you could utilize the exchange message query language there to query for certain things.

If you're just something simple, this is the easiest way to go. You can just simply click add.

We have some common, properties that we can utilize here, things like our subjects, to fields, you know, if it has attachments or not, and then some operators here. Does it contain, not contain, equal, less and greater than?

So here, I'm interested in emails that have a subject that contains the word invoice and that it has an attachment. So has attachment equals true.

And for any attachments, I'm gonna save those here into my email invoices folder, which you can specify and, again, override anything that may already exist there. And because we're done with our exchange session, we're gonna go ahead and close that down. Again, good practice. Let's clean up after ourselves.

And now for each of those invoice files that we have downloaded or any of the attachments that we've downloaded, we're gonna again loop through those things. So we have, again, in our looping actions, we have a files loop. So we can specify, in this instance, a folder of files that we're interested in looping through.

And the nice thing about this is you can actually utilize wildcards here, so we don't have to loop through everything in the folder. I can say only loop through PDFs or only loop through files that have this certain file name prefix on them. So you can kind of utilize wildcards here to narrow down what you're interested in looping into.

And then we're gonna populate a variable with the current file name that we're on in our loop here in our VAR file name.

And the mode here just indicates what we're looping through. Is it files? Is it folders? Is it files and folders?

We could pop you a dataset if we need to as well. I don't need to in this instance. And then we have some options here. We could utilize an exclude mask to exclude certain files again. We could use a regular expression for that if need be, or we could even say, you know, only grab files that are newer than a certain date, older than a certain date, or in between these dates here.

And for each of these files, we're gonna utilize our OCR actions to grab some text from them. So here we see our OCR actions, so optical character recognition, grabbing essentially static dot text and data off of a page.

So I'm gonna use our get text action.

The engine itself, typically, I wanna use Tesseract. That's the newest one.

Picking our file name here in this image section. It doesn't have to be an image. This could just as easily be something like a PDF here that we're gonna utilize today.

Just to show how this works, though, I'm gonna grab a file that we can take a look at.

Wasn't you doesn't like the the variable there since it's not initialized with the value yet. So we're gonna grab an actual file here we can look utilize to look at. So we click our pick region there, and this is gonna allow us to actually choose the region of the file we want to grab the text from. So pretty straightforward process here. Just to click and drag a box around the area that we're looking for. So if we wanna say grab the vendor from the top, we can simply grab that here.

We see the preview here in the Right side. Looks like I grabbed that A little dot there at the end. So gotta be kind of careful where you're grabbing. Also, you know, if you have a very a third generation scan of some documents, It's gonna be a little hard for it to read, so you always wanna make sure you've got nice clear text.

So something like what we're looking at here where we have a pretty legible text here that's not too small, very easy to see. We get some nice nice results here. But I'm interested in grabbing the vendor here, so I could grab it from, again, the logo here at the top, but oftentimes logos are a little bit more difficult to read. Better place for something like that is down here in our remit two section where it's a little bit plainer to read there, we can specify that region.

And the same thing here for our total and our invoice number, so the three things we're interested in getting from each of these invoices. And for all that data, what are we gonna do with it? Well, we're gonna again put this into a dataset. So I've created dataset here. We created the dataset above, excuse me, our invoice data. And, again, here is those column names, and the values we're inserting into those columns are the three variables that we created that we captured from our OCR text.

So, again, looping through all those files, capturing all of that data, and then, again, creating a workbook here for all of that invoice data And writing that dataset of our invoice data to that workbook, again, in cell a one.

And then we're gonna close and save everything out.

So let's take a look at this particular task in action.

Again, creating that exchange session. It's gonna query for any, emails that match our criteria of a subject with the word invoice and has an attachment. Grab all those attachments down, And then OCR through all of those. So if we actually take a look at that particular folder, We can actually see in our invoices folder here, all of the files that downloaded.

So you see our attachments. And one thing about, grabbing attachments, it does tend to grab, things from your, like, your signature, so images. You know, people tend to put images of their logos or, you know, like, link the little LinkedIn picture in there. So it'll grab those as well.

So that's another reason why we may have wanted to utilize those, you know, like, the star dot PDF so that way we don't try to accidentally OCR on something like this in here.

But you saw there, I did grab all of that data out. And put that into our document here for us, so which again it saved here in that same folder.

So very simple way to utilize exchange within, you know, your automations as well. Be able to take action on incoming emails that may arrive, or potentially just query a mailbox for a certain set of criteria.

Chapter
Automating File Uploads to SharePoint

Alright. So the final activity that we're going to work with today is going to deal with SharePoint. So if you're not familiar with SharePoint, a lot of functionality within it. It's part of the Office three sixty five subscription.

Common task or common thing that companies use it for though is for a document library. So a shared place where people can upload and download files that that they may need to share with other people in the organization. So here in this example, we're going to actually upload files from a local system into SharePoint automatically.

So once again, we've got our variables here at the top. So I've defined my site URL. I've defined my local file path so the files we're interested in uploading to SharePoint. And, again, I can utilize wildcards here, so I'm only interested in uploading PDF files.

The archive file path. So I'm going to move these files, from the local folder to an archive folder once they've been uploaded so we don't accidentally upload them again or know that they have been uploaded. And then the actual document library that we're going to move them to.

So, again, variables created here at the top, and then we're gonna utilize our SharePoint actions here to first create our session. So a common theme we've seen here so far, and then we're gonna be going and creating a session here to our SharePoint site.

So the version here at the top, we support SharePoint online or SharePoint's on prem.

And specify our site URL, which we defined earlier in our variable, and then all of our authentication information to authenticate to our SharePoint instance here.

So now we've created our SharePoint session. Let's go and upload those files. So here, we're gonna reference our session again utilizing our upload files action.

And we have the option of uploading either a single file or multiple files here. So once again, we can utilize wildcards like it specifies here at the top. So I'll use that local file path where I said to move anything that is a PDF file and moving that into our sales documents document library, and we're gonna go create a dataset in this instance to capture all of that upload information just in case we need to troubleshoot anything.

And, again, we have some file options here, we could put in some exclusions, either by mask or regular expression or by date time stamp. So being able to, again, manipulate what gets uploaded there if we need to.

And I am gonna move these files once they have been uploaded, so we will choose to move these files with our file system actions, moving them from that local path to that archive path which we specified again in our variables.

And, again, clean up after ourselves. Let's end that SharePoint session here. So if we wanna take a look at first the folders itself, we can look at our SharePoint folder. You see we have some PDF files in here, some text files. Here's our archive folder that's currently empty.

And if we take a look at our SharePoint site here, you see we have the SharePoint site. Does have some of those daily reports in it already. So let's go ahead and run this action or this task and take a look at these files get moved.

So again, creating our SharePoint session here, uploading those files up to SharePoint, moving those files from the local path to the archive path, and we can see here the progress as it goes.

Alright. So first, let's check and make sure the files moved. So as you see, it did move all of those PDF files. It did not touch those text files that existed, and now those are all in our archive path.

And if we take a look at our SharePoint sites, we see those monthly reports. It went ahead and uploaded and ignored those existing daily reports that were already there. So, again, great use case as far as being able to utilize SharePoint within your automation tasks as well.

Conclusion and Q&A Session

Alright. Well, I think that's essentially kind of wraps us up for the demo portion of this. I think we went a little over here, so for any questions that are in the chats or anything that you'd like to leave the question, we'll go ahead and collect those and send those responses out to the group and send a follow-up email along with a recording of this particular session.

So I just want to thank you for coming to this webinar, and I hope you guys learned something new today. Thanks a lot.