Automating Your Microsoft Applications with RPA

A complete transcript of the webinar is below.

00:01: Welcome everyone, to our webinar today on how to automate your Microsoft applications with RPA. I'm Pat Cameron, and I'll be the presenter for today's webinar. Today's session is going to take us a little deeper into the wide world of Microsoft application automation. In today's session, you'll hear about many of Microsoft's key application technologies and how they can be automated in conjunction with our Automate software. As I said, my name is Pat Cameron, I'm a director of automation technology here at HelpSystems. I've been working with the Automate software for about six years now, I've been with HelpSystems about 20, and I've worked with a lot of our different product lines.

00:49: This is our agenda for today, and we'll start the session with a quick introduction to Microsoft application automation, then we'll walk through several potential Microsoft automation use cases that your organization can consider when looking to streamline Microsoft applications and user workloads. We'll discuss some of the Automate capabilities, and we'll also provide a short technology use case demo as well. If time permits, we'll end with a few minutes of Q&A. Feel free to enter your questions in the chat window as we go, and we'll address them towards the end of the webinar.

01:26: I plan to complete this session within about 30 minutes, might be a little bit longer, 35 or 40 minutes is what it's been running, so hopefully you have plenty of time to get to your next meeting. Also, today's event will be recorded, and you'll receive a link after the webinar to be able to share with anyone in your organization who might be interested, but wasn't able to join today's session.

01:51: Microsoft. We all use Microsoft applications in our daily work. Of course for most of us, that means Windows desktops, and then we probably use one of several Microsoft Office or Office 365 applications for handling correspondence, responding to email, dealing with financial documents, giving a presentation or a pitch to the management team, or even doing a webinar like I'm doing today. Chances are, you'll also have the need to automate some of your most manual processes that you work on throughout the day. As your workload increases, unfortunately the number of hours in the day does not. Ultimately, being able to automate your workflows allows your teams to better serve your vendors, your customers, and employees.

02:42: Let's take a look at some of the use cases for Microsoft software. Probably one of the top things that we help customers with is the automation of Excel file processing. Often we'll work with users who are performing manual copying and pasting of information from maybe a CSV file to spreadsheets. They may also be performing data cleansing activities such as record validation, or cleansing of data by comparing against an existing database, or they may be receiving spreadsheets that contain changes that need to be merged with other data.

03:21: Automate contains built-in Excel automation actions to process files without even needing Excel to be loaded. No matter what type of CSV, Excel, or other data you're receiving, Automate can help to streamline the process of getting the data entered, validated, and processed efficiently without keying or re-keying of any data. You can use any of Automate's built-in 600 activities, or repurpose existing macros and other automation techniques, such as VB scripting and ActiveX automation, to get the job done.

04:00: For correspondence documents such as letters and notifications, Microsoft Word is usually the go-to tool of choice. Imagine being able to automatically take a list of 100 or 1,000 email addresses and send a personalized mail-merge letter to each one at the push of a button, or on a regularly scheduled basis. By automating Word document creation, distribution, printing and more, you can save your sales, customer service and other teams a lot of work. And just as with Excel, you can re-use existing macros through scripting languages as well.

04:38: Microsoft Database Engine has been around for a long time, and is often under-utilized. This data engine can be used to create, update, or query Access databases for information on the fly. One of the cooler features of this database engine is the ability to also use SQL statements to query and sort spreadsheet data on the fly. A great use case for this might be if any table data is stored in an Excel file and needs to be checked regularly, and users don't have a data entry application.

05:11: I like using Access databases for temporary work files, log files, encrypted setting files, and more. The Microsoft Data Engine can also be used with or without Microsoft Office installed. You can just Google Microsoft Data Engine to find the 2010 or 2016 runtime installation executables for both 32 and 64-bit modes, and then you'll be able to use all of our examples. You may also have applications written in Microsoft Access that you need to run. Often, Access apps are called from a command line to run processes, or maybe generate reports. Running Access via ActiveX automation or existing VB scripting is another way to fully control the running and execution of Access applications and processes.

