[Webinar] Streamline your web hosting managementRegister Today


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

Posted on 2010-04-10
Medium Priority
Last Modified: 2012-05-09
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

Question by:Jerry Paladino
  • 4
  • 2

Assisted Solution

by:Brad Sims, CCNA
Brad Sims, CCNA earned 1000 total points
ID: 30424532
Add the line below to your code after the logon line:

 OutApp.GetNamespace("MAPI").Folders.GetFirst.GetExplorer.CommandBars.FindControl(, 5613).Execute
LVL 16

Author Comment

by:Jerry Paladino
ID: 30429235
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,
LVL 85

Accepted Solution

Rory Archibald earned 1000 total points
ID: 30503865
should give you the current status.
Free tool for managing users' photos in Office 365

Easily upload multiple users’ photos to Office 365. Manage them with an intuitive GUI and use handy built-in cropping and resizing options. Link photos with users based on Azure AD attributes. Free tool!

LVL 16

Author Closing Comment

by:Jerry Paladino
ID: 31761414
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.

LVL 16

Author Comment

by:Jerry Paladino
ID: 30510101
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
LVL 85

Expert Comment

by:Rory Archibald
ID: 30510204
Have a look at Sue Mosher's books.
LVL 16

Author Comment

by:Jerry Paladino
ID: 30511425
OK - Thanks!

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Exchange database can often fail to mount thereby halting the work of all users connected to it. Finding out why database isn’t mounting is crucial and getting the server back online. Stellar Phoenix Mailbox Exchange Recovery is a champion product t…
Often, the users face difficulty in accessing Outlook 2016 PST files on Windows 10 computer. One of the reasons behind it is the improper functioning of MS Outlook when the user tries to open it. MS Outlook suddenly stops working, or it will not op…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Suggested Courses
Course of the Month11 days, 7 hours left to enroll

640 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question