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



## Nelson78 (Dec 3, 2018)

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 (Dec 3, 2018)

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 (Dec 4, 2018)

Coding4Fun said:


> 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 (Dec 4, 2018)

Nelson78 said:


> 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 (Dec 4, 2018)

Nelson78 said:


> 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 (Dec 11, 2018)

Coding4Fun said:


> ```
> 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
> ...



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 (Dec 11, 2018)

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 (Dec 13, 2018)

Coding4Fun said:


> 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
> 
> ...



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 (Dec 14, 2018)

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 (Dec 31, 2018)

Coding4Fun said:


> 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.
> 
> ...



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.


----------



## Nelson78 (Dec 3, 2018)

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


----------



## Nelson78 (Jan 14, 2019)

Coding4Fun said:


> 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.
> 
> ...



It seems I've figured it out (a couple of times the alert has been managed in the desired way).

There is still some things unclear: for example, I've add the following instruction, but maybe it is unnecessary (perhaps the focus is automatically on the alert regardless this instruction).


```
Set objShell = CreateObject("WScript.Shell")
objShell.AppActivate "Enterpassword#32770"
```


----------

