Solved How to prevent users saving mail into drafts? (1 Viewer)

XelaIrodavlas

Registered User.
Local time
Today, 09:52
Joined
Oct 26, 2012
Messages
174
VBA Scripted Outlook Email with sent/cancelled checker, how do I stop Users Saving as a draft?

Hi all, been a while since i logged in!

I have some code that writes an email in Outlook, and returns true or false depending on whether the user sent the email (or clicked cancel). This is working to 99% satisfaction, however I've hit a snag I can't get my head around:

When user hits the x on Outlook they get three options: Cancel, Close without saving, or Close and Save a Draft. It's the third option that presents a problem, as it means the users can cancel (telling my database the email wasn't sent) but then go back into their drafts and send it later, without going through the database (= email not tracked, = data is lost).

Ideally I need a way to prevent this, so the user can either send the email or cancel it without saving, and nothing else. I know you guys are all geniuses, so any ideas???

Failing that, anyone know a way to determine if the user saved a draft copy? At least I can rig a caution pop-up.

Here's my code for reference:
Code:
Public Function TrackedEmail(vEmailTo As String, _
                             vSubject As String, _
                             Optional vBody As String, _
                             Optional vCC As String, _
                             Optional vBCC As String, _
                             Optional vAttachment1 As String, _
                             Optional vAttachment2 As String, _
                             Optional vAttachment3 As String) As Boolean
'Purpose: Script an email, display it, and return true/false if it was sent/cancelled
'+++++++++Example Application+++++++++
'
'Dim vCheck As Variant
'vCheck = TrackedEmail("AlexSalvadori@BlahBlah.com", _
'                      "TestEmailTracking", _
'                      "Hello Friend, <p> don't forget the HTML coding...</p>", _
'                      "SomeoneElse@BlahBlah.com", , _
'                      "C:\Users\AlexSalvadori\Desktop\SomeFile.jpg")
'
'If vCheck = True Then
'    MsgBox "Email was sent, hurray!"
'Else
'    MsgBox "Email wasn't sent (or something went wrong!) boo!"
'End If
'
'+++++++++++++++++++++++++++++++++++++


Dim TimeMA As String, StrBody As String
Dim vMsgSent As Boolean

On Error GoTo Error_Handler

'Reset in case of errors:
vMsgSent = False

'Create Outlook Mail Item:
Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0) '(olMailItem)
 
'Assign values to Email Variables:
    '1. Good Morning/Evening
    If Time() & gt < #12:00:00 PM# Then
        TimeMA = "Morning"
    Else
        TimeMA = "Afternoon"
    End If
    
    '2. Copy default Signature
    OutMail.Display
    Signature = OutMail.HTMLBody
    OutMail.Close olDiscard '(1) do not save
'[/Assign Values]

'Script and display the Email:
With OutMail
    .BodyFormat = olFormatRichText 'olFormatHTML
    .To = vEmailTo
    .CC = vCC
    .BCC = vBCC
    .Subject = vSubject
    .HTMLBody = "<p>Good " & TimeMA & ",</p>" & vBody & Signature
    If Len(vAttachment1 & vbNullString) > 0 Then .Attachments.Add vAttachment1       'E.G: "C:\BATTERY_ANALYSIS\MyDocument.Xlsx
    If Len(vAttachment2 & vbNullString) > 0 Then .Attachments.Add vAttachment2
    If Len(vAttachment3 & vbNullString) > 0 Then .Attachments.Add vAttachment3
    .Display True
End With
 
'Check if the email was sent or just closed
On Error Resume Next
vMsgSent = OutMail.Sent 'Required to generate an error
If Err = 0 Then
    'No message: email closed, OutMail still exists
    'OR, no message: email saved, OutMail still exists '####Still a loophole where user clicks exit but saves changes.... records as not-sent but they can go into drafts and send it anyway... may have to accept the risk.
    'vMsgSent = False
    TrackedEmail = False
Else
    'Message was sent: OutMail is null
    'vMsgSent = True
    'Record Comms and update LastEmailed Date:
    TrackedEmail = True
End If
'Reset error handling
On Error GoTo Error_Handler

'Cleanup & Exit:
Set OutMail = Nothing
Set OutApp = Nothing

Exit Function

'Error Handling
Error_Handler:
MsgBox "An error has occurred preventing Access from writing/recording your email correctly. Contact your administrator.", vbCritical, "TrackedEmail - Error"
Resume Next

End Function

Thanks All! :)
 

vba_php

Forum Troll
Local time
Today, 04:52
Joined
Oct 6, 2019
Messages
2,884
another option of course would be to simply prevent them from doing anything inside of access before ever opening the email client. you have a lot more options inside of access than you do inside of outlook. more than likely you can detect the button presses of dialog boxes like that, but finding internet help on that subject is probably impossible. vba code can do almost anything that interface clicking can do, thus it is probably possible, but as you have found out, the 0 / -1 options for detection are pretty basic by default.
 

jdraw

Super Moderator
Staff member
Local time
Today, 05:52
Joined
Jan 23, 2006
Messages
15,361
Gasman's approach seems reasonable, but the real question is what is the impact on the application when the user saves as draft then sends later.
Do the standard procedures allow the user to save to drafts, just so they can continue their normal processing later? Or is saving to drafts a signal for you to act?
Perhaps you could remove the X option and "force" the user to use your controls.
 

XelaIrodavlas

Registered User.
Local time
Today, 09:52
Joined
Oct 26, 2012
Messages
174
Thanks for the replies all, I considered adding more controls inside Access but figured it should be doable within Outlook, and turns out it is!

My first thought would be to just delete it.?

You have the subject and apparently you can locate by that and then delete it, it it exists?

http://www.snb-vba.eu/VBA_Outlook_external_en.html#L_3.2.3

HTH

How did I not know about this!? :whistle:

Note to future self: I did observe this method only deletes the oldest email with the specified subtitle, but I can work with that: might loop until it returns an object-not-found error, or just add the msgID to the subject so they're always unique... Either way we're good here. Thanks a tonne Gasman! :giggle:
 

Users who are viewing this thread

Top Bottom