I have an Excel UserForm which upon a button click generates an Outlook E-mail and the user manually clicks the send option on the mail.
The system will register the E-mail content in an Excel database (only if the user clicks on the Send Option in Outlook through an Withevents Class).
If the database is not available there is an error message which should prompt the user. The prompt is not showing to the user (covered by the Outlook E-mail) because Excel code is processing and the E-mail sending process will be waiting for it to be done.
Is there any way I can show the message box on top of Outlook or run the code to save to the database but only AFTER the Send option is clicked?
The code in the Userform to fill and display the E-mail in Outlook.
Public itmevt As New CMailItemEvents
Public Outapp As Outlook.Application
Public Outmail As Outlook.MailItem
public subject as string
public body as string
Private Sub SendMail_Click()
Set Outapp = New Outlook.Application
Set Outmail = Outlook.Application.CreateItem(0)
Set itmevt.itm = Outmail
body=userform.text1.text
subject=userform.text2.text
itmevt.itm.Subject = "Some Subject"
With itmevt.itm
.HTMLBody = Body
.Subject = subject
.Display
End With
this is the code for the Class called (CMailItemEvents) to detect the Send Option Click
Option Explicit
Public WithEvents itm As Outlook.MailItem
Private Sub itm_Send(Cancel As Boolean)
EmailsForm.savedetails
End Sub
once the Send Option is clicked the code to save will run
sub savedetails()
--->Open Excel DB
If DB.ReadOnly Then
Msgbox ("Error Message Here") ----> here is the problem, the message shows on excel
--- but the outlook mail is on the front of the screen
exit sub
else
--->Save details to DB
End Sub
I tried to keep the code sample as short and simple as possible.
I was finally able to do it with a workaround, I'm not sure if this is going to help anyone.
I've created another event watcher to detect when the E-mail window is actually closed, and according to that the message will be triggered.
this is the updated Class to detect the Send click & the E-mail deactivation event:
Option Explicit
Public WithEvents itm As Outlook.MailItem
Public WithEvents appv As Inspector ----> this part is added
Public Sent as Boolean
Private Sub itm_Send(Cancel As Boolean)
Sent=True ---> Sending E-mail Check
EmailsForm.ETo = itm.To
EmailsForm.ECC = itm.CC
EmailsForm.savedetails
End Sub
---This Part Is Added---
Private Sub appv_Deactivate()
If Sent = True then ---> Sending E-mail Check To Avoid Triggering the watcher if the E-mail is closed without sending
if EmailsForm.Bool=true then
msgbox ("Error Message Here")
EmailsForm.Book=False
Sent=False
End If
End If
End Sub
when the user click the button on the user form the following code it triggered:
Public itmevt As New CMailItemEvents
Public Outapp As Outlook.Application
Public Outmail As Outlook.MailItem
public subject as string
public body as string
Private Sub SendMail_Click()
Set Outapp = New Outlook.Application
Set Outmail = Outlook.Application.CreateItem(0)
Set itmevt.itm = Outmail
Set itmevt.appv = Outmail.GetInspector ----> this is added to link the E-mail window to the deactivation trigger
body=userform.text1.text
subject=userform.text2.text
itmevt.itm.Subject = "Some Subject"
With itmevt.itm
.HTMLBody = Body
.Subject = subject
.Display
End With
I've added a Boolean to be checked from the call
public Bool as Boolean
sub savedetails()
Bool=false ---> Boolean to be checked by the class
--->Open Excel DB
If DB.ReadOnly Then
Bool=true
exit sub
else
--->Save details to DB
End Sub
I hope the above is clear and can help anyone with similar issue; thank you for your support everyone