Solved Determine if M365 via VBA - how? (1 Viewer)

[Marshall Brooks]

I thought this would be a simple question - it appears not to be. We have some users running Office M365 and some using Office 2016.

Recently my nightly Excel reports stopped being created unattended b/c Excel said they needed a security label. I found a fix for that here: https://stackoverflow.com/questions/74712674/add-sensitivity-label, and our E-mails will need a security label - I found a fix for that (Sendkeys - crude) here: https://stackoverflow.com/questions/72230105/changing-sensitivitylabel-in-outlook-365-email-with-vba

But I would only like to use the new code if on a machine with M365. (Technically, I need to know if using Excel M365 or Outlook M365, not Access M365, but hopefully the same code will work for both.) I'm not sure if you can run Access M365 and Outlook 2016, but none of our users will be doing that.

I found Colin's program here https://www.isladogs.co.uk/access-office365-win-check/index.html#DL, which works, but I don't know HOW it determines if it is M365.

There is code here http://www.vbaexpress.com/forum/sho...-365-subscription-or-purchased-Office-version which I haven't tested, but some replies implied it did not work.

As a crude workaround, some of our users run Access M365 from the Desktop and others run it under Access from a shared drive using Access 2016 under Citrix. So I could simply check if the Access Path is on the shared drive, but I'd prefer something more definitive.

All help appreciated.

 

[theDBguy]

I found Colin's program here https://www.isladogs.co.uk/access-office365-win-check/index.html#DL, which works, but I don't know HOW it determines if it is M365.

Just guessing, and I'm sure @isladogs will correct me if I am wrong, but I think his code checks for Registry entries.

 

[Marshall Brooks]

Question - I just tried to run my new code in Access 2016 and the line:
Dim docSenseLabel As SensitivityLabel
Gives me an Error - User defined type not defined.

Could that be used for error trapping - i.e. if you don't see an error on that line you are running M365 and if you do, you aren't using M365?

 

[theDBguy]

Question - I just tried to run my new code in Access 2016 and the line:
Dim docSenseLabel As SensitivityLabel
Gives me an Error - User defined type not defined.

Could that be used for error trapping - i.e. if you don't see an error on that line you are running M365 and if you do, you aren't using M365?

Was that from his code?

 

[Gasman]

Likely a missing reference.

 

[Marshall Brooks]

No, that's from the first StackOverflow link I posted above. The code works in M365 and fails in Access 2016. However, it doesn't even compile in Access 2016, so I'm not sure I can use Error Trapping with it to determine if running M365 or not. (But something similar would work for me, if possible.)

 

[theDBguy]

No, that's from the first StackOverflow link I posted above. The code works in M365 and fails in Access 2016. However, it doesn't even compile in Access 2016, so I'm not sure I can use Error Trapping with it to determine if running M365 or not. (But something similar would work for me, if possible.)

I Googled it and found this link. Check out the Remarks and Note sections. Perhaps your organization has it defined in the policy for M365 but not for Desktop apps.

SensitivityLabel object (Office)

SensitivityLabel object (Office)

learn.microsoft.com

 

[Marshall Brooks]

Yes, that's basically what started this whole exercise. Our organization requires sensitivity labels when using Office M365. I don't think sensitivity labels are available for Office 2016 - the link Remarks and Notes implies maybe not. The SO Link says how to apply the labels if using M365, but I don't want to have to have an M365 and A2016 versions of my database.

 

[isladogs]

The code I use in my Access/Windows version checker tool checks for information available in many different places including:

• registry entries
• info available directly from VBA code
• database properties
• web scaping an MS help article (for the YYMM version number used in recent C2R versions)

All of that in about one second!

The app is supplied as an ACCDE file so the code isn't available to inspect.

The challenge was finding out where all the information was available. In the case of the YYMM version number, unbelievably there isn't any location that information can be retrieved from Access or the registry. Hence the need to web scrape for that. . .

The add-in version of the app is available at:

Access/Windows Version Check Add-In

Use this add-in to obtain the full Access/Windows/Office 365 version and bitness information. It also provides details of the VBA references used by the host application

www.isladogs.co.uk

That also gets all the references used in the host app

EDIT: I've never heard of sensitivity labels in Office. Would signing the project code with a code signing certificate solve your issue?

VBA Project Signing - 1

This article explains how to use the new VBA project signing feature in Access. It also compares the use of this feature with using trusted locations when distributing your Access applications

www.isladogs.co.uk

 

