Solved Form Field and SendKeys (1 Viewer)

[Marshall Brooks]

Solved. - Field got de-linked. I.e. if I went into design mode and looked at the event tab, there was an event for BeforeUpdate, but not for AfterUpdate, even though that was in the VBA. I just selected ... and Code from the drop-down and it is working again.

The code above works with the NotInList_1 (and possibly without).

 

[Marshall Brooks]

Out of curiosity, I went back and verified that the code in Reply #13 does work.

Apparently, you can set cancel = True and then set it back to false later.

It appears that the final state of it at the end of the procedure determines whether it is used or not.

 

[Marshall Brooks]

More complicated issue. Reply #13 works if the focus happens to be on the Status field as the SendKeys fires. If it is on a different textbox field, there is no effect.

One solution would be to copy text like Reply #13 to the BeforeUpdate event of EVERY textbox on the form, but that would be cumbersome (but would work).

Is there some way to basically ignore AY<Enter> for ANY field?

I saw https://www.access-programmers.co.u...to-iterate-through-controls-on-a-form.165872/

And I tried:

Dim ctl As Control
For Each ctl In Me.Controls
     If ctl.ControlType = acTextBox Then
        If ctl = "AY" Then
            ctl.Undo
        End If
     End If
Next ctl

In both the Form_Dirty and Form_BeforeUpdate events and neither one worked.

Also, ideally I would like the Undo event to occur as soon as the field is changed, NOT to wait until a different record is selected, although that would be acceptable if that is the only option.

Thanks in advance!

 

[sonic8]

Because I couldn't find a better way to apply Sensitivity labels, [...]

Did you investigate what happens if you access the Word.Application object of the Outlook inspector and set the SensitivityLabel for the current Word.Document, which is the current email?

 

[Marshall Brooks]

Did you investigate what happens if you access the Word.Application object of the Outlook inspector and set the SensitivityLabel for the current Word.Document, which is the current email?

Not exactly following you.

I have code that will apply the sensitivity label when you save an Excel File and it works properly.

I assume the same code would work for Word files.

In theory, the same code SHOULD work for Outlook E-mails, but it doesn't work when I tried it. (I'm thinking a future update to Outlook may fix it, but so far no luck.)

 

[Pat Hartman]

I thought issuing Cancel = True cancelled the form update?
Can you really reverse it later with Cancel = False ??

Genuinely, never seen that done like that before.

If I have a lengthy BeforeUpdate procedure and would have a lot of places to set Cancel = True, I sometimes start the procedure with Cancel = True. Then as the validation progresses, when an error is found, a message is displayed and the procedure is exited. Then the last statement of the procedure is Cancel = False.

The Cancel argument isn't processed by Access until the end of the procedure so toggling it has no impact. All that matters is the value when the procedure ends.

 

[sonic8]

In theory, the same code SHOULD work for Outlook E-mails, but it doesn't work when I tried it.

I read that in your first post. - But what and how exactly did you try?
The WordEditor property of the Outlook.Inspector displaying an email contains the email's text as Word Document. So, what if you apply the SensitivityLabel to that Word Document? I think, there is a slim chance that it will propagate to the email. - I cannot try this myself because there are no SensitivityLabels defined in my organization.

 

[Marshall Brooks]

@sonic8 - I think that would only work - if it works - assuming everyone has "Use Word as document editor" or similar set in Outlook - which I'm not sure they do.

Lots of info on the web - some conflicting.

https://stackoverflow.com/questions/74712674/add-sensitivity-label] - This or similar is what I am using for Excel. Works great.

I assumed, but didn't test that the same GUID was used for outlook and tried to add it to the olMailItem. I got an error that the object does not support this property - although I see it in the properties.