06:08: Accessing and automating SQL Server and other database actions is very desirable when you need to query or update data in a network database on the fly. Using our database actions, you can write tasks to process data from a SQL Server, and just about any other database actually, including Oracle and IBM i, MySQL, Postgres, Salesforce Dynamics, et cetera. You can also tap into the power of existing stored procedures when needed as well.

06:39: It might be time to consider retiring the SQL Server scheduling component in favor of Automate, so that all your SQL jobs can easily be scheduled and logged in one place. And notifications are sent automatically upon success if you want to be notified, or absolutely upon failure. You might also have the need to run SSIS packages for ETL or data exchange, or maybe you want to schedule and run SSIS reports. Automate also has a trigger to catch insert, update, and delete notifications for SQL Server and Oracle. Automate can help round up and monitor all of your database activities in one place.

07:24: Many of our customers use Exchange, but an ever-growing amount of customers are moving to Office 365 for their mail servers to move email processing offsite. As part of that process, selected emails still need to be kept as permanent records, and mail data probably needs to get backed up. Monitoring email boxes and capturing inbound messages is a great way to get out of the business of mailbox monitoring.

07:52: Imagine a vendor invoice coming in, being retrieved, OCR'd, and placed into your document management system and processed all in one step with Automate. Or perhaps receive a help desk request, such as a password reset request, and have the reset and the password distribution done automatically. Automate version 11 has a realtime mail trigger, which can monitor an Exchange mailbox and capture inbound messages, and filter the messages so that they get processed as quickly as possible, and then moved to a saved inbox after processing for archival purposes. Email automation is kind of like a superpower for Automate.

08:37: Well, it's not very often that some customers need to automate PowerPoint processing, but in my case I create a lot of slide decks on a regular basis, and automation could come in handy. Imagine being able to select a couple of presentations and then automatically merge all the selected slides from a template deck into a combined presentation for sending to a new client or a prospect as a PDF. Using PowerPoint automation can help you to do this type of work and more using the built-in PowerPoint ActiveX automation and macro functionality embedded into an Automate task.

09:20: Onboarding new users with Active Directory is a very common use for automation. Imagine being able to receive info on a new user from your HR system, and then have that information flow through an automated workflow to create a new user, assign the user to appropriate departments, create their mailbox, provision an Office 365 license, and then add the user to any other related applications automatically without any intervention. Automate can help you to do all of this, and in the process can help create a help desk ticket, add the details to it, close out the ticket automatically so you've got metrics for tracking purposes. Imagine how much time you can give back to your help desk and support teams and department managers with onboarding automation. We have lots of customers that are automatically onboarding new employees.

10:18: In addition to Microsoft automation, moving files around the network and maybe between trading partners is a very common use case that we see with almost every customer. Imagine being able to securely send encrypted data files to trading partners, being able to decrypt incoming files automatically as well. Or move those files around on a local network, and process them line by line before archiving and deleting them once processed. Whether you have files that live on a local server or PC in the network folders, or on systems like a mainframe or an IBM i, Automate has built-in file processing and encryption actions to automate those processes as well. Automate also has a file system trigger that can be used to trigger processes as files arrive, are created, or changed from your local network drives.

11:14: OneDrive comes as part of the Office 365 subscription, and every user is typically provided an account with 100 gig of file space. OneDrive can be used as document storage that spans devices, because it can automatically sync files between the cloud and your local PCs and servers, as well as to a mobile device like an iPhone or a tablet. When OneDrive is used with Automate, you would simply set up a local or network server folder that's connected to OneDrive, and as files arrive in your OneDrive cloud account, they get automatically synced to your local PC or server so that Automate can process them.

