Kudos to Maria Barnes and her code to utilize Microsoft Graph.

Here is her open source project on github.com: VBA-MicrosoftGraph/README.md at master · mbarnesatbbs/VBA-MicrosoftGraph · GitHub

This allowed me (along with her instructions and some help from CoPilot) to set up a new shared mailbox on my Microsoft 365 business account to be able to send client emails.

The problem:

I was using the CDO approach which had worked well, but is being deprecated (someday). The client had moved their server to the cloud and the scheduled task I was using to send email from a queue via CDO was no longer working. The CDO was what the problem was and would require server admin access to correct that I did not have.

Here is more info from DevHut on the CDO approach:

VBA – Using CDO Mail To Send E-mails | DEVelopers HUT

Enter Maria’s technique of using the latest approach of utilizing the modern Microsoft Graph API (this is not a reference to “Graphs”, but a type of unified API or Application Programming Interface). This allows you to have different ways to authenticate with Microsoft 365 in order to utilize services for a Microsoft 365 tenant (their language for your business account, coming from the language for Software as a Service or SAAS for someone renting usage of the service).

Overall here is what I needed to do:

Download Maria’s database with all the code.

Then I had to decide whether to require a user who would be present at runtime and use their authentication or create app credentials for my application to be able to send emails.

For me, since my app is unattended and meant to send emails from a queue, I could not have a user present at runtime and so needed to create app authentication credentials so I could send email.

DISADVANTAGE: This authentication method uses a client ID (basically a username for your app) and an App Secret (the password in user terminology) in order to access the service. These credentials cannot be given permanent lifetime priveleges, and must have a timeout set for their usage, capping at two years (at the moment anyway). This means that my app will run for two years before I will need to update the app security on my Microsoft 365 account with a new App Secret and then I will need to update the settings in my app so it can continue to work.

I wanted to be able to send these emails using the display name “App Emailer” with app being the name of the app I’m using.

DISADVANTAGE: Although you can setup the system to allow you to send from any account, you cannot simply specify any account or email sender name at runtime. You have to create a shared email account (this is associated with a licensed email account, but is free to create and you can create as many as you’d like). So to send from a specific email and/or with a specific name shown, you HAVE to create a shared email box attached to an existing licensed account. Thankfully you can do this for free, but is not something easily done at runtime (and I’m not entirely sure you could automate it at runtime even if you wanted to) . So you need to create an account and name to use. Alternatively, you could just send it from an existing licensed account, but it will use that account’s email and specified name and you can’t change it.

But all that said, I was able to create a shared email account with the email address and name I wanted to use.

The approach uses standard HTML calls to Microsoft servers in order to do the actual email message creation and sending which is what I needed.

The beauty here though is that the Microsoft Graph API allows you to do a lot more than just send emails. In all, this approach was a big win for me and I am grateful to Maria for doing the hard work of creating a nice little framework I was able to port into my application.

Thanks Maria!