[isladogs]

Adding on to my previous answer, I determine if its Office 365 first checking by running a registry check for the ProductReleaseIds value in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\Configuration as below

Typical output:
'365: O365HomePremRetail, O365ProPlusRetail
'2019: ProPlus2019Retail
'2021: ProPlus2021Retail, VisioPro2021Retail
'2016: ProPlusRetail i.e. no number included!

So I extract the number (if any) from that key value to determine the specific 'flavour' of Office 16.0

NOTE:
The key only exists in C2R installs - not MSI ...so not 2013 or earlier. Some versions of 2016 can also be MSI
As my code needs to work in Office 2000 onwards, I first check whether the office build number starts with 16.0 and then whether its C2R to avoid errors. You may not need to worry about such issues

 

[Marshall Brooks]

@isladogs - Thank you so much. I think that gave me what I need. The key on my local machine shows 0365ProPlusRetail. The key on the network machines is not present and your tool says it is an MSI build (Office 2016).

I just need to figure out how to parse the registry using VBA, but I can probably figure that out.

This has the advantage that I can add a check for that key and if in a year or two the network switches to M365, the added features in my code will automatically work with it.

To answer your previous questions:

• I realized your code is .accde. My first thought was "The AVC64_v2.73SA file has a field for Office 365 - Yes/No, so if I can figure out how the code determines that value, I have my answer. I don't really need all of the capability of the .accde file, but it could be helpful.
• My second thought, if I couldn't find the true test was launching your database in hidden mode and reading that value from the form - but while the code may run in under a second, the database won't load that quickly - and it means two databases to keep up with for all users.
• I'm not sure if I can install a 3rd-party add-in. It would be frowned on by corporate IT, even if it did work, and it isn't required for what I need.

Sensitivity labels have nothing to do with the actual code. There is a group policy you can set to require Sensitivity labels on new file creation (actually on new file save). (It might only be available for government contractors). For example, Excel:

Outlook:

The drop-down has values such as Unrestricted, Proprietary Information, Export Controlled Information, Third Party Proprietary Info, Attorney-Client information, Personal Information, etc.

The first StackOverflow article explains how to get the required codes for the labels in Excel and apply them via VBA to new files.