11:53: Once files are processed, they can be removed from OneDrive by deleting the local OneDrive file copy, or relocating by moving the file to a new OneDrive folder or somewhere else on the local network, which would also cause the file to get removed from your OneDrive account. One thing that's not talked about much with OneDrive and other cloud offerings is the fact that virus-infected files can easily be synced between multiple devices, thus destroying all of your data copies on every device, so always make sure to back up files that are important to you, whether they are on-premise or in the cloud.

12:32: SharePoint is also part of the Office 365 subscription. Many of our customers also use SharePoint on-premise. Imagine that you need to publish a scanned document or a daily report, and today you have to manually upload to SharePoint or engage your development team to do a custom project. 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. SharePoint lists can also be used to send and receive data from database tables or online forms.

13:15: In a few minutes, we'll talk about using SharePoint as a possible data repository for a Microsoft PowerApp mobile application, and you'll see where this might be useful for mobilizing data. The best part of all is that the built-in Automate SharePoint actions are easy to use, you probably don't need a SharePoint developer for any of your document publishing projects. And we have an example of SharePoint document uploading that I will show you shortly.

13:46: While PowerShell does have a lot of functionality, it's probably one of the oddest scripting languages I've seen. PowerShell is built on top of the .NET Framework, which is good, but the syntax does not resemble any base .NET programming that I've ever seen. The nice part is that Automate can be used to quickly put a scheduling, logging and notification management wrapper around any existing PowerShell script. Any of the thousands of individual PowerShell actions can also be used within Automate to extend our 600-plus built-in automation actions to be several thousand. We don't force you to give up PowerShell if that's what you like 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 that you currently might be automating with PowerShell, but they do certainly work together.

14:41: Also, Microsoft is continually expanding its cloud offerings. 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 are using Azure cloud, Amazon cloud, or another cloud, Automate can work to automate work on-premise, fully in the cloud, or in a hybrid mode, to provide automation access to your entire spectrum of servers.

15:18: Let's take a look at some of Automate's capabilities, kind of in general. So we've just seen a lot of examples of using Automate with Microsoft products. Keep in mind that the RPA capabilities of Automate are very flexible, and require no coding to put into production. We talk about back of glass tasks, we're referring to things like file transfers, database actions, monitoring inbound email and executing web service requests, they can all be done in the background. Because they are done in the background, you can execute multiple tasks simultaneously on the server where Automate is running. We have customers running thousands of tasks every day.

16:05: [inaudible 00:16:05] front of glass tasks in the center are typically mimicking what a human does. Maybe log into a website, point and click, find an object on the screen, enter data into a field. If the application that you need to automate has no type of API that can be used to interface with it, Automate can always use its interactivity for automation actions. Automate is kind of the great integrator. It can integrate two separate applications using any number of functions. As I mentioned, Automate supports many types of API, REST API certainly is very common today, but if you have an application that does not have an API available, there's always the standard RPA integration of recording the mouse clicks and that mouse movement.

17:00: Some of the terminology used within Automate. A dataset. When a list of data is created from a file or directory listing, or a database query, an Excel file, CSV file, email inbox listing, the results are returned in a list format called a dataset, and that can be used to process each record. Think of a dataset as an array of all the data you just requested from the source. Once you've received the dataset results, you can process that dataset individually, record by record.

17:38: For example, for a directory list, the dataset might contain file names, file sizes or directory names. For a database query, the dataset will contain all the records you just queried, along with the individual field values. For an Excel file or a CSV file, the dataset will contain a list of records read in from the file, similar to a database query. And for an email message list, the dataset contains a list of the email messages and their properties for processing.

18:11: Email usually consists of the from, to, CC and BCC addresses, subject line, body text, and any attachments for that email, and you can selectively choose to process all the records in a dataset, or filter just on specific ones. For example, in an email monitoring task, you might want to only process password reset requests or server failures within a particular task. You can filter and select only the messages you want to deal with in an automation task.