https://stackoverflow.com/questions/72230105/changing-sensitivitylabel-in-outlook-365-email-with-vba -This is what I'm currently using, but I modified it to use wshShell.Sendkeys b/c otherwise it toggled the NumLock key. It works fine for me, but another user always clicks the mouse while the E-mail is generating and ends up with AY in a database field. I'm trying to disallow that in Access - but even if I succeed, he will still have the potential issue of the label not being applied to the his e-mail and (possibly more critically), potentially Alt-H, AY, Down, Down, Right, Enter being sent to some other application than Access.

https://stackoverflow.com/questions...ection-labels-to-an-outlook-email-using-acces - info here, but I didn't try it.

https://stackoverflow.com/questions/49003285/azure-classification-using-vba - I'm thinking of using Hapi's solution here, where he creates a template E-mail and sets the sensitivity to the template and then opens that via VBA. I think it will work, but he uses a subfolders of the drafts folder. Probably a good choice, but I have a script that sends all draft E-mails (don't think it sends from subfolders), and a 64-day retention policy on the draft E-mail folder - but I can set a different policy on subfolder. I'm going to try to modify it to work on a subfolder of the Inbox first though.

 

[Marshall Brooks]

https://stackoverflow.com/questions/6105064/access-outlook-default-folder - info here on how to use a different folder than drafts.

 

[sonic8]

I think that would only work - if it works - assuming everyone has "Use Word as document editor" or similar set in Outlook - which I'm not sure they do.

Well, that is certainly true. - However, I think it is a non issue if your users use a current version of Outlook 365. In the current Outlook version using Word as email editor is the default and you cannot turn it off anymore.
Of course, my suggestion can still fail, if the SensitivityLabel cannot be applied to that Word Document or if it doesn't propagate to the email.

The approach of creating an email template with the SensitivityLabel already applied and then creating the new emails based on that template sounds very promising to me.

It will be mostly up to you to try these things out, as the SensitivityLabels are not used that widely (yet).

 

[Marshall Brooks]

Almost working for the template with the sensitivity label applied.
If I create a folder named "VBA Templates" inside my Drafts folder and use this line:
Set olFolder = Application.GetNamespace("MAPI").GetDefaultFolder(16).Folders("VBA Templates")
It works properly.
If I move the folder to below my E-mail address (and outside the inbox) and use this line:
Set olFolder = Application.GetNamespace("MAPI").Folders("VBA Templates")
It can't find the folder, although I would have expected that to work ...

 

[sonic8]

If I create a folder named "VBA Templates" inside my Drafts folder and use this line:
Set olFolder = Application.GetNamespace("MAPI").GetDefaultFolder(16).Folders("VBA Templates")
It works properly.

That's very good news!

If I move the folder to below my E-mail address (and outside the inbox) and use this line:
Set olFolder = Application.GetNamespace("MAPI").Folders("VBA Templates")
It can't find the folder, although I would have expected that to work ...

You are looking for the folder on the wrong level. The top level of folders is the mailbox(es) and below that are the individual folders.

This will work:

Set olFolder = Application.GetNamespace("MAPI").Folders("YourAccountName").Folders("VBA Templates")

Or without needing to know the primary account name:

Set olFolder = Application.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox).Parent.Folders("VBA Templates")

 

[Marshall Brooks]

Figured it out, but ...

We were cross-posting ... I figured out from https://stackoverflow.com/questions/17049546/get-mapi-folder-in-outlook-from-folder-path that I had to use my username - which won't work for other users.

Your final line looks like what I want. I'll try that and report back!!!

THANK YOU!!!

 

[Marshall Brooks]

It worked before, but it isn't working now.

Now it finds the folder, but instead of opening a copy of the template, it is in a loop making multiple copies in the folder until I close Outlook.
He is the (messy and slightly obfuscated) code:
Calling procedure:

Sub Test2()
Call SendEmail(, , "ECI")
End Sub

Sub:

