View original (French)

Outlook au premier plan

Flomen -  
RV71 Posted messages 613 Registration date   Status Membre Last intervention   -
Hello,

My problem:

Through a button in Access, I prepare an email that I send using .display or .send in VBA.
Everything works correctly.

However, Outlook opens in the background, and the Access application stays in the foreground! I would like Outlook to come to the foreground after sending the .display or .send command.

Do you have the solution?

Thank you

Configuration: Android / Chrome 81.0.4044.96

18 réponses

Réponse 1 / 18
yg_be Posted messages 23437 Registration date   Status Contributeur Last intervention   Ambassadeur 1 588
 
Hello, you will allow us to help you better by showing us your code.
0
yg_be Posted messages 23437 Registration date   Status Contributeur Last intervention   1 588
 
Could it be that your VBA code brings Access to the foreground?
0
Réponse 2 / 18
flomen
 
Bonjour,
Here is my code that works very well, but I can't bring Outlook to the forefront!

Thank you for your help

My Code:

Function EnvViaOutlook(DestMail As String, CCMail As String, SujetMail As String, TxtMail As String, Signature As String, Optional PJ1 As String) As String
Dim OL As Outlook.Application, mi As Outlook.MailItem
On Error GoTo OLMailErr
Set OL = New Outlook.Application
Set mi = OL.CreateItem(olMailItem)
With mi
.To = DestMail
.CC = CCMail
.Subject = SujetMail
.Body = TxtMail
.HTMLBody = "Test Signature" & "
" & .HTMLBody
.Display
End With
Set mi = Nothing: Set OL = Nothing
Exit Function
OLMailErr:
MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
Exit Function
End Function
0
yg_be Posted messages 23437 Registration date   Status Contributeur Last intervention   1 588
 
how do you use this function? maybe it's after calling it that Access does something and comes back to the foreground.
0
Réponse 3 / 18
flomen
 
Hello,
In a function, I call this function to create an email and then display it (.Display) so that the user can adapt it.
You can try with the code I attached
Thank you
0
Réponse 4 / 18
RV71 Posted messages 613 Registration date   Status Membre Last intervention   31
 
Hello,

you can test this code directly in a button's sub to see... 

 Private Sub boutonTest_Click() Dim OL As Outlook.Application, mi As Outlook.MailItem On Error GoTo OLMailErr Set OL = New Outlook.Application Set mi = OL.CreateItem(olMailItem) With mi .To = DestMail .CC = CCMail .Subject = SujetMail .Body = TxtMail .HTMLBody = "Test <b>Signature<\b>" & "<br>" & .HTMLBody .Display End With Set mi = Nothing: Set OL = Nothing Exit Sub OLMailErr: MsgBox "Error: " & Err.Number & vbCrLf & Err.Description Exit Sub End Sub
0
Réponse 5 / 18
flomen
 
Hello,
Your solution changes nothing; it's exactly the same code with Function replaced by SUB.
If you have Access, you can verify this by testing the code; it just lacks the command to bring Outlook to the foreground.
Thank you.
0
f894009 Posted messages 17417 Registration date   Status Membre Last intervention   1 717
 
Hello everyone,

I have been following this request from the beginning and as others were taking care of it, I handed it off temporarily.
flomen:
I tested your code on Access and there’s no problem, Outlook is indeed in the foreground with .display. Which is normal. I do this regularly, mainly with Excel to test the content of the emails to be sent, but it doesn’t change anything in that regard.
0
yg_be Posted messages 23437 Registration date   Status Contributeur Last intervention   1 588
 
I don’t think it’s useful to bring Outlook to the foreground, because it’s already in the foreground at the time of the .display. Access then comes back to the foreground, perhaps due to an action taken in the calling function.
Can you share your file?
0
yg_be Posted messages 23437 Registration date   Status Contributeur Last intervention   1 588
 
Have you tested the RV71 suggestion (via a simple button)?
0
Réponse 6 / 18
flomen
 
Hello,
What do you mean by A simple button?
I'm testing with a button that I have set to execute the code on the click event
Look at the code and tell me what needs to be modified or do you have another code to display an email before sending it
Thank you
0
yg_be Posted messages 23437 Registration date   Status Contributeur Last intervention   1 588
 
Can you share your file?
0
Réponse 7 / 18
flomen
 
Hello,
I don't understand your request: Can you share your file?
Which file are you talking about?
0
Réponse 8 / 18
RV71 Posted messages 613 Registration date   Status Membre Last intervention   31
 
Have you tried putting the code on a button?

Because I did it (without using a function) and it works.

As yg_be said, share your database (removing strictly confidential data), for example by using https://fr.fromsmash.com/

See you!
0
Réponse 9 / 18
flomen
 
Hello,
It doesn't work "SUB" and "FUNCTION"
I have a mini database of 692 K, how can I share it with you?
0
Réponse 10 / 18
RV71 Posted messages 613 Registration date   Status Membre Last intervention   31
 
As mentioned just above (read): https://fr.fromsmash.com/ or given the small size https://www.cjoint.com/

See you!
0
Réponse 11 / 18
flomen
 
I just shared the Access database, here is the link https://www.cjoint.com/c/JDup5xiHoxP

Thank you for your help
0
yg_be Posted messages 23437 Registration date   Status Contributeur Last intervention   1 588
 
I tested with your file, and the message appears in the foreground.
I tested with Outlook already open, or not: same behavior.
0
Réponse 12 / 18
RV71 Posted messages 613 Registration date   Status Membre Last intervention   31
 
There were plenty of checked references, I removed them, and it seems to be OK.

https://www.cjoint.com/c/JDuqHu51InU

A+
0
Réponse 13 / 18
flomen
 
