Excel VBA to Test if Exchange is Online/Offline and set to Offline if Online

Hello Experts,

I have an Excel VBA routine that emails an attachment using the Outlook application (see below).  I would like to check to see if the user's Outlook/Exchange is "Connected to Microsoft Exchange" or "Offline".  If it is connected, I want to set it to "Offline" so the email is sent but stays in the users Outbox until they release them by reconnecting to Exchange.

Any help modifying the code below would be appreciated.


' Set up email addresses, subject line, body text and temp file attachment before this section...

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    With wb2
        With OutMail
            .To = EmailAddr
            .CC = MgrEmailAddr
            .BCC = ""
            .Subject = Subj
            .Body = BodyText
            .Attachments.Add SaveAsName
        End With
        On Error GoTo 0
        .Close SaveChanges:=False
    End With

    Kill SaveAsName

    Set OutMail = Nothing
    Set OutApp = Nothing

'More code.....

Open in new window

LVL 16
Jerry PaladinoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brad Sims, CCNANetwork AdministratorCommented:
Add the line below to your code after the logon line:

 OutApp.GetNamespace("MAPI").Folders.GetFirst.GetExplorer.CommandBars.FindControl(, 5613).Execute
Jerry PaladinoAuthor Commented:
Thank you!   I am not familiar with the Outlook Object model and Outlook VBA.   Is there a way to test the value to see if Exchange is Connected or Offline?   The code you provided acts as a "Toggle".  Each time it is executed it toggles Exchange between Connected and Offline.
My purpose is to test and ensure that Exchange is Offline when the application is being tested so no emails are sent to the end users during testing.  I will not know what the testers current state is so just executing the new line of code could put it back to a Connected status if they had manually taken Exchange offline.
Thank You,
Rory ArchibaldCommented:
should give you the current status.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

Jerry PaladinoAuthor Commented:
John / Rory,

Thank you both.  I'll split points on this one since John provided the On/Off switch and Rory provided the code to test if Exchange is On/Off.

Jerry PaladinoAuthor Commented:
Any suggestions on a book or web site where I can get a beginner's view on the Outlook model?   Once I have the syntax I can find it in the VBA help but maybe something at a more basic level to that I can get started with.
Thanks - Jerry
Rory ArchibaldCommented:
Have a look at Sue Mosher's books.
Jerry PaladinoAuthor Commented:
OK - Thanks!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.