In theory, the same principles SHOULD work in Outlook applying the properties to the MailItem object, but it fails with "Object does not support this property or method." (Our organization is somewhat slow to update to the latest Office Version, and Microsoft generally focuses on Work, Excel and Powerpoint prior to Outlook, so the Excel code MAY work in Outlook in the future, but the second SO article says it doesn't. (If anyone knows how to make it work, please advise). The second SO article explains how to do it in Outlook using "SendKeys", but that only works if you are displaying the message, not saving it to the draft file, but I'm going to try to figure out how to display the message and then use SendKeys to save and close it.

(It looks like I should be able to do that with)(Untested Air Code):
SendKeys {Alt}{F} - File
SendKey {Alt}{S} - Save
SendKeys {Alt}{F} - File
SendKeys {Alt}{C} - Close

Not a fan of using SendKeys for this, but I don't know a better option.

 

[Marshall Brooks]

Solved. If anyone else needs it - here is the code I used - there might be simpler ways. (I only need to determine if Office 2016 MSI or Office M365 - there might need to be additional checks for earlier versions:

Sub Test()
If IsM365 = True Then
    MsgBox "M365"
Else
    MsgBox "Not M365"
End If
End Sub

Public Function IsM365()
' https://www.access-programmers.co.uk/forums/threads/determine-if-m365-via-vba-how.328145/
Dim i_RegKey As String
IsM365 = False
i_RegKey = "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\ClickToRun\Configuration\ProductReleaseIds"
If RegKeyExists(i_RegKey) = True Then
    If Left$(RegKeyRead(i_RegKey), 4) = "O365" Then
        IsM365 = True
    End If
End If
End Function

Function RegKeyExists(i_RegKey As String) As Boolean
Dim myWS As Object
' https://vba-corner.livejournal.com/3054.html
  On Error GoTo ErrorHandler
  'access Windows scripting
  Set myWS = CreateObject("WScript.Shell")
  'try to read the registry key
  myWS.RegRead i_RegKey
  'key was found
  RegKeyExists = True
  Exit Function
ErrorHandler:
  'key was not found
  RegKeyExists = False
End Function

Function RegKeyRead(i_RegKey As String) As String
Dim myWS As Object
' https://vba-corner.livejournal.com/3054.html
  On Error Resume Next
  'access Windows scripting
  Set myWS = CreateObject("WScript.Shell")
  'read key from registry
  RegKeyRead = myWS.RegRead(i_RegKey)
End Function

 

[Marshall Brooks]

The second SO article explains how to do it in Outlook using "SendKeys", but that only works if you are displaying the message, not saving it to the draft file, but I'm going to try to figure out how to display the message and then use SendKeys to save and close it.

For Outlook, I am using Ron DeBruin's code:

Home | Ron de Bruin Excel Automation

Welcome to https://www.rondebruin.nl

www.rondebruin.nl

Home | Ron de Bruin Excel Automation

Welcome to https://www.rondebruin.nl

www.rondebruin.nl

I have an option called "Action" which either shows the e-mail or saves it to the draft folder.

Oddly, .Show has to come BEFORE the E-mail is filled out, and .Save has to come after it.

So the non-M365 Code looks like:

    If Action = "Show" Then
        .Display
    End If
    .To = EmailTo
    .CC = EmailCC
    If Action = "Save" Then
        .Save
    End If

The M365 Code (simplified) looks like:

If Action = "Show" Then
        .Display
        SendKeys "%h" ' Alt H - gets the home menu
        SendKeys "AY" ' Sensitivity Label
        SendKeys "{DOWN}{ENTER}" ' Unrestricted'
    End If
    .To = EmailTo
    .CC = EmailCC
    If Action = "Save" Then
        .Display
        SendKeys "%h" ' Alt H - gets the home menu
        SendKeys "AY" ' Sensitivity Label
        SendKeys "{DOWN}{ENTER}" ' Unrestricted'
        Pause (2)
        .Close olSave
    End If
    
    Public Function Pause(NumberOfSeconds As Variant)
' https://www.access-programmers.co.uk/forums/showthread.php?t=167470
' Supports resolution at least to the the tenth of a second, maybe greater.
On Error GoTo Err_Pause

    Dim PauseTime As Variant, start As Variant

    PauseTime = NumberOfSeconds
    start = timer
    Do While timer < start + PauseTime
    DoEvents
    Loop

Exit_Pause:
    Exit Function

Err_Pause:
    Box Err.Number & " - " & Err.DESCRIPTION, vbCritical, "Pause()"
    Resume Exit_Pause

End Function

Not Crazy about using SendKeys at all, but this is better than Sending Alt-F4 or similar, which could close a completely different window or program.

 

[Marshall Brooks]

I spoke a bit too soon ...

The function above DOES detect if I am running M365 or not.

I have the following code:

    If IsM365 = True Then
        Dim docSenseLabel As SensitivityLabel
        Dim labelInfo As Office.labelInfo
        Set docSenseLabel = oExcelWrkBk.SensitivityLabel
        Set labelInfo = docSenseLabel.CreateLabelInfo()
        With labelInfo
            .AssignmentMethod = MsoAssignmentMethod.PRIVILEGED
            .LabelId = "<Proprietary Code>"
            .LabelName = "Unrestricted"
            .SiteId = "<Proprietary Code>"
        End With
        docSenseLabel.SetLabel labelInfo, labelInfo
    End If

It works fine in M365.

In Office 2016 MSI, it gives me an Error "User-Defined type not defined" on the dim statements when I try to compile or when I try to run the code - even though the code is inside the IsM365=True If Then and should not be executed.

I tried getting rid of the variables like this:

    If IsM365 = True Then
        With oExcelWrkBk.SensitivityLabel.docSenseLabel.CreateLabelInfo()
            .AssignmentMethod = MsoAssignmentMethod.PRIVILEGED
            .LabelId = "<proprietary>"
            .LabelName = "Unrestricted"
            .SiteId = "<proprietary>"
        End With
        oExcelWrkBk.SensitivityLabel.docSenseLabel.SetLabel labelInfo, labelInfo
    End If

But then I get "MsoAssigmentMethod is not defined" "labelInfo is not defined".

I'm not sure where to go from here ...

 

[Marshall Brooks]

Disregard ...

        Dim docSenseLabel As Object
        Dim labelInfo As Object

Seems to still work in M365 and should compile and work in Office 2016 MSI

In Office 2016 MSI, I had to also Dim MsoAssignmentMethod as Object. That got the code working in Office 2016. Now I will need to test if it still works in Office M365 with that change.

So many hoops to jump through !!!!

 

[Marshall Brooks]

In Office 2016 MSI, I had to also Dim MsoAssignmentMethod as Object. That got the code working in Office 2016. Now I will need to test if it still works in Office M365 with that change.

In Office M365, above statement gives an error "Error Number 91: Object Variable or With Block Variable not set." I added a line "Set MsoAssignmentMethod = Nothing", but I still get the same error.

This error is in a subroutine that generates Excel reports from queries in the Access database.

Unless anyone have better solutions, I think I have the following options - none of which I am crazy about:

• I can change the code back to how I originally had it set up. Reports will not run under Office 2016, and the database won't even compile under Office 2016, but I think the other functions of the database should work under Office 2016. I'm not crazy about that, but it's probably workable. (Typically, the reports run from my Office M365 computer. If they fail, my backup person is also running Office M365. The only potential issue would be if neither of us were available, the next backup person would be using Office 2016, but I don't think they know how to run the reports anyway.)
• I think I can split the subroutine - but that means changing "Export2XLS()" to "Export2XLS_M365()" and "Export2XLS_O2016". Changing all callouts from "Call Export2XLS(some variable)" to "If IsM365 =True Then Call Export2XlS_M365(some varialble) else Call Export2XLS_O216(some variable)" I think it would work in either Office program if I did that, but I don't think it would compile in Office 2016 MSI, and there are probably 30-40 function calls to update.
• I can split the front end and have one version for O2016 and one version for M365 until when/if everyone migrates to M365. That would work and compile, but defeats the purpose of the code that determines if it is M365 or not, means that I have two databases to maintain when we want to make changes to it, and breaks our auto-update procedure, which currently replies on one updated version of the front end in one location.

Does anyone see something that I am missing?

 

[Marshall Brooks]

Two updates:
Instead of regular SendKeys, I am using windows script send keys like this:

Dim WshShell As Object
Set WshShell = CreateObject("WScript.Shell")
WshShell.SendKeys "%h" ' Alt H - gets the home menu
WshShell.SendKeys "AY" ' Sensitivity Label
WshShell.SendKeys "{ENTER}" ' Unrestricted
Set WshShell = Nothing

Regular SendKeys was toggling the NumLock status.

There is a fourth option:

I am using SendKeys b/c the .SetLabel procedure that I am using in Excel doesn't work in Outlook. Not really crazy about having to do that, but I could probably do the same thing in the Excel subroutine. I don't like the idea, but it should work and compile in either flavor of Office.

 

[isladogs]

I've never tried to do this as I've never had a reason to do so, but just wondering whether you could use conditional compilation based on the Access version.
That would probably be simple where the Access.Version differs (2010 =14.0; 2013=15.0 etc) but perhaps it could be adapted to work for the different flavours of Access 16.0 (2016/2019/2021/365) using the registry key values I supplied earlier.

#If Is365 Then
   'Do something
#Else
   'Do something else
#End If

It may not work but certainly worth a try

 

[Marshall Brooks]

I'm not sure how to do conditional compilation or really what you mean by that.

(Maybe I do and it won't work).

At the risk of oversimplifying things, the error mainly comes down to this code snippet.
If IsM365 = True Then
...
.AssignmentMethod = MsoAssignmentMethod.PRIVILEGED
...
End If

That works fine in M365, so obviously MS updated the VBA compiler to recognize MsoAssignmentMethod, etc.

It fails to compile in Office 2016 and if I dim the statements, if fails to run in M365, even though the code is inside the If IsM365 = True Then condiitonal, which checks for the registry key, which I think is what you meant by conditional compilation. If so, that doesn't work.

I could deal with not being able to compile in 2016, but the subroutine tries to compile when I run it, so the only way I see to make it work in both flavors would be to split the subroutines and call one for M365 and the other for O2016.

I "think" that would compile in M365 and still fail to compile in O2016, but since the subroutine that fails is never called, it would probably be useable.

But it means I would have to change about 20 statements where the subroutine is called to conditionals to call one version for M365 and a different version for O2016 - and then when and if we migrated to M365, all of those conditionals would need to be removed (well, it would work without removing them, but it is superflous code at that point.

I appreciate all the suggestions, though.

 

[Marshall Brooks]

I understand somewhat what you are talking about - it would be like the code for 32/64 Bitness where you say:
If VBA7 Then
function ptrsafe ()
Else
function ()
End if

The issue I am having is similar to if I were running 32-bit Office and the VBA Compiler said "ptrsafe" is not defined.