Prompt Message After Sending Outlook Mail using Excel VBA

Question

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.

Try `AppActivate Application.Caption` to bring Excel forward. — Tim Williams, Dec 11 '18 at 06:00
Try another approach eg from here - https://stackoverflow.com/questions/34481674/vba-api-declarations-bring-window-to-front-regardless-of-application — Tim Williams, Dec 11 '18 at 06:11
it's also not working for some reason, I will keep trying and searching and I will update you if I find anything. — Amir Al Yousfi, Dec 11 '18 at 07:19

Amir Al Yousfi · Accepted Answer · 2018-12-11T11:40:53.567

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

score 0 · Answer 2 · answered Dec 11 '18 at 07:21

0

I've had to deal with stubborn applications myself. Try hiding the application then showing it before your msgbox.

If DB.ReadOnly Then
    Application.Visible = False
    Application.Visible = True
    MsgBox "Error Message Here"
End If

Probably not the most elegant of solutions - but it usually works.

answered Dec 11 '18 at 07:21

K.Dᴀᴠɪs

9,945
11
33
43

I've just tested this, but it didn't work either, I believe the issue is that the code is running what the (Private Sub itm_Send(Cancel As Boolean)) is still running. – Amir Al Yousfi Dec 11 '18 at 08:14
is there any way to run the code after exiting the Send Sub?; I checked around but I couldn't find anything. – Amir Al Yousfi Dec 11 '18 at 08:14

Prompt Message After Sending Outlook Mail using Excel VBA

2 Answers2