Send Email with PowerShell
By Richard Rost
2 years ago
Send Email Using PowerShell in Microsoft Access
In this Microsoft Access tutorial, I will show you how to send email directly from Access using Windows PowerShell. We will cover setting up PowerShell, creating command strings, handling errors, and sending emails with customizable parameters such as to, subject, and body fields. This is an alternative to the technique I covered in my Send Email with CDO video series (link below).
Members
In the extended cut, we will learn how to set up CC, BCC, fields, HTML formatting, and attachments for sending emails with PowerShell. We will also fail miserably at getting ReplyTo to work. Details in the video.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Links
Recommended Courses
Keywords
TechHelp Access, send email using PowerShell in Access, PowerShell email script, Microsoft Access email automation, sending Access forms via email, Access to PowerShell integration, automate Access tasks with PowerShell, PowerShell send email SMTP, email without Outlook in Access, Access tutorial PowerShell email
Intro In this video, I will show you how to send email from Microsoft Access using Windows PowerShell. We'll talk about what PowerShell is, why you might use it instead of Outlook or CDO, and how to set up a simple form in Access with a button to trigger a PowerShell script. You'll see how to build and manage the command string, work with variables, handle basic error messages, and make sure the email body is formatted correctly, including escaping special characters like double quotes. This video focuses on integrating PowerShell with Access for sending emails.Transcript In today's video, I'm going to show you how to send email from Access using Windows PowerShell. Now, what is PowerShell, you might be asking? If you've ever done any programming with DOS or Windows batch files, PowerShell is basically batch files on steroids. It's a command-line shell and scripting language. It's built on .NET, so it's new and it lets you automate lots of cool stuff. It comes with Windows, so it's already on your system, and most importantly, a lot of things that you can't do or you can't easily do directly in Access or VBA you can run in PowerShell. Then you just say, "Hey, PowerShell, do this for me," and PowerShell will give you a return code letting you know if it was successful or failed.
Now, we've got a new section on my website called PowerShell LearningZone, and my good friend and colleague Alex Hadley has set up a bunch of free TechHelp videos here to show you all about PowerShell and how to set it up. If you've got newer versions of Windows, you've already got PowerShell on your machine, but there's a lot of different stuff you can do with it, and so I'll put a link to Alex's PowerShell page down below. You can check that out later.
So, why learn how to do this with PowerShell? Well, it's another option for you to have in your Lego toolkit, right? For years and years, we've been sending email from Access using Outlook, but the new versions of Outlook have problems with that. So, I just recently put together a series of videos to show you how to use CDO--Collaboration Data Objects. This was last week's video series. If you haven't watched these, go watch these first because I'm going to use the database we built in these for today's video, okay?
Now, the problem with CDO is that it's been around for a while too. It's part of the Windows Script Host, and although Microsoft hasn't made any announcements as far as getting rid of Windows Script Host, you never know. They have already announced an end of VB script, and I used to use VB script all the time. So, this is just kind of planning ahead for the future just in case. PowerShell is new. It's based on .NET. It's not going to go away anytime soon, so again, it's just another tool to have in your kit.
Okay, so here I am in the database that we built in the send email with CDO series that I said you should go watch, and in here, we've got a customer form. We can send email. I added a bunch of extra fields in the extended cut for the members. We have a CC, a BCC, a reply-to field. We can add attachments by just picking a file. But we're just going to focus on the "to," the subject, and the body. Now, I'm not going to set up a whole separate module for this like we did in the other video. You can do that if you want to. I'm just going to put a button down here and just send directly from the form to keep things simple for today. I don't want this to be a four-part series. It's real simple.
Alright, so send email. I'm going to resize this button, and we're going to say this is going to be sent with CDO. That's the old button. Copy-paste. Put a new button down below. And we'll say send with PowerShell like that. And you can't use the alt S twice, so this can be alt P. How about that one? Okay. And we'll slide that down. Alright, give this button a name. Let's call it send with PowerShell button. Right-click build event. That'll put us in our code editor. And we're right down here on the bottom.
Now, like I've mentioned in a bunch of different videos, this is going to be simply a matter of copying and pasting the script. If you're a member, if you're a gold member, you can grab it out of my database. It'll be in the code vault. I'll put links down below. Everybody else, you can get to typing or you can use some screen OCR and copy it. But there it is. I just copy-pasted it in.
Alright, we've got some variables declared up top here: a shell object, which is an object we can do stuff with, right? The return code that we get back from PowerShell so we know if we succeeded or failed. The command string is a big long string that we're going to put together to send the command to PowerShell. Think of it like your batch file command. Here's the command string. We'll talk about this more in just a second. It's going to send all the data that we need to PowerShell, send the email, and then get a return.
Here's the shell object created there. Here's where it's run. Shell object.run. Your command string. Zero means hide the window. If you want to see the window, if you want to actually see the little PowerShell command prompt pop up, make that a one. There's other options too. I think like two is minimized, three is met, whatever. And then true is needed here. This is "wait till it's finished." If you set that to false, it's just going to issue the command and then continue running codes. You want to wait for that return code to come back.
Alright, and down here, it's either return code zero which means it's a success. Do the same thing we did in the last video. Otherwise, it's a fail. We don't get more information than that, though. Yeah, there are some ways to get more verbose error messages, but basically, it's a success or a fail. If you can't figure out why it's failing, just run this from the command line in PowerShell and it'll show you what the problem is.
Okay, let's take a look at the command. There's some stuff in here you don't have to worry about like execution policy and all that, but here it is right here. Command. You'll see try and catch in here. That is how it handles the errors. It's going to try running this command, and if it's successful, it exits with a zero. Otherwise, it exits with a one, which means it's a failure. Alright, and this little guy here reads the status of the send mail message command. There's a lot of cool stuff to learn with PowerShell. I'm still just a moderate user of it myself.
But this is where you put all the stuff in here. Here's all the parameters, right? To email. There's my from address. The subject is getting the subject in the form. The body is right there. We have one issue we have to deal with in just a few minutes. We'll talk about how to deal with that body issue in just a minute. The SMTP server that comes from our constants. Remember, we set up that send mail config module, right? And then here's where all your settings are: your SMTP server, the port we're going to talk about in just a second. There's your username, your password. Don't forget, if you're using Gmail, you have to set up the Gmail app password that I talked about in the last video. Alright, and some things like timeout we're not going to use here. But that's where you specify your SMTP server. SMTP: Simple Mail Transfer Protocol.
Alright, now port 587. I've hard-coded that in here instead of relying on it in the settings because with using PowerShell, it usually wants to use 587. Some servers might use 465 or even 25. That's completely up to you. I found that most of the time, whenever I use PowerShell, I've got to use port 587. You may need to change that. I'm using SSL all the time in here as well. Again, I just put that in here instead of using the parameters. If you want to change it to use the constants again, they're your Lego pieces. That's fine. Do whatever you like. Here's where you set up your credentials. Alright, there's your username and your password, and you can see this convert to secure string. It does some security stuff there for you.
Okay, and then when all that's said and done, it'll either exit with a zero or exit with a one and come down here and do your thing. Alright, okay let me try it first without my password in there, and that should generate a fail. Alright, so let me debug compile. Okay, we're good. Let's come out here and customer form. Send email, blah blah blah. Send with PowerShell. Failure. Alright, because it's the wrong password. Let me actually put my password in here now. No, you can't have it and now let's try that again. Send with PowerShell and away it goes. Okay, so it's working.
Now, I mentioned a minute ago there's one issue we have to deal with when it comes to that body text. Alright, if I put in here, "hi there," and then I say something like, "she said," quote, "hello," and then I try to send this. Oh, it actually went out? Let's see if it sent it correctly. Okay, it worked but only accidentally because I have the email set to HTML mail. Okay, so it converted that to a quote, quote like that. Alright, but let me convert this back to plain text. We did this; I converted this to rich text so I could use HTML in the extended cut of the last video series. Now, let's go back to plain text and do it again. Okay, hi there, she said, "no way," whatever. Okay, send with PowerShell and oh it worked again. That's interesting. I'm surprised it actually took it. Usually with something like that with a string, you get an error unless you escape those double quote characters. Okay, I wasn't sure exactly why it still worked though. Usually, I would expect an error and something like this. I didn't test it ahead of time, folks. I just knew we had to do this.
Normally, you have to escape those quotes. Alright, and it's different for every language. With PowerShell, you have to use a backtick. Where is it? Yeah, we have to use a backtick and then a double double quote. Okay, the backtick tells PowerShell that the next character is literal. Okay, the reason why--because I asked GPT--I'm like, why does this work? It says the reason it works in the sense that the command executes but the double quotes are removed is that PowerShell is interpreting the double quotes as part of the command syntax. So, the command still forms a valid PowerShell command. It runs without errors, but the quotes don't make it through as part of the email body. So, it's a weird quirk that it still works, it just removes them.
Okay, so yeah, that confused me for a moment there too. I was definitely expecting an error with that, like a malformed string or something, but it's easy to fix. We'll just come into here. Now, I don't want to change the actual body in the form, so we're going to have new body as a string, and then before we create that we're going to say new body equals replace(body). Now, wherever you find a quote, we're going to replace it with a backtick that's next to the one and under the tilde. Alright, that little guy--not this guy--that guy. Okay, and then put the quote in there, and we have to escape double quotes. Usually, like with JSON, you have to use a slash and then all kinds of different rules for different languages, but that's what you gotta do for PowerShell. Alright, so debug compile and now let's try it again one more time. Rick said, "wow this works," and then send with PowerShell and there it goes. Okay, and it didn't work and I made a classic error in my code. Let's see if you guys can figure it out. I'm leaving this in the video because I do this all the time. Alright, do you see what I did? Do you see it, anybody? Classic classic mistake.
Well, I set a new variable called new body and I'm still using the old body down here, aren't I? So, it's just basically ignoring that command. So, change it to new body, and if you caught that, give yourself three points. Save it, debug compile, let's come back out here. Let's try that again. Close it, open it. Hi there, hello. Ready, go. Okay, now this didn't work either, and I should have tested this ahead of time. I was pretty confident that that backtick wasn't the right character to use for the escaping of the double quotes. I thought it was a slash or a backslash, but I'm not a PowerShell expert, so I just went with what ChatGPT gave me. Let this be a lesson that you don't always listen to what ChatGPT gives you. It was wrong. It's not that character. It is indeed the backslash. So, we're going to convert double double quotes into backslash double double quotes. That'll put a backslash quote in the command string, and that's the right one to use. And yes, I tested this before proceeding.
Alright, one more try. Here we go. Test test, and then in quotes, "hi." Alright, send with PowerShell and let's go check our inbox, and it worked. Okay. Alright, that was a case where I knew ahead of time we were going to have to escape it, but I didn't test it before I just started recording a video. I know, I know, I know. But now it works, and now our code's good and there you go and that's what you need right there.
Alright, we're going to do an extended cut. I'm going to go through the same stuff I covered in the other extended cut for the other video, which is the cc, bcc, reply-to, HTML, and attachments. Don't forget to check out Alex's PowerShell stuff. He's got some cool videos on here. I'll put a link down below. Another scripting language you can do this with is Python. Now I've been learning Python myself for the last couple of months. It's really cool. It's very powerful. It's one of those languages where you can issue one command and it does a whole bunch of stuff. If anyone wants to see how to do this in Python, let me know. I've been thinking about putting together some Python lessons but I'm not sure if there's demand for it. So, we'll see. Post a comment down below if you're interested.
Also, don't forget to check out my Access Email Seminar if you want to learn more about sending emails and bulk emails and building a mail server and all kinds of stuff that you can do in Access with email. Alright, so that's going to do it for your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time.
A special thank you and shout out to our diamond sponsor Juan Soto with Access Experts Software Solutions. They're manufacturing experts specializing in Microsoft Access and SQL Server. Juan is a 13-time Microsoft Access MVP. Check them out at AccessExperts.com.
TOPICS:
Sending email from Access using PowerShell
Introduction to PowerShell
Creating a customer form with email fields
Adding a button for sending email with PowerShell
Copy-pasting PowerShell script into VBA
Declaring and understanding variables in PowerShell script
Creating and running a shell object in VBA
Constructing the command string for PowerShell
Using shell object.run method in VBA
Handling PowerShell command execution policy
Using try-catch for error handling in PowerShell
Setting up email parameters in PowerShell command
Specifying SMTP server and port in PowerShell
Handling email body text in PowerShell
Replacing double quotes in email body for PowerShell
Ensuring secure transmission with SSL in PowerShell
Setting up and using credentials in PowerShell
Handling success and failure return codes in PowerShell
Debugging and testing PowerShell email script
Handling multiple PowerShell command parameters
Overview of sending HTML and plain text emails
Implementing CC, BCC, reply-to, and attachments
Overview of integrating PowerShell with Access forms
Troubleshooting command string issues in PowerShell
Escaping special characters in PowerShell command string
COMMERCIAL:
In today's video, I'll show you how to send email from Access using Windows PowerShell. We'll cover what PowerShell is and why it's superior for automation tasks you can't easily handle in Access or VBA. We'll use a database from a previous tutorial and create a simple email sending form. Copy and paste the script into your module and learn to manage variables and command strings. Handle errors effectively and ensure your emails send correctly. You'll also see how to escape double quotes in email bodies. The full video is available on my YouTube channel and website at the link shown. Live long and prosper, my friends.Quiz Q1. What is Windows PowerShell primarily used for in the context of the video?
A. Creating graphical user interfaces
B. Playing multimedia content
C. Command-line shell and scripting automation
D. Video editing
Q2. Why is PowerShell preferred over CDO for future-proofing email automation?
A. PowerShell is built on .NET and is likely to stay supported
B. CDO does not support sending emails
C. PowerShell is easier to use than CDO
D. CDO is only compatible with older versions of Windows
Q3. In the video, what is the purpose of the shell object in PowerShell?
A. To store email content
B. To execute command strings and interact with the system
C. To create graphical elements
D. To read user input
Q4. Why should you set the return code parameter to true when running a PowerShell command from Access?
A. To make the command run faster
B. To let the script continue running without waiting
C. To ensure the script waits for the PowerShell command to complete
D. To disable error-checking
Q5. What is the common SMTP port mentioned for sending emails using PowerShell?
A. 25
B. 465
C. 587
D. 80
Q6. How can you handle double quotes in the email body when sending emails via PowerShell, according to the final solution in the video?
A. By using single quotes
B. By using a backtick character
C. By using a backslash character
D. By removing all quotes from the string
Q7. According to the video, what should you do if PowerShell commands work differently or unexpectedly?
A. Rewrite the script from scratch
B. Manually modify each command in Access
C. Run the command directly from PowerShell to debug
D. Switch to another programming language
Q8. What was the incorrect advice given by ChatGPT regarding escaping double quotes in PowerShell, as per the final corrected solution in the video?
A. Use a forward slash
B. Use a double backslash
C. Use a backtick
D. Use an exclamation mark
Q9. What did the presenter suggest to do if interested in learning another scripting language for similar tasks like sending emails?
A. Learn Java
B. Learn Python
C. Learn Ruby
D. Learn PHP
Q10. Apart from PowerShell, what other key method was discussed in previous videos for sending emails from Access?
A. Using Microsoft Exchange Server
B. Using Microsoft Outlook
C. Using Windows Messenger Service
D. Using Google Calendar
Answers: 1-C; 2-A; 3-B; 4-C; 5-C; 6-C; 7-C; 8-C; 9-B; 10-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone will demonstrate how you can send email from Microsoft Access using Windows PowerShell. If you are not familiar with PowerShell, it is a command-line shell and scripting language built on the .NET platform. Think of it as an advanced replacement for traditional DOS or Windows batch files. PowerShell is included with Windows, so you already have it on your system. The big advantage here is that PowerShell can automate many tasks that are not easily handled directly in Access or VBA, and it provides a return code to let you know if your command succeeded or failed.
Our website now has a section called PowerShell LearningZone that features a collection of free TechHelp videos by my colleague Alex Hadley. These videos will walk you through learning and setting up PowerShell, so if you want to dig deeper or learn more, you will find a link to Alex's PowerShell page below.
You might wonder why you should use PowerShell to send email from Access. Simply put, it gives you another tool for your technical toolkit. For years, most Access developers have relied on Outlook for sending email, but more recent versions of Outlook present some challenges. Not long ago, I created a video series teaching how to use CDO - Collaboration Data Objects - to send email. If you have not watched that series, I recommend you view it first since we will reuse the database we built in those videos for today's tutorial.
A challenge with CDO is that, while it is part of the Windows Script Host, the future of some related components is uncertain. Microsoft has already retired VBScript, for example. Although there is no official word that CDO or Windows Script Host will be discontinued, it is wise to look ahead. PowerShell, being newer and built on .NET, is not going away and makes a solid addition to your skillset.
In today's example, I will use the customer form from the previous CDO video series. The form allows users to send email. In the extended cut of the previous series, I added fields for CC, BCC, reply-to, and attachments, but for this demonstration, we will stick with the basics: email address, subject, and body. Rather than set up an entirely new module, I will simplify things by adding a button on the form itself to send email using PowerShell.
So, on the form, I create a new button labeled 'Send with PowerShell.' I assign it a name and open the VBA code editor to attach code to the button's click event. For this process, the PowerShell script can simply be copied and pasted into the VBA code. Members can grab the script from the code vault, but others will need to type it in manually or use screen capture tools. Once the script is in place, we are ready to proceed.
In the script, a few variables are declared. These include a shell object for interacting with Windows, a return code to capture the result, and a command string that holds the actual PowerShell command you want to execute. The command string contains all the email data you want to send via PowerShell.
The shell object is created and used to run the command string. I use the 'run' method so that PowerShell operates in the background. If you want to see the PowerShell window as it runs, you can modify this setting. The 'wait for completion' parameter ensures Access waits for PowerShell to finish and return a success or failure code before continuing.
If PowerShell returns zero, that means the command was successful, otherwise it's a failure. PowerShell does not easily provide detailed error messages, but if you want to troubleshoot, you can always run the same command directly in PowerShell to view any fuller error details.
The PowerShell command string itself includes a few extra pieces, such as specifying execution policies, but the important parts are the parameters for the email: the recipient address, sender, subject, body, and the SMTP server settings. I am using port 587 in this demonstration, as that is the most common, but you may need to change this based on your SMTP server requirements. SSL is enabled by default and credentials are provided in the command as needed. If you are using Gmail, remember to set up an app-specific password as explained in previous videos.
A special point to cover is handling the email body. PowerShell requires special care regarding double quotes inside the body text. If you include something like she said, "hello," it may cause issues if not handled correctly because PowerShell uses quotes as part of its syntax. At first, I was not sure whether to escape double quotes using a backtick or a backslash. After some testing, I confirmed that using a backslash is the correct way to escape double quotes within the PowerShell command string. To handle this, I added a bit of VBA code to replace regular double quotes in the email body with escaped versions, ensuring that the email body is transmitted properly.
While testing, I ran into a common coding mistake by introducing a new variable to hold the adjusted body text and forgetting to use that variable in the final command. Once corrected, the script worked as expected.
In the Extended Cut for members, I will also cover how to handle CC, BCC, reply-to fields, send HTML-formatted emails, and attach files, just as I did in the earlier CDO video series.
Do not forget to visit Alex Hadley's section on PowerShell for more related videos. Also, if you are interested in seeing similar solutions using Python, let me know in the comments. I have been exploring Python lately and am considering producing lessons for it if there is enough interest.
If you are interested in learning more about sending emails from Access, including sending bulk emails and setting up your own mail server, check out my Access Email Seminar.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Sending email from Access using PowerShell
Introduction to PowerShell for Access automation
Creating a button to send email with PowerShell
Copying and pasting PowerShell scripts into VBA
Declaring shell object and variables in VBA
Building a command string to run PowerShell
Running PowerShell commands from VBA
Setting command window visibility and wait mode in VBA
Interpreting return codes from PowerShell
Using try-catch in PowerShell for error handling
Configuring email parameters in the PowerShell script
Specifying SMTP server and port in PowerShell
Hard-coding port 587 for PowerShell SMTP
Enabling SSL in PowerShell email scripts
Setting credentials using ConvertTo-SecureString
Debugging incorrect credentials in email script
Replacing double quotes in email body for PowerShell
Escaping special characters in PowerShell command strings
Fixing bugs when replacing text values for the email body
Testing and validating PowerShell email functionality from Access |
