VBA Code to email (outlook) - Fine in 2016(32bit) / Broken in 365(64bit)

[Dave1975]

After various searches (when working) to no avail resorting to new post. apologies if answered elsewhere.

Short Backstory.
MS Access 2016 32bit. Lots of DB's happily emailing in the background using vba code.
Company decides to finally upgrade to win10 and fully adopt 365. Great. Although that now means Access365 in 64bit.
I have managed to adjust code all over the place for the 32/64bit change but cant find a solution for the following code.

Dim app As Outlook.Application
Dim msg As Outlook.MailItem
Dim recipTO As Outlook.Recipient
Dim recipCC As Outlook.Recipient
Dim Att As Outlook.Attachments


Set app = CreateObject("outlook.application")
Set msg = app.CreateItem(olMailItem)



todayDate = Format(Date, "DDMMYY")
FileName = Application.CurrentProject.path & "\Daily" & todayDate & ".xlsx"
DoCmd.OutputTo acQuery, "Daily Report", acFormatXLSX, FileName, False



With msg
   Set recipTO = .Recipients.Add("email.address@here.com")
   Set Att = msg.Attachments
   Att.Add FileName, olByValue, 1, "Todays Report as of " & todayDate


   .subject = "Daily Report - Generated (" & Format(Now(), "dd.mm.yyyy") & ")"
   .HTMLBody = tmpEM

   .Send
End With

This is just one example - i use variations of this code all over the place - even in some functions

its driving me up the wall.
using Display mode just about works though referencing recipients is a minefield - but i also need send mode to work.
Any solutions?

 

[NauticalGent]

Going to need a little more info. Does is simply fail silently or is it going into debug mode. If the latter, what line is it breaking on?

using Display mode just about works though referencing recipients is a minefield

Can you give a little more detail here?

Is this a company using MS Exchange Server for emails or is it something else?

 

[Gasman]

Not an expert, but I *thought* the 64 bit issue was only for APIs?
I see nothing there that *should* cause a problem?

 

[NauticalGent]

Not an expert, but I *thought* the 64 bit issue was only for APIs?
I see nothing there that *should* cause a problem?

Spot on Paul, which is why I asked my questions. If I were laying bets, I would put my money on WIN 10 being the problem. WIN 10 at its base installation has more security features than WIN 7 had at max optimization (or so I've been told, I am NOT an IT guy).

When my company did a simular "upgrade" the number of STIGs that came with the install were off the charts.

 

[Gasman]

Well I have been running similar code for a couple of years on Win10 (various versions) and Office 2007, without any issues.?

I know when I was working, I did have problems with one office package not being inline with the others, but that was Excel, later than 2007, as one user had a trial version of a later office package on their PC.?

 

[NauticalGent]

Understood, which is why I brought up the STIGs. Our IT department did the base install which came with a whole slew of them that pretty much rendered any automation with Outlook impossible.

After about a week of working with the IT help desk, I finally got elevated to whatever tier had the expertise to figure it out.

 

[arnelgp]

which part is not working?

With msg
   .TO = "email.address@here.com"
   .Attacments.Add FileName, olByValue, 1, "Todays Report as of " & todayDate


   .subject = "Daily Report - Generated (" & Format(Now(), "dd.mm.yyyy") & ")"
   .HTMLBody = tmpEM

   .Send
End With

 

[Dave1975]

We are running MS Exchange - and there are plenty of complaints at the new security level concerning macro's etc. Not sure i am going to get anywhere on those issues as the company likes thenew security level (for some strange reason)

Not sure what an STIG is

the .Send part is where i get to at present. refuses point blank

on the one occasion we got to run Access 2016 in windows 10 - this bit ran fine. So somewhere in the full migration, adopting 365 64bit etc it has gone awry.

Currently arguing to allow 2016 to be installed again though they are being a bit stuck/mud syndrome on it - so also looking to see if my code needs adapting.

 

[NauticalGent]

Not sure what an STIG is

Security Technical Implementation Guide - In normal language, it is high level settings that can disable certain features of MS Office applications. I may have assumed too much too - it appears this only applies to US DoD networks. Since you are in the UK, STIGs might not apply to you.

 

[Dave1975]

i get this at the .send stage in 365/64bit

same database and code in 2016/32bit - no issue

 

[arnelgp]

Can you send mail manually using outlook?
If not then..

 

[Dave1975]

.display does work but breaks the process. most of the .send processes are becuase we need it to automatically email once something has been imported.
if using .display then we risk it not being sent or being altered accidently.

 

[arnelgp]

Can you alternaively use CDO object?

 

[Dave1975]

i have tried but have less experience with CDO and im convinced im doing it wrong. what would be the CDO equivelent?

 

[arnelgp]

I am not on my pc so i can only suggest googling some vba examples.

 

[NauticalGent]

i have tried but have less experience with CDO and im convinced im doing it wrong. what would be the CDO equivelent?

Start here: https://codekabinett.com/rdumps.php?Lang=2&targetDoc=send-email-access-vba-cdo
or here: http://www.accessmvp.com/thedbguy/demos/cdoemail.php
and even here: http://www.mendipdatasystems.co.uk/email-tester/4594365455

that should get you started!

 

[NauticalGent]

I found this one too which might be specific to your situation...

VBA – Send Email Using Windows 10 Curl | DEVelopers HUT

No matter which way you go, please report back here what solution you went with!

 

[theDBguy]

Hi. Just as a test, either try removing the reference to the Outlook object, closing the code window, open it again, and then re-add the correct Outlook reference. Or, try using late binding in your code.

Just a thought...

 

[NauticalGent]

Or, try using late binding in your code.

Would that be done by replacing Dim app As Outlook.Application with Dim app As Object ?

 

[theDBguy]

Would that be done by replacing Dim app As Outlook.Application with Dim app As Object ?

Hi @NauticalGent. That's exactly right!

Here's some more examples:

Dim app As Object 'Outlook.Application
Dim msg As Object 'Outlook.MailItem
Dim recipTO As Object 'Outlook.Recipient
Dim recipCC As Object 'Outlook.Recipient
Dim Att As Object 'Outlook.Attachments

olMailItem = 0

Set app = CreateObject("outlook.application")
Set msg = app.CreateItem(olMailItem)

Hope that helps...