Thank you for your help,
I just tried with the database you corrected (3 references) unfortunately it still doesn't work!
For your information, I am using Office 2013 and Windows 10

Thanks again, maybe you will have another solution!
0
flomen
 
I have another question, with this method I can create an email and then send it, however I would like to insert the signature as it is defined in Outlook, I haven't found the solution
Thank you
0
f894009 Posted messages 17417 Registration date   Status Membre Last intervention   1 717 > flomen
 
Hello everyone,

Signature:
https://www.rondebruin.nl/win/s1/outlook/signature.htm
0
flomen > f894009 Posted messages 17417 Registration date   Status Membre Last intervention  
 
Thank you, it works perfectly, I was able to integrate a logo (jpg) into the signature using HTML code.
0
Réponse 14 / 18
RV71 Posted messages 613 Registration date   Status Membre Last intervention   31
 
Hello,

honestly I'm stuck, since it works on my end (Win7 + Office 2013 32bits).

Can you try the database on another machine??

See you!
0
Réponse 15 / 18
RV71 Posted messages 613 Registration date   Status Membre Last intervention   31
 
I tested it by opening Outlook first, that works too, maybe it will work for you then.
However, it's messy because if there’s a change in the Outlook version, the VBA will need to be modified...

See the modified file here --> https://www.cjoint.com/c/JDvmTYg3xgU
0
flomen
 
Indeed, with this method it works; if the user sends multiple emails, with this method, each time a new Outlook session is opened, so many sessions are open!
Thank you for all the time.
0
RV71 Posted messages 613 Registration date   Status Membre Last intervention   31 > flomen
 
How so?

If he closes Outlook, the session closes, right?

See you!
0
flomen > RV71 Posted messages 613 Registration date   Status Membre Last intervention  
 
When I say multiple sessions, I mean all the sessions opened with each email sent. Perhaps we can close the Outlook session that we have opened? The act of sending the email closes the email window but not Outlook!
0
Réponse 16 / 18
RV71 Posted messages 613 Registration date   Status Membre Last intervention   31
 
Since the Outlook window is in front of the Access window, the user will have to close it to return to Access, unless they intentionally minimize it, but that’s not the usual behavior.

Otherwise, yes, we can kill the Outlook process (if it exists) before triggering the creation of the email…
0
Réponse 17 / 18
RV71 Posted messages 613 Registration date   Status Membre Last intervention   31
 
But that doesn’t explain why it doesn’t work without the shell...

Do you have Outlook set as the default program?

Cheers!
0
yg_be Posted messages 23437 Registration date   Status Contributeur Last intervention   1 588
 
At my place, Outlook is not set as the default email program, and everything works fine with the starting file from Flomen.
0
flomen
 
Would you have the code to close Outlook, which would allow me to use your method: Opening Outlook with the code you gave me, sending the email, and finally closing Outlook to avoid having stacked sessions.
0
flomen > yg_be Posted messages 23437 Registration date   Status Contributeur Last intervention  
 
No, it was not set by default, I set it by default but it does not change anything!
I tried on a second computer, same problem! Outlook is still hiding!
0
RV71 Posted messages 613 Registration date   Status Membre Last intervention   31 > flomen
 
You cannot know in Access what the user has done in Outlook, so at what point to close it.
Access no longer has control when the user is writing their message in Outlook.
You need to:
1/ check if Outlook is open
2/ if it is open, close it
3/ restart Outlook with the code

But I still believe that 99% of users close Outlook to return to Access...
0
Flomen > RV71 Posted messages 613 Registration date   Status Membre Last intervention  
 
Hello,
I am still looking for a solution,
I am not the only one with the problem see:

https://www.commentcamarche.net/forum/affich-34949745-forcer-la-fenetre-d-envoi-outlook-en-premier-plan-access-2013-16

I have tried it on another computer after fully reinstalling Windows 10 and Office 2013, still the problem!
Where I have a doubt is on Windows 10 because you told me it works, the difference is that you are on Windows 7.
I will see if I can reinstall Windows 7.
Thank you
0
Réponse 18 / 18
RV71 Posted messages 613 Registration date   Status Membre Last intervention   31
 
Well,

here's the code to put on the button: 

Private Sub Go_Click() '--- Close the Outlook Process --- Dim ovc As Object Dim sQuery As String Dim oproc Set ovc = GetObject("winmgmts:root\cimv2") sQuery = "select * from win32_process where name='" & "Outlook.EXE" & "'" For Each oproc In ovc.execquery(sQuery) oproc.Terminate Next Set ovc = Nothing ' restart outlook Dim SessionOutlook Const Path As String = "C:\Program Files\Microsoft Office\office15\OUTLOOK.exe" ' ---> adjust this path if necessary SessionOutlook = Shell(Path, 1) Dim MyOutlook As Outlook.Application Dim MyMessage As Outlook.MailItem On Error GoTo Err_sendMail ' ===== Initialization ===== Set MyOutlook = CreateObject("Outlook.Application") Set MyMessage = MyOutlook.CreateItem(0) ' ===== Create the email ===== With MyMessage .To = "menon.florent@gmail.com" .Subject = "Subject of this email" .Body = "Here is the content of the email to be sent" ' Display the email (comment the line for background task sending) .Display ' Send the email automatically (uncomment the line for background task sending) '.Send End With Exit_sendMail: Set MyOutlook = Nothing Exit Sub Err_sendMail: Select Case Err.Number Case Else MsgBox "Error No." & Err.Number & vbCrLf & "Description : " & Err.Description & vbCrLf & "Source : " & Err.Source, vbCritical, "Error" End Select Resume Exit_sendMail End Sub
0