VBO Run Macro with Email

[DenisEischen]

Hello Everyone! I am currently working on a very basic process that goes as follows: 1) Open saved excel 2) Run Macro 3) Close Excel The tricky portion is related to the Macro. The Macro is designed to take information from the excel and send out emails using outlook. When I run my process in the studio it seems to work correctly. However, I have a problem when I try running the process through the control room. The process finishes successfully, but when I step into my virtual machine I notice that I have several outlook emails open that have not been sent. Why is my process and my macro running successfully in the studio, but not executing sending emails when I run it through the control room.   I've tried to add a wait stage after running the macro to ensure the excel does not close out before sending emails, but that does not seem to work.   Any ideas would be appreciated.   Thanks!

[AmiBarrett]

A few things. 1) Are you running the process in the control room on the same system as you tested in the studio? If no, are they running the same version of Outlook? 2) Check the programmatic access settings in Outlook's trust center 3) Change Outlook's Send/Receive frequency to one minute (Bit of a hack, but it works) 4) Which VBO specifically are you using to send e-mail? Or is Excel handling the outbound e-mail instead of Blue Prism?     4a) If Excel is handling it, it may have to do with which command the macro is using to send the message(s). 5) Will it work with Outlook already open and running in the background?

[DenisEischen]

Amibarrett I think I know what the issue might be, but I'm not sure how to resolve it. I will start off by answering your questions below: 1) Are you running the process in the control room on the same system as you tested in the studio? If no, are they running the same version of Outlook?' The process that is ran through the control room is kicked off on blue prism from my laptop. The process that is actually ran in the studio is running on a virtual machine that is connected to the same server. Both my laptop and the virtual machine have the same version of Outlook. 2) Check the programmatic access settings in Outlook's trust center I don't currently see any issues with outlook's access settings 3) Change Outlook's Send/Receive frequency to one minute (Bit of a hack, but it works) This is interesting and good to know, but I tried it and unfortunately it did not resolve the issue. 4) Which VBO specifically are you using to send e-mail? Or is Excel handling the outbound e-mail instead of Blue Prism?     4a) If Excel is handling it, it may have to do with which command the macro is using to send the message(s). The email is being sent using the macro developed in excel. It is not being sent using blue prism. See below for the portion of the macro that we are using the send the email: 'Send Email     strSubject = OpCo & "" Calendar Status Update""     Set OutApp = CreateObject(""Outlook.Application"")     Set OutMail = OutApp.CreateItem(0)     On Error Resume Next     With OutMail         .To = Email         .CC = """"         .BCC = """"         .Subject = strSubject         .HTMLBody = ""Below is a status update for "" & OpCo & "" :"" & """" & _                     """" & Status1 & "": "" & StatusValue1 & "" "" & """" & _                     """" & Status2 & "": "" & StatusValue2 & """" & """" & _                     """" & Status3 & "": "" & StatusValue3 & """" & """" & _                     """" & Status4 & "": "" & StatusValue4 & """" & """" & _                    

[AmiBarrett]

Awesome. Thanks for that info! It's actually super-helpful. :) The macro you've pasted is certainly creating the message, but it is not invoking a send command. You can fix this one of two ways: 1) Add this to the bottom of your With OutMail block: .Send() 2) Alternatively, add this after the End With: OutMail.Send()   In regards to the SendKeys error, it sounds like by having the virtual machine closed, you just mean it's not in the foreground. If that's the case, this absolutely explains why it isn't working. Global Send Keys functions as a top-down (layer-wise) interaction tool. Best practice is to always ensure it's in the foreground by activating it within Blue Prism first. Likewise, if I recall correctly, SendKeys acts the same way as it's just what Blue Prism's Global Send Keys calls in the background. It's interesting that it would specifically give an access denied error, though. That sounds more like one of the processes is running under a different user account with different permissions, or something.

[DenisEischen]

I've tried adding .send, but it doesn't seem to send the file. I had been using Application.SendKeys ""%s"", but as mentioned before I can't SendKeys if I do not have the virtual machine in the forefront of my desktop. This is an issue, because we are trying to set up this macro to run on a schedule to send users status update emails. I'm currently at a loss for how to resolve this. I've researched all over the internet and many people say that SendKeys are not reliable when using a virtual machine and a lot of people cannot get the "".send"" command to work due to security issues from their company.       

[zdenek.kabatek]

Hi, Denis, the issue is elsewhere. When your virtual machine is not active there is no active desktop and therefore Global Send Keys actions will not work. I suspect that it also fails because you have .display called in your macro. So try to avoid Global SendKeys and displaying anything on the screen as long as your virtual machine is not logged in. For such cases usual scenario is to call Login Agent that logs in into your virtual machine and it ensures that there is an existing active desktop. Also please bear in mind if you connect to your Virtual machine via RDP and then you click on the closing button this automatically closes the session which was open.

[AmiBarrett]

SendKeys reliability will mostly depend on how you're trying to interface with the system. I know that RDP considers using SendKeys a security risk, and generally blocks the keystrokes. As an alternative, see if you can have the VM run AutoHotKey to send they keystrokes. We used a similar approach with a solution that had to drill down through three RDP hops with a 100% success rate. I can't see a reason why running it on the target system directly would cause an issue. If you need a method to remotely start the application, I would start with PSExec from SysInternals' PSTools package. As a fallback, you should also be able to code in something via PowerShell.