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

Dave1975

Member
Local time
Today, 14:33
Joined
Oct 21, 2015
Messages
39
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.

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?
 
Last edited:

NauticalGent

CopyPaster of the First Order
Local time
Today, 09:33
Joined
Apr 27, 2015
Messages
4,889
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

Enthusiastic Amateur
Local time
Today, 14:33
Joined
Sep 21, 2011
Messages
10,354
Not an expert, but I *thought* the 64 bit issue was only for APIs?
I see nothing there that *should* cause a problem?
 

NauticalGent

CopyPaster of the First Order
Local time
Today, 09:33
Joined
Apr 27, 2015
Messages
4,889
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

Enthusiastic Amateur
Local time
Today, 14:33
Joined
Sep 21, 2011
Messages
10,354
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

CopyPaster of the First Order
Local time
Today, 09:33
Joined
Apr 27, 2015
Messages
4,889
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

..forever waiting... waiting for jellybean!
Local time
Today, 21:33
Joined
May 7, 2009
Messages
16,135
which part is not working?
Code:
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

Member
Local time
Today, 14:33
Joined
Oct 21, 2015
Messages
39
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

CopyPaster of the First Order
Local time
Today, 09:33
Joined
Apr 27, 2015
Messages
4,889
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

Member
Local time
Today, 14:33
Joined
Oct 21, 2015
Messages
39
dotSEND error.jpg


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

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

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:33
Joined
May 7, 2009
Messages
16,135
Can you send mail manually using outlook?
If not then..
 

Dave1975

Member
Local time
Today, 14:33
Joined
Oct 21, 2015
Messages
39
.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

..forever waiting... waiting for jellybean!
Local time
Today, 21:33
Joined
May 7, 2009
Messages
16,135
Can you alternaively use CDO object?
 

Dave1975

Member
Local time
Today, 14:33
Joined
Oct 21, 2015
Messages
39
i have tried but have less experience with CDO and im convinced im doing it wrong. what would be the CDO equivelent?
 

NauticalGent

CopyPaster of the First Order
Local time
Today, 09:33
Joined
Apr 27, 2015
Messages
4,889

theDBguy

I’m here to help
Staff member
Local time
Today, 06:33
Joined
Oct 29, 2018
Messages
18,792
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...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:33
Joined
Oct 29, 2018
Messages
18,792
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:
Code:
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...
 
Last edited:

Users who are viewing this thread

Top Bottom