Sub SendEmail(Optional fArr, Optional Action = "Show", Optional SensLabel = "Unrestricted")
' Don't forget to copy the function GetBoiler in the module.
' https://www.rondebruin.nl/win/s1/outlook/signature.htm
' http://www.vbaexpress.com/forum/showthread.php?52440-Inserting-non-default-signature-with-picture-in-Outlook-e-mail -modifying signature to work with E-mail.
' Working in Office 2000-2016
' Relies on module RdB_Email (Ron deBruin) - https://www.rondebruin.nl/win/s1/outlook/openclose.htm
' 12-Jul-2023 - WshShell.SendKeys is used rather than simply SendKeys to avoid unintended toggling of the NumLock key. MB.
Dim OutApp As Object
Dim OutMail As Object
Dim SigString As String
Dim Signature As String
Dim WshShell As Object
Dim I As Integer
Screen.MousePointer = 11
' Pause below is required to prevent intermittent "Server Execution Failed" errors. Solution found - 15-Dec-22.
Pause (2)
' Set OutApp = CreateObject("Outlook.Application")
Set OutApp = OutlookApp()
If IsM365 = True Then
    Set OutMail = getVbaTemplateMail("VBA Template - Sensitivity=General")
    Dim olFolder  As Outlook.MAPIFolder
    Dim olItem As Outlook.MailItem
'    Dim OutMail As Outlook.MailItem
    'GetDefaultFolder(16) = Draft folder, "Drafts/VBA Templates" is my VBA Template folder
'    Set olFolder = Application.GetNamespace("MAPI").GetDefaultFolder(16).Folders("VBA Templates")
    Set olFolder = OutApp.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox).Parent.Folders("VBA Templates")
    For Each olItem In olFolder.Items
        If SensLabel = "ECI" Then
            If olItem.SUBJECT = "VBA Template - Sensitivity=_ECI" Then
                Set OutMail = olItem.Copy
                'If error "Active Inline Response" appears, the mail is open in Outlook, close it first!
            End If
        Else
            If olItem.SUBJECT = "VBA Template - Sensitivity=Unrestricted" Then
                Set OutMail = olItem.Copy
                'If error "Active Inline Response" appears, the mail is open in Outlook, close it first!
            End If
        End If
    Next
Else ' Not M365
    Set OutMail = OutApp.CreateItem(0)
End If
SigString = Environ("appdata") & "\Microsoft\Signatures\CompanyName.htm"
If LenB(Dir(SigString)) <> 0 Then
    Signature = GetBoiler(SigString)
Else
    Signature = ""
End If
On Error Resume Next
With OutMail
    If Action = "Show" Then
        .Display
'        If IsM365 = True Then
'            ' https://stackoverflow.com/questions/25977933/sendkeys-is-messing-with-my-numlock-key-via-vba-code-in-access-form
'            Set WshShell = CreateObject("WScript.Shell")
'            WshShell.SendKeys "%h" ' Alt H - gets the home menu
'            WshShell.SendKeys "AY" ' Sensitivity Label
'            If SensLabel = "ECI" Then
'                WshShell.SendKeys "{DOWN}{DOWN}{DOWN}{DOWN}{DOWN}{DOWN}{DOWN}{DOWN}{DOWN}{RIGHT}{ENTER}" ' Export Controlled'
'            Else
'                WshShell.SendKeys "{ENTER}" ' Change the number of {DOWN} to select a particular value from your list - Unrestricted
'            End If
'            Set WshShell = Nothing
'        End If
    End If
    .To = EmailTo
    .CC = EmailCC
    .BCC = EmailBCC
    .SUBJECT = EmailSubject
    .HTMLBody = strbody & Signature
    '        .WindowState = olMinimized
    ' does not seem to work - would like to use olNormal or olNormalWindow but those don't work either.
    If LenB(AttachFile) <> 0 Then .Attachments.ADD AttachFile
    If LenB(AttachFile1) <> 0 Then .Attachments.ADD AttachFile1
    If LenB(AttachFile2) <> 0 Then .Attachments.ADD AttachFile2
    ' Both of the statements below seem to evaluate to true - i.e. the for loop is executed, but the code seems to work normally.
    ' If Not IsEmpty(fArr) Then
    If UBound(fArr) <> 0 Then
        For I = 1 To UBound(fArr)
            .Attachments.ADD fArr(I)
        Next
    End If
