VBA, Outlook and Excel: how to inhibite Outlook popup that blocks automatic operations?

[Nelson78]

Hello everybody.

I have built a hybrid process that engages both Microsoft Excel 2007 and Microsoft Outlook 2007.

The process is triggered by an incoming e-mail sent by a specific sender at any hour at minute 15.
With VBA instructions set in Outlook, the mail .xls attachment is used to update an .xls report and, still automatically, this report is sent to colleagues all over the firm.
The process works very well taking a couple of minutes from the delivery of the mail that triggers the operations and without affecting my regular activity. The process works all night, with my pc always on.

The only problem I've bumped into is about an Outlook popup that requests entering username and password (see https://imgur.com/a/lqXwsn2 ), a request that from my point of view is not justified by anything.

Of course, this popup is a sort of blockage: it is necessary to confirm "OK" to allow working the automatic process. This implies that when it occours through the night, the report is not sent for hours, until I join my workplace and click "OK".

Now, due to safety restrictions set by IT, I'm not able to inhibite this pop up by manipulating the Outlook settings.

Now, I think the best solution is a macro from an Excel workbook (always open) that any hours at the minute 15 or so, checks the presence of the popup in Outlook: if yes click ok, if not do nothing.

Could you support me in this operation?

Thank you in advance.

Paolo

 

[Coding4Fun]

Maybe this will help, this is a macro that will move the mouse position and click in a certain area. You could make it so it is clicking where your OK button would appear.

I would be curious to see the code you are using to send the email however as I have not run into issues being asked for login while engaging the sending of an email from outlook.

64 Bit Version

Public Declare PtrSafe Function SetCursorPos Lib "user32" (ByVal x As Long, ByVal y As Long) As Long
Public Declare PtrSafe Sub mouse_event Lib "user32" (ByVal dwFlags As Long, ByVal dy As Long, ByVal cButtons As Long, ByVal dwExtraInfo As Long)
Public Const MOUSEEVENTF_LEFTDOWN = &H2
Public Const MOUSEEVENTF_LEFTUP = &H4
Public Const MOUSEEVENTF_RIGHTDOWN As Long = &H8
Public Const MOUSEEVENTF_RIGHTUP As Long = &H10


Sub autoclick()


Application.Wait Now + TimeSerial(0, 0, 2)
SetCursorPos 1000, 500


Application.Wait Now + TimeSerial(0, 0, 2)
mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0
mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0


End Sub

For 32 bit

Public Declare SetCursorPos Lib "user32" (ByVal x As Long, ByVal y As Long) As Long
Public Declare Sub mouse_event Lib "user32" (ByVal dwFlags As Long, ByVal dy As Long, ByVal cButtons As Long, ByVal dwExtraInfo As Long)
Public Const MOUSEEVENTF_LEFTDOWN = &H2
Public Const MOUSEEVENTF_LEFTUP = &H4
Public Const MOUSEEVENTF_RIGHTDOWN As Long = &H8
Public Const MOUSEEVENTF_RIGHTUP As Long = &H10


Sub autoclick()


Application.Wait Now + TimeSerial(0, 0, 2)
SetCursorPos 1000, 500


Application.Wait Now + TimeSerial(0, 0, 2)
mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0
mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0


End Sub

 

[Nelson78]

Maybe this will help, this is a macro that will move the mouse position and click in a certain area. You could make it so it is clicking where your OK button would appear...

I've not understood your request: I'm not dealing with login issues about Outlook. It is simply my account which is open.

About your suggestions: where do I have to put the code? How can I identify the correct cursor position?

 

[Coding4Fun]

Hello everybody.
The only problem I've bumped into is about an Outlook popup that requests entering username and password (see https://imgur.com/a/lqXwsn2 ), a request that from my point of view is not justified by anything.

Of course, this popup is a sort of blockage: it is necessary to confirm "OK" to allow working the automatic process. This implies that when it occurs through the night, the report is not sent for hours, until I join my workplace and click "OK".

Your post stated that there was a login screen that was appearing and you had to click OK to continue the code.

 

[Coding4Fun]

About your suggestions: where do I have to put the code? How can I identify the correct cursor position?

This would be placed in a stand alone excel file that is left open and running, it would call this process every x number of minutes (You would need to set this up).

To determine the position of the popup - there are tools out there that will indicate your position on the screen but you could trial and error until the mouse is positioned where the OK box would appear. Not trying to promote the product but one solution for finding the screen position is Greenshot, it is a screen capture software that will show you the x and y coordinates of the mouse while you are taking a screen shot.


This is a complete example that would set the mouse position to 1000x500 every hour for 24 hours

This is setting the 24 hours (Will loop 24 times)
For i = 1 to 24

This is saying for each loop wait an hour and then move the mouse.
Application.Wait Now + TimeSerial(1, 0, 0)

Public Declare PtrSafe Function SetCursorPos Lib "user32" (ByVal x As Long, ByVal y As Long) As Long
Public Declare PtrSafe Sub mouse_event Lib "user32" (ByVal dwFlags As Long, ByVal dy As Long, ByVal cButtons As Long, ByVal dwExtraInfo As Long)
Public Const MOUSEEVENTF_LEFTDOWN = &H2
Public Const MOUSEEVENTF_LEFTUP = &H4
Public Const MOUSEEVENTF_RIGHTDOWN As Long = &H8
Public Const MOUSEEVENTF_RIGHTUP As Long = &H10


Sub autoclick()


Application.Wait Now + TimeSerial(1, 0, 0)
SetCursorPos 1000, 500


Application.Wait Now + TimeSerial(0, 0, 2)
mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0
mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0


End Sub


Sub runAutoClicker()