18:43: A loop action is an important action used to iterate or spin through the record results that are stored in your dataset, and you'll see that in the examples when we go online in just a minute. Quite often, a loop will process all records in a dataset until there are no more records. Other times, you might be selective as we described in the previous slide. You can use conditional logic to accomplish selective processing within a loop.

19:13: And then by using conditional logic, you can make your tasks and workflows really smart. Using if-then, else statements and select case end select statements, you can make important decisions within your tasks. Decision making can be made within a loop, or anywhere within the task, allowing you to easily implement task decision making as needed to help your processes flow correctly. You can also call sub-tasks from within a task to perform additional business logic that you might have modularized. Make sure to take full advantage of the business logic available within Automate when you start creating your monitoring automation workflows.

19:58: So let's take a quick look at the requirements gathering process you should consider when starting to create a new automation task, or starting to automate a process. This requirements process is the same for Microsoft automation or any task that you want to automate. We always tell people, just create a simple bulleted list or an outline, it doesn't have to be fancy, but you want to document all of the steps for a desired automation task. In other words, what does the human doing the work do today?

20:34: Based on some assumptions and talking it through, you have to determine, can a chosen task be systemized for automation? For example, can task inputs be driven from, for example, an email message, a help desk ticket, a file or a database, where does that input come from? What kinds of decisions have to be made in the task, and does the task need manual input most of the time or not? Importantly to ask, is the process consistent, is the process predictable, and is it repeatable every time? If so, then chances are the process can be fully automated, whether it's an email task, a SharePoint task or Excel, or some other automation process.

21:24: All right, let's go online and take a look at Automate live. So what we're looking at here is our workflow. All those gray boxes are individual tasks, they might be Microsoft automation or some other type of a task, but what you can do is you can take those individual tasks with all their steps enclosed in it, put them in a workflow, and then process them across multiple servers. As you can see, we have dependencies here, I'm waiting for... These are our file triggers on the left, so I'm waiting for files to come in, and as soon as those files arrive and get created, I'm going to go ahead and run that task.

22:09: I've built some logic into my workflow here where I need to wait. Before I can update the sales system, I need to wait until all three of these files have arrived, so I can build that logic into this workflow. You can also see here if I do have a failure, I can notify the help desk immediately so that they're aware of a problem. So this is kind of the big picture where you can build a workflow, and again run it across multiple servers if you need to, and have your tasks run in the proper order every single time.

22:44: Let's take a look at some individual tasks. So here we have a task that will read a database table, and then it's going to write to Excel. So this is our task builder, this is kind of the basics or the guts of Automate, actually. This is where you actually build those individual steps that you're doing now into an automated task. So on the screen over on the left side, we have all of the actions that are available to you.

23:22: Let's say if I'm going to use the Excel action, I can take that and I just drag it over here to this work area, it will open up a template for me, and then I select which of these actions do I want to perform? I can open an existing or create a new workbook. Here I can get the value of cells, and I'll go through some of my tasks and show you some of these. Set the value, so I can read or write to a spreadsheet. So again, you don't have to do any coding, everything is done with drag and drop within Automate.

23:58: So in this task that we have, it's going to loop through a Microsoft Access database, and then it's going to write those contents out to an Excel spreadsheet. As you can see here, you can put comments into your task so other people know what it is that you're trying to do, here we can add variables or add comments very easily. So our first section here is our declaration of variables that we'll be using, and so here we've defined the location of files, using variables makes your tasks very dynamic, very easy to change those values. Also, the location of the database that I'm using, and the connection string to connect to that database, I've just got those stored in a variable, and I'll show you where we're going to use those in our task.

24:44: So the first thing that we're going to do in the main part of the task is I'm going to create a folder for this output, and just to show you, so I've run this task a million times, and if I get an error because this folder already exists, I can decide to just continue on to the next step. So you can absolutely build some error recovery into your tasks at that step level, depending on what it is you want that... How you want that step to react.

