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.
For a complete video tutorial on this topic, please visit https://599cd.com/SendEmailwithPowerShell
No comments:
Post a Comment