'   .OriginatorDeliveryReportRequested = True ' delivery confirmation
'   .ReadReceiptRequested = True ' read confirmation
' Commented out Send - it now pops up a confirmation dialog.
'    If Action = "Send" Then
        ' https://www.rondebruin.nl/win/s1/security.htm - Send gives a warning message that you have to display.
'        '.Send
'        .Display
'        Pause (2)
'        SendKeys "%s"
    If Action = "Save" Then
'        If IsM365 = True Then
'            .Display
'            Set WshShell = CreateObject("WScript.Shell")
'            WshShell.SendKeys "%h" ' Alt H - gets the home menu
'            WshShell.SendKeys "AY" ' Sensitivity Label
'            If SensLabel = "ECI" Then
'                WshShell.SendKeys "{DOWN}{DOWN}{DOWN}{DOWN}{DOWN}{DOWN}{DOWN}{DOWN}{RIGHT}{ENTER}" ' Export Controlled'
'            Else
'                WshShell.SendKeys "{ENTER}" ' Change the number of {DOWN} to select a particular value from your list - Unrestricted
'            End If
'            Set WshShell = Nothing
'            Pause (2)
'            ' https://www.mrexcel.com/board/threads/vba-e-mail-window-close.865000/
'            .Close olSave
'        Else ' Not M365
            .Save
'        End If
    End If
End With
Screen.MousePointer = 1
On Error GoTo 0
' Variables should be reset in the calling procedure.
'EmailTo = ""

'OutMail.Quit - Not supported
Set OutMail = Nothing
'OutApp.Quit Closes Outloook
Set OutApp = Nothing
End Sub

 

[Marshall Brooks]

I think I just need an "Exit For" after each Set OutMail = olItem.Copy

 

[sonic8]

I think I just need an "Exit For" after each Set OutMail = olItem.Copy

That's what I think too.

 

[Marshall Brooks]

Still not working, but that did cancel the loop.

If I change Is M365=True to False, it works (but it just opens a blank E-mail with no sensitivity label).

If I step through the code with Is M365 = True, it seems to work, but it never displays the E-mail - although it seems to hit the .Show line.

I think it either didn't make the copy or didn't set outMail to the copy or didn't display it.

 

[Marshall Brooks]

SUCCESS!!!

Not sure why this works and the other method doesn't - but it's just as well or maybe better for me.

I saved my templates as msg files on my U:\Drive.

Then I changed:
Set OutMail = olItem.Copy
to
Set OutMail = OutApp.CreateItemFromTemplate("U:\Outlook_VBA_Templates\Unrestricted.msg")
(And commented out the other lines).

Not sure why the original method failed and this works, but it does.

I'm going to mark this as "Solved" - although the solution was to avoid using SendKeys!!!

 

[sonic8]

I'm going to mark this as "Solved" - although the solution was to avoid using SendKeys!!!

I very much hope that the solution for all threads about SendKeys will be: "avoid SendKeys".

Good that you solved it and thank you for posting your solution!

 

[Marshall Brooks]

I very much hope that the solution for all threads about SendKeys will be: "avoid SendKeys".

Yep, they are evil.

Odd thing is they worked fine for me. We got a new user in the group and suddenly is was "Why do you have "AY" in this field?" and then "Don't move the mouse when the database is going to generate an E-mail!!!" and it STILL happened.

Glad I found a (still crude but) more robust solution.

And thanks to all for the help. Two good things came out of the thread: 1) We aren't using SendKeys anymore. 2) The previous solution to the status field I still like. There is no more annoying pop-up about only selecting entries from the drop-down list, it just cancels the input if it is not valid.