25:15: So I'm going to create a folder, and then using the OpenDocument spreadsheet action instead of using Excel, so I don't have to have Excel loaded on this server, I have all the actions that we can do in Excel as well, so I'm going to create a new spreadsheet that I'm going to write to. And in my case, I overwrite it if it already exists. And then the next thing I'm going to do is I'm going to establish the connection, so we've got a database action you can use, I'm going to connect to that database, and I've got my credentials stored in that connection string in that variable. And then I'm going to execute the SQL statement that we have, so we just select the SQL query, and we can put those query statements here. Again, I've got those stored in a variable up above. And I believe all this one is doing, if we look at the select statement, it's just selecting all the records from my customers database.

26:13: Once we have that SQL statement done, what we're going to do is we're going to create a dataset like we talked about, going to store all those records in this dataset one. Now we can go ahead and we can close that SQL connection, we've got those records stored locally now, we can go ahead and close it, and now we're going to start writing the contents of that dataset to my spreadsheet. And I can include the column names in this when I write these records out. So I'll run this just to show you what it does, it'll run very quickly, it's going to go ahead and create a new spreadsheet for me. You can see it's already run that SQL query, and it's written all those records out to my spreadsheet, along with the headers.

27:02: All right. So that's one way you can automate writing from a database and... Reading from a database and writing to Excel. Now let's take a look at it the other way around. What I'm going to do in this task is I'm going to read from Excel and update a database. So the same type of thing here, I've got all of my file locations, et cetera, stored in the variable here. I do have a delete statement here, because the first thing I'm going to do is I'm going to delete all those customers that I... That are in that database so that I can update them again, so you'll see that as we go in.

27:38: One of the other actions that we have is to write to the event log, so we're writing some information about what this task is doing out to the Windows event log. Same thing here, I'm making my connection to the SQL database. The first thing I'm going to do is I'm going to execute that delete statement, clean up those records, and then I'm going to open up my spreadsheet, and instead of writing to the spreadsheet, now I'm going to read, I'm going to get the cells from the spreadsheet, I'm going to write them to this database, and I'm just going to start up in the corner, row one, column one, and Automate is smart enough to know how many records there are, and it will auto-detect the number of records. And then I'm also going to use that first row as the column names, so when I write those records out to SQL, it will know where to put them.

28:29: So when I read the database, it will create that dataset, and it also will create variables for all of the fields that are in that spreadsheet. So if you see any field here that has a percent sign, we have a lot of different variables that you can use, and actually let me show you, I will run just the reading of the spreadsheet.

29:02: So it's going to clear out that spreadsheet, open up a document for me, and now when I go to write that query for my insert statement, if I click here on the percent sign, now you can see when we read in from that Excel spreadsheet, it created all of these variables that we can use with all of the fields that are in that spreadsheet, and now what we're going to do is use those variables, here you can see we've got percent signs wrapped around the first name, and we're going to insert that database. So when we run this, can see it looping through the dataset that we've got. And it will open up my database, and then here are my customers that I updated this database from that spreadsheet automatically. Very cool.

30:03: Emailing. Well let's take a look at automating some email, so what this task is going to do, it's going to read from a list of email addresses that I've got, and I've got these on a spreadsheet over here. So I'm just emailing to me and then one of my demo emails, so this could be... This input could be coming from a database of customers, it could be coming from a spreadsheet with a list of customers or vendors, and it will read in all of those email addresses.

30:35: And then we also have a file here for the message that we want to send, [inaudible 00:30:40] I can find this file. We want to send this, so this is the message that we want to send to all of those customers, so I've got just a text file, and again it's going to read in that text file, and enter that into the email for me. So here we've got our message file, where is that at? This body.text. Where's our email file? Here I've got a address Excel spreadsheet. And then we've got an email server, because we're going to send out an email.

