MobileMobile | Continue

Excel Bar

Excel Bar

Your excel questions will be responsed by our excel experts within 24hrs.Our service is free.

 Forgot Pass?
 Register Now
Find
Hot Search: Vlookup Match VBA
View: 275|Reply: 7

Macro to close outlook

[Copy link]

14

Topics

68

Posts

168

Integral

Conqueror

Rank: 3Rank: 3

Integral
168
Post on 1-16-2017 09:13:42 | All posts |Read mode
Is it possible to have a macro that will close outlook after the outbox is empty? maybe set a 10 sec delay timer at end of code.Have a code in excel to open outlook,now I would like a way to auto close after the file from excel is out of the outbox. If file is not sent then outlook stays open. Not sure if a macro from excel can do this,I still need to open outlook and send emails when outbox is empty.
                                                      Thanks for any suggestions.




Reply

Props Report

14

Topics

68

Posts

168

Integral

Conqueror

Rank: 3Rank: 3

Integral
168
 Author| Post on 1-16-2017 10:10:02 | All posts



Not sure I understand the problem
The procedure below is a generalised version of one I use a lot - (based on one of Ron de Bruin's examples)
With the .Display and .Close(0) commented out the email is sent and closed automatically.
If you choose to use the .Display allowing you to view the email then the .Close(0) line is the one that will close the email but of course since you're able to view the email you could close it anyway.
Sub SendeMail()
    Dim OutApp As Object
    Dim OutMail As Object
    Set OutApp = CreateObject(Outlook.Application)
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
    With OutMail
        .to = email address
        .cc =
        .BCC =
        .Subject = Subject
        .Body = Here is the body message
        .Send   'or use .Display
        '.display
        .Close (0)
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
  
End SubCan you clarify the point you're making?











Reply Support Opposition

Props Report

3

Topics

272

Posts

637

Integral

Lord

Rank: 4

Integral
637
Post on 1-16-2017 10:50:57 | All posts



Thanks Richard
Sometimes the email will get hung up in the outbox,if I don't catch it it will stay there.Looking for a idiot message box telling me message was sent and do I want to close program.




Reply Support Opposition

Props Report

3

Topics

272

Posts

637

Integral

Lord

Rank: 4

Integral
637
Post on 1-16-2017 12:29:09 | All posts



Sub CheckOutbox()
    Dim oOutlook As Object
    On Error Resume Next
    Set oOutlook = GetObject(, Outlook.Application)
   
    If oOutlook.Session.GetDefaultFolder(olFolderOutbox).Items.Count  0 Then
        MsgBox A send/receive just completed and there are still unsent items in your Outbox., vbExclamation + vbOKOnly, Unsent Item Warning
    Else
        MsgBox No items in outbox
    End If
End Subi just took the syntax of checking if outbox is empty from here
https://www.experts-exchange.com/que...in-Outbox.html
worked on my test setup
believe it only checks the default outlook account not all accounts so if this is not the case...cross that bridge later if needed












Reply Support Opposition

Props Report

3

Topics

272

Posts

637

Integral

Lord

Rank: 4

Integral
637
Post on 1-16-2017 12:34:23 | All posts



oh forgot the closing outlook bit


Sub CheckOutbox()
    Dim oOutlook As Object
    On Error Resume Next
    Set oOutlook = GetObject(, Outlook.Application)
   
    If oOutlook Is Nothing Then
        MsgBox Outlook is not open, open Outlook and try again
    ElseIf oOutlook.Session.GetDefaultFolder(olFolderOutbox).Items.Count  0 Then
        MsgBox A send/receive just completed and there are still unsent items in your Outbox., vbExclamation + vbOKOnly, Unsent Item Warning
    Else
        If MsgBox(No items in outbox, Do you want to close Outlook, vbYesNo + vbQuestion, Close Outlook) = vbYes Then oOutlook.Quit
    End If
End SubCode seems to work better if Outlook is open
so left the outlook open check in there
Reply Support Opposition

Props Report

14

Topics

68

Posts

168

Integral

Conqueror

Rank: 3Rank: 3

Integral
168
 Author| Post on 1-16-2017 17:39:44 | All posts



oh forgot the closing outlook bit


Sub CheckOutbox()
    Dim oOutlook As Object
    On Error Resume Next
    Set oOutlook = GetObject(, Outlook.Application)
   
    If oOutlook Is Nothing Then
        MsgBox Outlook is not open, open Outlook and try again
    ElseIf oOutlook.Session.GetDefaultFolder(olFolderOutbox).Items.Count  0 Then
        MsgBox A send/receive just completed and there are still unsent items in your Outbox., vbExclamation + vbOKOnly, Unsent Item Warning
    Else
        If MsgBox(No items in outbox, Do you want to close Outlook, vbYesNo + vbQuestion, Close Outlook) = vbYes Then oOutlook.Quit
    End If
End SubCode seems to work better if Outlook is open
so left the outlook open check in there










Reply Support Opposition

Props Report

3

Topics

272

Posts

637

Integral

Lord

Rank: 4

Integral
637
Post on 1-16-2017 18:41:33 | All posts



Nice piece of work,this is a good start. Is there a way I could put this code in BeforeWorkbook Close event and make it work ?
Reply Support Opposition

Props Report

14

Topics

68

Posts

168

Integral

Conqueror

Rank: 3Rank: 3

Integral
168
 Author| Post on 1-16-2017 22:09:00 | All posts



Nice piece of work,this is a good start. Is there a way I could put this code in BeforeWorkbook Close event and make it work ?
Reply Support Opposition

Props Report

Points policy of this forum

Archiver|Mobile|Small dark house|Contact us|Excel Bar

GMT-5, 9-24-2017 21:22 , Processed in 0.243545 second(s), 20 queries .

Powered by Discuz! X3

© 2001-2013 Comsenz Inc.

!fastreply! Top !return_list!