VBA Send Outlook Email from MS Access (avoid user prompt to allow)

[SidCharming]

I have posted in Tek-Tips and am looking for a quick turn around on this question. Because the app that I built is ready for production, but IT in the last minute said we can't use CDO emailing. The only option I have is to use Outlook but there is the security feature that stops the code and waits for the user to allow each email.

Link is: https://www.tek-tips.com/viewthread.cfm?qid=1787202

As you'll see the thread is older and it was resolved until yesterday which IT bounced back during our pre-release testing.

Thanks!
Rob

[pbaldy]

See if this helps:

https://support.microsoft.com/en-us/...half-warning-i

[Minty]

As a point of interest in your original code, do you realise that this line

.SendUsingAccount = appOutLook.Session.Accounts.Item(3)

is unreliable, as each users accounts are stored in an arbitrary order.
In other words account 3 might be sales@you.co.com on your PC, but could be fred.at.home@gmail.com on someone else's laptop.

As to your issue, I'm not sure you can without some external help.
Have a read hee https://stackoverflow.com/questions/50963221/get-rid-of-warning-message-for-vba-sent-email

[SidCharming]

As a point of interest in your original code, do you realise that this line

.SendUsingAccount = appOutLook.Session.Accounts.Item(3)

is unreliable, as each users accounts are stored in an arbitrary order.
In other words account 3 might be sales@you.co.com on your PC, but could be fred.at.home@gmail.com on someone else's laptop.

As to your issue, I'm not sure you can without some external help.
Have a read heehttps://stackoverflow.com/questions/50963221/get-rid-of-warning-message-for-vba-sent-email

Minty, yes... I changed the .SendUsingAccount function. It was an attempt (early on) to use an account that executives use that their support staff don't have access to. I worked around it by making a table with addresses.

I'll look at the stackoverflow URL. Thanks

[SidCharming]

See if this helps:

https://support.microsoft.com/en-us/...half-warning-i

That would be the last option I'd like to approach IT with... altering the security of all Outlook installs (in my mind) is outside the scope. Meaning if I can use a third party app to make it happen, I don't compromise the integrity of Outlook as it stands (outside the use of my app).

Thanks for the suggestion. =)

[SidCharming]

I am looking at this DLL as a possible solution... Called Redemption. http://www.dimastr.com/redemption/rdo_introduction.htm

Anyone here used or has insight on it?

[pbaldy]

The first option solved the problem for us.

[SidCharming]

Here is the high-level layout of my application.

I.T. wants my MS Access application (Front end) to be run at the workstation level vs server.

1. Selected users are given a Desktop icon via a login script. (using AD groups) This icon copies the access database from a server each time it is clicked (expected clicked once).
The Access database is stored in %LocalAppData%
2. Access opens to a menu where the users select team members and click Submit
3. A macro generates up to 12 Excel files that have any number of sheets within (determined by algorithm)
4. When files are done begin generated, the system grabs each file from the (LOCAL) Compiled directory and emails to the rightful owner.
5. Close Access... Done

[Gicu]

Hi Rob, is it intended to run unattended or will it be a user there? I believe that you can avoid the prompt if you only use the Display method of the Outlook message object (meaning the user will need to click the Send button) and do not call the Send method. I did use Redemption successfully in the past, it is very easy to implement as the objects are similar to the Outlook ones; the only issue with Redemption is that it needs to be registered on every workstation (it comes with with a Setup.exe file that needs to be run) and the users and IT sometimes find that unnerving....
Cheers,
Vlad

[SidCharming]

Hi Rob, is it intended to run unattended or will it be a user there? I believe that you can avoid the prompt if you only use the Display method of the Outlook message object (meaning the user will need to click the Send button) and do not call the Send method. I did use Redemption successfully in the past, it is very easy to implement as the objects are similar to the Outlook ones; the only issue with Redemption is that it needs to be registered on every workstation (it comes with with a Setup.exe file that needs to be run) and the users and IT sometimes find that unnerving....
Cheers,
Vlad

Hi Gicu,
I am trying to do as much Unattended as possible. These users are not savvy and quite frankly they'd muck up things because of their history of doing things THEIR way vs the instructed way.

Thinking redemption could be installed/configured on each workstation phantomly via log in scripting. (Could be wrong, but that's my hunch)... Thanks for the insight Gicu!