Excel VBA Macros & "New" outlook

Question

I currently have an Excel document that runs a macro to create an email based on certain criteria on one of the sheets. The macro works correctly with "old" outlook, but I cannot get it to work with "new" outlook. I'm seeing issues with VBA Macros not being a part of "new" outlook but I'm curious if there's a way to update the Excel macros to include the "new" version of Excel.

Here's the email application of the macro:

On Error Resume Next 

Set olApp = GetObject(, "Outlook.Application") 

On Error GoTo 0 

If olApp Is Nothing Then 

    Set olApp = CreateObject("Outlook.Application") 

End If 

Set olMail = olApp.CreateItem(0)

Answer 1

I did a little research and I will start by saying that I will NOT be trying or switching to "new" Outlook any time soon if ever. This new version of Outlook is supposed to replace Mail and Calendar and is a web or JavaScript based app. It doesn't really replace "old" or classic Outlook as it does not support COM add-ins (VBA is a COM add-in.) Needless to say, the base of users that rely on Office VBA to get work done have been a little apprehensive for years about the future of VBA. VBA was and is used by a significant percentage of users, especially in Excel and Access, to automate functionality.

Back sometime around the mid 2000's, Microsoft started an effort to enhance and augment VBA with other languages. For a few years they dazzled us with amazing demos of automation technology that took VBA into whole new levels of capabilities and performance. But then Google came at the market with Sheets which opened up a different world of browser-based apps. Microsoft realized that this was a huge threat to their dominance and decided to pursue a similar route with Office. And Office Online was born. It was, for the most part, pretty amazing that now we could run Excel and other Office apps from any browser on any platform. However, this path created a huge problem for Microsoft: VBA could not be supported outside of the native application world (Windows and Mac OS) and so the effort to enhance and augment VBA was dropped.

Microsoft addressed this new void by creating automation hooks for Javascript based plugins. The only problem is that these Javascript plugins were much more difficult to create than VBA and were very poorly implemented - at least at first. To this day I don't think they have any where close to the same level of integration with the Office object model that we have with VBA. And accessing the Windows API and other system resources is out of the question because browser-based apps are sandboxed for security reasons.

All-in-all, VBA specifically and automation in general has never really been implemented outside of VBA as we have known it for the past 25 years. Microsoft took VBA out of Office Mac in 2008 and they lost a lot of credibility over that mistake - it was quickly put back in in the next release.

VBA and the VBA community continues to thrive today but many of us are apprehensive about support moving forward. Many years ago Microsoft relegated support for VBA to an independent software group based in India and, as you have probably noticed, has not gotten a lot of love the past so many years. The VBE remains pretty much the same as it was 20 years ago and the language itself has not been revamped or improved. Tools such as user forms and user form controls sport decades old UI motifs and have the same limited functionality as they did when they were first built. It's a shame as there is so much VBA automation out there being used by millions of users everywhere from large corporations to individuals. And it's this automation that enhances Excel and other Office applications to be so much more than they were designed to be. Microsoft knows this but, until they figure out the next serious VBA replacement, they and we are stuck with a decades old development tool.

So, back to your issue. Switch back to "old" Outlook and consider "new" Outlook a diversion/distraction that, hopefully, does not signal the eventual demise of "old" or classic Outlook any time soon. "New" Outlook is, as far as I can tell, not a replacement of "old" Outlook and should never have been presented to us the way it was.

Kevin

Answer 2

Yeah, having the same problem here. Microsoft really dropped the ball on this one.

Everytime I need to use the macros in Excel, I have to switch back to "old" outlook first. Only now it wont let me open "old" Outlook anymore.

Additional note.... a great many add-ins are not available for "new" Outlook.

P.S. Don't fix what's not broken.

Answer 3

OMG. Spent hours today trying to figure out why my Excel macro cannot tell that Outlook is open. Finally found this thread, went back to the "Old Outlook" and it works. Thank you.

Answer 4

I think what is being asked here is the correct syntax when you have both the classic outlook vs the "new"

Set olApp = CreateObject("Outlook.Application")

How do you reference "new" Outlook?

Set olApp = CreateObject("Outlook (new).Application")

Answer 5

You don't as it does not support VBA automation. I believe it's an entirely web or HTML/Javascript based application.

Kevin