For i = 1 To 24


Call autoclick


Next i


End Sub

 

[Nelson78]

Public Declare PtrSafe Function SetCursorPos Lib "user32" (ByVal x As Long, ByVal y As Long) As Long
Public Declare PtrSafe Sub mouse_event Lib "user32" (ByVal dwFlags As Long, ByVal dy As Long, ByVal cButtons As Long, ByVal dwExtraInfo As Long)
Public Const MOUSEEVENTF_LEFTDOWN = &H2
Public Const MOUSEEVENTF_LEFTUP = &H4
Public Const MOUSEEVENTF_RIGHTDOWN As Long = &H8
Public Const MOUSEEVENTF_RIGHTUP As Long = &H10


Sub autoclick()


Application.Wait Now + TimeSerial(1, 0, 0)
SetCursorPos 1000, 500


Application.Wait Now + TimeSerial(0, 0, 2)
mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0
mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0


End Sub


Sub runAutoClicker()


For i = 1 To 24


Call autoclick


Next i


End Sub

Excuse me, because of a bad flu I've been stuck in bed for a couple of days.

So, if I put the code in a module, something goes wrong.

https://imgur.com/a/BL0zDKY

By workaround, removing the word "PtrSafe" the lines font regularly turn into black.

Could this fix the problem in the correct way? (consider I've been working with W7PRO 64bit).

Hello everybody.
The only problem I've bumped into is about an Outlook popup that requests entering username and password (see https://imgur.com/a/lqXwsn2 ), a request that from my point of view is not justified by anything.

About this clarification:
- there is only one account that has ever run on my Outlook;
- I've never changed the password in ten years I've had this e-mail account;
so, I don't understand why I'm asked entering username and password again.

 

[Coding4Fun]

This is where I initially got the idea and I had found the Microsoft site as well but I am not able to locate the Microsoft documentation on this.

https://www.youtube.com/watch?v=ml2aUnQTrcw

When I initially tried to run the code in the video it did not work for me and I had to add in the PtrSafe and adjust a few other points.

The code I posted with PtrSafe works on my machine running windows 10 64 bit / Excel 64 bit.

In regards to why you are getting this pop up for credentials. There is a setting to "Always prompt for credentials" which may be turned on.

[FONT=&quot]1. In Outlook, go to File tab > Info > Account Settings > Account Settings…
2. Highlight your Exchange account and click Change button.
3. Click on More Settings button.
4. Select the Security tab.
5. Under the User Identification, make sure the “Always prompt for logon credentials” option is not selected.
[/FONT]
It is also possible you have a cached credential on your computer, it tried to use that to login and then cant so it asks you to sign in. Search for Credential Manager on your PC and delete your cached credentials for outlook. It should ask you to login the next time and then allow you to store the updated login.

 

[Nelson78]

This is where I initially got the idea and I had found the Microsoft site as well but I am not able to locate the Microsoft documentation on this.

https://www.youtube.com/watch?v=ml2aUnQTrcw

When I initially tried to run the code in the video it did not work for me and I had to add in the PtrSafe and adjust a few other points.

The code I posted with PtrSafe works on my machine running windows 10 64 bit / Excel 64 bit.

Ok, following your suggestions and consulting the video, I figured it out this way (consider that 630, 153 is not the real popup position).

Public Declare Function SetCursorPos Lib "user32" (ByVal x As Long, ByVal y As Long) As Long
Public Declare Sub mouse_event Lib "user32" (ByVal dwFlags As Long, ByVal dx As Long, ByVal dy As Long, ByVal cButtons As Long, ByVal dwExtraInfo As Long)
Public Const MOUSEEVENTF_LEFTDOWN = &H2
Public Const MOUSEEVENTF_LEFTUP = &H4
Public Const MOUSEEVENTF_RIGHTDOWN As Long = &H8
Public Const MOUSEEVENTF_RIGHTUP As Long = &H10


Sub autoclick()


Application.Wait Now + TimeSerial(0, 0, 2)
SetCursorPos 630, 153


Application.Wait Now + TimeSerial(0, 0, 2)
mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0


End Sub

Now, I think, as soon as it appears I should check the popup alert name:
- if exists, focus on it and click ok
- it not exists, do nothing

Is this the correct way to proceed considering that, I fear, this click could affect other automations running on my pc? (so, it would be better to minimize risks of interference).

 

[Coding4Fun]

Going a little deeper than I anticipated but it gives me the opportunity to learn.

There are too many variables for me to give you an exact solution but maybe this will help point you in the right direction.

https://answers.microsoft.com/en-us...xcel-vba/ccdee036-1755-4589-a79a-94f7b899b3f6

http://www.vbforums.com/showthread.php?558785-Get-Active-Window

You need to figure out what the name of the window is and then look for it, if it exists trigger the click. Let me know if you get stuck and I will see if I can assist further.

 

[Nelson78]

Going a little deeper than I anticipated but it gives me the opportunity to learn.

There are too many variables for me to give you an exact solution but maybe this will help point you in the right direction.

https://answers.microsoft.com/en-us...xcel-vba/ccdee036-1755-4589-a79a-94f7b899b3f6

http://www.vbforums.com/showthread.php?558785-Get-Active-Window

You need to figure out what the name of the window is and then look for it, if it exists trigger the click. Let me know if you get stuck and I will see if I can assist further.

Ok, it has appeared again.

The pop up name should be:

"Enterpassword#32770"

The coordinates to click "ok" should be

SetCursorPos 630, 680

Now, I'm going to try setting a periodic check (for example every hour at the minute :25) about the pop up alert existence, then click "Ok" if it exists.

I'll keep you up to date.