31:15: So, kind of the same thing. We're going to open up the spreadsheet, and we're going to get all the text from there and create a dataset which is a list of all my email addresses. And then we're going to also read in the data from the file that we've got, so we've got our message that we're going to put into the email. And then we're going to start looping through that dataset, and as we loop through it, we're going to send an email, so here's our email server, here's our message, again it's coming from that email list that we ran, who's it going to? And then here's our message down here. So if we go ahead and run this guy, think I can... Here I've got one of the emails, so here this is one of the emails that went to automate.demo, and there's our message that we're sending. And using those variables, you could put any value in there from some other data source that you needed to communicate with your customers.

32:32: And then two more, so we're... Let's take a look at Active Directory, so if we... Think I'm going to open this up. Here we have our Active Directory action, and what we're going to do is we're going to create a new user, three new users, actually. So we've got our new hire spreadsheet, if we take a look at this, you'll see I've got three Patricias out there, and I want to add them to Active Directory. And so in my task, same kind of thing, I'm going to open up that spreadsheet, and then I'm going to start looping through that dataset, and use that data to... First of all, I'm going to generate a password, and you can set that up for whatever your policy is, and store that. This is going to be a temporary password, we're going to force the user to change that when they log in, but we'll create a temporary password.

33:32: And then the next thing we'll do is create that Active Directory user, so these are all the different options that you have for the Active Directory action, we need to know where the directory is, and then using the data from that spreadsheet, here we have the first name and the last name, their full name. Here we'll create their login. Here's the temporary password, we'll populate that variable in there, and the user's got to change it. And then also their email address, based on that spreadsheet.

34:08: And then we go out and we get that, so we're going to use the get object, and we're going to get this user that we're still looping through that spreadsheet, and then we're going to update some of the parameters associated with that user, maybe description, phone number, location, et cetera. So again, it's a matter of knowing where that user needs to be and the credentials that they need, and setting it up in Automate.

34:36: Now we're going to send an email message. So the same thing, we're going to use that server. I'm just sending this to my dev, and I've got my new employee login information, so we have the new name, and then their temporary password as well, send that out. And this can be part of a larger workflow, so we can create that Active Directory entry and then we can go on and enter that person into the payroll system, enter them into the accounting system, or whatever systems, whatever applications they need access to can all be part of a bigger workflow.

35:15: And then the last task that I want to show you is a SharePoint task. The variables that we've created here, I've got the URL for my SharePoint site, and then my credentials, the user and the password for logging in, and I'm just going to upload a document. So if we look here at the SharePoint action, just open that up so you can take a look, so we have a lot of different actions that you can perform with SharePoint, so I'm uploading a file in my task, but as you can see, you can go get files, check them in and out, make copies, move files.

35:50: Same thing with folders, create and update folders. Lists, so if you have customer lists you need to update, you can do that automatically as well, so I could read in the customers from some source and then update them. Manage attachments, server, workflow, view, group, user, different roles, so you can do a lot of your management of SharePoint automatically.

36:18: And so this is just a very simple... I'm going to make a connection to my Automate... Or to my SharePoint site, the user... A URL, the user and the password, so I just make that connection. And then I'm going to upload the file, so I just use the upload file. I'm just doing a single file, but you could certainly do multiple ones. And I'm going to overwrite it if it's already out there, and then disconnect, so it's very straightforward and simple. If I run this, should run very quickly. When it turns blue, it's done. You can see the log down here. [inaudible 00:37:00] I thought I had this open. Let me find my Automate... Or my SharePoint site. And I've got my documents here. And here you can see the document that was just updated a few seconds ago.

37:30: So, lots of different ways that you can automate your Microsoft tasks with Automate. I see we're running a little bit over, so I will have somebody follow up with questions that have come in, and we will get back to you after today's session. So thank you for joining me today, I appreciate you taking the time to take a look at Automate. I think you'll see that it really can help you automate your Microsoft tasks and free up your time, and also eliminate any kind of errors. So thank you for joining us today, and hopefully we'll see you next time. Bye bye.