Power Automate with VBA with Ynte Jan Kuindersma

Faced with the impending divorce between Outlook and VBA, Microsoft Access developers are searching for reliable alternatives to maintain email and calendar functionality in their applications.

In his Access DevCon Vienna presentation, Ynte Jan Kuindersma delivered a compelling introduction to Microsoft Power Automate as a potential solution. As a certified Power Platform expert, Ynte Jan demonstrated how Power Automate can not only replace Outlook automation but also extend Access applications by connecting to various web services through easy-to-build workflows (flows) rather than complex API integrations.

Whether you're looking for ways to continue sending emails from your Access applications or interested in expanding your application's capabilities to integrate with modern cloud services, this presentation offered practical techniques that most Access developers can implement with minimal coding.

What is Power Automate?

Background

• Part of the Microsoft Power Platform alongside Power BI and Power Apps
• Originally released in 2016 under the name "Flow"
• Included in most Microsoft 365 business and enterprise accounts
• Premium features (like HTTP request triggers) require a paid license (~€15/month)

Core Components

• Triggers - Events that start a flow (scheduled, automatic, or manual/instant)
• Actions - Tasks that the flow performs (sending emails, creating calendar items, etc.)
• Controls - Logic within flows (conditions, loops, variables, etc.)

Key Integration Points for Access Developers

HTTP Request Trigger

• Creates a web endpoint that can be called from VBA
• Allows passing data from Access to Power Automate
• Can return data back to the calling application
• Requires premium license

Authentication Options

• Built-in Microsoft account authentication for standard flows
• Public endpoint with password verification as a simpler alternative
• IP address filtering for additional security

JSON Handling

• Power Automate has built-in tools for handling JSON
• Can easily convert between JSON and structured data
• Sample payloads can automatically generate schema definitions

Practical Applications for Access Developers

Email and Calendar

• Send emails from any provider (Outlook, Gmail, etc.)
• Create and manage calendar appointments
• Work with contact lists
• Handles attachments

External Services

• Connect to hundreds of services through pre-built connectors
• SharePoint, OneDrive, Teams, SQL Server
• Third-party services like Adobe Sign, Gmail, and more
• Create custom connectors for proprietary systems

Document Processing

• Fill Word templates with data
• Process Excel files
• Handle file operations on SharePoint/OneDrive
• Manage document approvals

Implementation Example

From VBA to Power Automate

• Create a dictionary object with parameters in VBA
• Convert to JSON
• Use MSXML2.ServerXMLHTTP to post to Power Automate endpoint
• Process the response from Power Automate (if applicable)

Security Considerations

• Public endpoints require additional security measures
• Password verification within the flow
• IP address filtering
• Better to implement proper authentication (though more complex)

Conclusion

Ynte Jan Kuindersma's presentation demonstrated that Power Automate provides a viable path forward for Access developers facing the end of VBA support in Outlook. While there are authentication challenges to address for secure implementations, the platform offers a remarkably flexible way to connect Access applications to modern services without complex API programming. The combination of Power Automate's visual workflow builder and VBA's familiar programming environment creates a bridge between traditional Access development and cloud-based services.

Access to Recordings

This presentation was part of Access DevCon Vienna, a paid online conference for Microsoft Access developers. The full recording and other conference materials are exclusively available to registered attendees of the conference.

If you attended Access DevCon Vienna, you should have received (or will soon receive) an email from the conference organizers with a link to the videos.

Consider Attending Future Conferences

The quality and depth of content from Access DevCon Vienna demonstrates the value of professional development conferences focused on Microsoft Access. Consider attending future conferences to:

• Learn advanced techniques directly from expert presenters
• Gain access to exclusive content not available elsewhere
• Ask questions of expert presenters and other attendees
• Stay current with the latest Access development trends and best practices

For information about upcoming Access DevCon Vienna conferences, visit donkarl.com.

Acknowledgements

• Initial draft generated by Claude-3.7-Sonnet