Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ PHP, Perl, MySQL and Postgres _ Run Powershell Commands From VBA

Posted by: David92595 Jul 14 2017, 05:11 PM

This is the closest forum I could find for running powershell commands. Please let me know if this post is better placed elsewhere.

I need to send an email which I am doing via powershell (below). Part of the coding is pulling the active users email from AD.
My current issue is how to call from within in Microsoft Access. I'm found ways to run this if I wanted to save the script as a .PS or .tst file. But I'm looking to keep it all within access.
**This code works in Powershell**

$searcher = [adsisearcher]"(samaccountname=$env:USERNAME)"
$FromEmailAddress = $searcher.FindOne().Properties.mail
$body = "Test 1"

send-mailmessage -from $FromEmailAddress -to "" -subject "Testing" -body $body -smtpServer

Any idea's?

Thank You!

Posted by: David92595 Jul 14 2017, 05:46 PM

I've worked out the string. Still now sure how to call the powershell command and insert my variable correctly.

Public Function SendMailwShell()
Dim Email As String
Dim retval

Email = "" & "$searcher = [adsisearcher]" & """" & "(samaccountname=$env:USERNAME)" & """" & "; $FromEmailAddress = $searcher.FindOne().Properties.mail; $body = """ & "Testing_1""" & "; send-mailmessage -from $FromEmailAddress -to """ & """" & " -subject " & """" & "Testing""" & " -body $body -smtpServer smtp"
Debug.Print Email

retval = Shell("powershell email")

MsgBox retval

End Function

Posted by: cheekybuddha Jul 15 2017, 05:56 AM


retval = Shell("powershell " & Email)