• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Tachytelic.net

  • Get in Touch
  • About Me

VBScript

How to trigger a Power Automate Flow with a HTTP Request

March 12, 2020 by Paulie Leave a Comment

Being able to trigger a flow in Power Automate with a simple HTTP request opens the door to so many possibilities. I love it! With some imagination you can integrate anything with Power Automate.

If you want an in-depth explanation of how to call Flow via HTTP take a look at this blog post on the Power Automate blog.

This post provide examples of some of the different ways that the trigger “When a HTTP request is received” can be executed:

  • PowerShell
  • curl on Windows
  • curl on Linux or Unix
  • vbscript
  • vba
  • jquery

The same flow will be executed with different tools or languages and each of them will submit this JSON:

{
	"MessageSubject": "Testing HTTP",
	"MessageBody": "Flow execution has been triggered"
}

The flow only has two steps, it receives the JSON payload and then sends me a message on Teams from the Flow bot:

Image of Flow in Microsoft Power Automate which will be triggered by the event "When a HTTP request is received"
Image of Flow Bot sending a Teams message using a flow Microsoft Power Automate

Invoke a Flow with PowerShell

It’s super easy to invoke a flow with PowerShell. Example:

$flowURI = "https://prod-118.westeurope.logic.azure.com:443/workflows/..."
$messageSubject = "Testing HTTP"
$messageBody = "Execution test from Powershell"
$params = @{"messageSubject"="$messageSubject";"messageBody"="$messageBody"}
Invoke-WebRequest -Uri $flowURI -Method POST -ContentType "application/json" -Body ($params|ConvertTo-Json)

Invoke a HTTP Flow Trigger with curl on Windows Command Line

Because of the way Windows command line interprets double quotes, they need to be escaped. So if you want to include the JSON data on the command line it gets ugly:

curl -H "Content-Type: application/json" -d "{\"messageSubject\": \"Test\",\"messageBody\": \"Executing Flow from curl in Windows Command Line\"}" "https://prod-118.westeurope.logic.azure.com:443/workflows/..."

It’s much easier to work with if you put the contents of the JSON in a file and use the following syntax:

curl -H "Content-Type: application/json" -d @c:\temp\data.json "https://prod-118.westeurope.logic.azure.com:443/workflows/..."

Invoke a HTTP Flow Trigger with curl on Linux or Unix

Things are a bit easier in Linux because there is no need to escape double quotes. So you can simply execute a flow like this:

curl -H "Content-Type: application/json" -d '{ "MessageSubject": "Testing HTTP", "MessageBody": "Flow execution has been triggered from Linux" }' 'https://prod-118.westeurope.logic.azure.com:443/workflows/...'

Invoke a HTTP Flow using VBScript

You can use this code to invoke a HTTP flow in a VBScript:

Option Explicit

Dim FlowURI, JSON, objHTTP, httpCode
FlowURI = "https://prod-118.westeurope.logic.azure.com:443/workflows/..."
JSON = Quote("{ `MessageSubject`: `Testing HTTP`, `MessageBody`: `Flow executed from VBScript` }")

set objHttp = wscript.Createobject("Msxml2.ServerXMLHTTP")
objHTTP.Open "POST",FlowURI,false
objHTTP.setRequestHeader "Content-Type", "application/json; charset=UTF-8"
objHTTP.setRequestHeader "CharSet", "charset=UTF-8"
objHTTP.setRequestHeader "Accept", "application/json"
objHTTP.setRequestHeader "Content-Length", Len(JSON)
objHTTP.send JSON
httpCode = objHTTP.Status
Set objHTTP = nothing

Function Quote(stringToQuote)
	'Small Function to replace backticks with Double Quotes
	Quote=Replace(stringToQuote, "`", chr(34))
End Function

Invoke a HTTP flow using VBA

Almost the same as the VBScript above, you can easily execute a flow using VBA from any of the Microsoft Office Suite:

Option Explicit
Sub ExecuteFlow()

    Dim FlowURI As String, JSON As String, objHTTP As Object, httpCode As String
    FlowURI = "https://prod-118.westeurope.logic.azure.com:443/workflows/..."
    JSON = Quote("{ `MessageSubject`: `Testing HTTP`, `MessageBody`: `Flow executed from VBA` }")
    
    Set objHTTP = CreateObject("Msxml2.ServerXMLHTTP")
    objHTTP.Open "POST", FlowURI, False
    objHTTP.setRequestHeader "Content-Type", "application/json; charset=UTF-8"
    objHTTP.setRequestHeader "CharSet", "charset=UTF-8"
    objHTTP.setRequestHeader "Accept", "application/json"
    objHTTP.setRequestHeader "Content-Length", Len(JSON)
    objHTTP.send JSON
    httpCode = objHTTP.Status
    Set objHTTP = Nothing

End Sub

Function Quote(stringToQuote)
    'Small Function to replace backticks with Double Quotes
    Quote = Replace(stringToQuote, "`", Chr(34))
End Function

Invoke a HTTP flow with jQuery

This is an example of how to execute the same flow with jQuery. I’ve also included the required HTML. Full source code on this page, send me a message!

<!DOCTYPE html>
<head>
<title>Execute Flow</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<script>
flowURI = "https://prod-118.westeurope.logic.azure.com:443/workflows/..."
$(document).ready(function(){
    $("button").click(function(){
        flowData = {
           MessageSubject: "Testing HTTP",
           MessageBody: $("#teamsMessage").val()
        }
        $.ajax(
            {
                url: flowURI,
                data: JSON.stringify(flowData),
                processData: false,
                contentType: "application/json",
                dataType: "json",
                type: 'POST',
                complete: function(xhr, textStatus) 
                {
                    if (xhr.status == '202')
                    {
                        $("#flowStatus").append("Flow Executed");  
                        console.log(xhr.status);                                                  
                    } else 
                    {
                        $("#flowStatus").append("Flow Execution Failed");                                                                                
                        console.log(xhr.status);
                    }  
                }
            });
    });
});
</script>
</head>
<body>
    <p>
        <label>Message</label>
        <input type = "text" id ="teamsMessage" value="Send a message on teams to Paulie" />
    </p>
    <p>
        <button>Invoke Flow</button>
    </p>
<p id="flowStatus"></p>
</body>
</html>

I will keep adding more examples as I create them, but if you have one, feel free to share and I will add it to the post.

Now secure your HTTP Request Trigger with the instructions here.

Filed Under: Power Platform Tagged With: Power Automate, Powershell, VBA, VBScript

Add item to VBScript Array

September 3, 2019 by Paulie 5 Comments

If you are using VBScript for scripting or in a classic ASP page adding items to an array is a difficult and slow process.

The arraylist class from .net is accessible via com and it provides a number of advantages over the native VBScript functionality:

  • It requires less code
  • It is faster
  • It makes it simple to sort arrays
  • Adding and removing item is easy
  • No need to re-dimension arrays

Example Code:

Set players = CreateObject("System.Collections.ArrayList")
players.Add "Novak Djokovic"
players.Add "Rafael Nadal"
players.Add "Roger Federer"
players.Add "Dominic Thiem"
players.Sort
WScript.Echo Replace("There are {0} players in the draw", "{0}", players.Count)

You can easily loop through each items in the array like this:

For Each player In players
	WScript.Echo player
Next 

or like this:

For i = 0 To players.Count - 1
	If i < players.Count - 1 Then
		WScript.Echo players(i)
	Else
		WScript.echo Replace("The last player is {0}", "{0}", players(i))
	End if
Next 

I used this technique on a script I wrote for a customer around 10 years ago. The script still worked fine but it was slow as it was working through a lot of data. By changing the script to use the Arraylist class I was able to reduce the amount of code and increased the performance by around 50%.

It’s also easy to remove items from a VBScript array created this way:

players.Remove "Dominic Thiem"

You can also use the arraylist class to store objects instead of simple values. So taking the above example slightly further you could do something like this:

Class TennisPlayer
	Public Rank
	Public Name
	Public Points
End Class

Set players = CreateObject("System.Collections.ArrayList")

Set player = New TennisPlayer
player.Rank = "1"
player.Name = "Novak Djokovic"
player.Points = "11685"
players.Add player

Set player = New TennisPlayer
player.Rank = "2"
player.Name = "Rafael Nadal"
player.Points = "7945"
players.Add player

Set player = New TennisPlayer
player.Rank = "3"
player.Name = "Roger Federer"
player.Points = "6950"
players.Add player

WScript.Echo Replace("There are {0} players in the draw", "{0}", players.Count)

For Each P in players
	WScript.Echo StrFormat("Player {0}, is ranked {1}, and has {2} ATP points", Array(p.Name, p.Rank, p.Points))
Next 

Function StrFormat(FS, Args())
    Dim i
    StrFormat=FS
    For i = 0 To UBound(Args)
        StrFormat = Replace(StrFormat, "{" & i & "}", args(i))
    Next
End Function

Which produces the following output:

Image showing output of a VBScript script which has used the .net arraylist class to store objects

If you are wondering about the StrFormat function, it is just something I use to make formatting strings easier and you can read about that function here.

I have also written up this same information and method to add items to an array in PowerShell.

Filed Under: Technical Posts Tagged With: VBScript

VBScript lpad and rpad functions

May 23, 2019 by Paulie 1 Comment

VBScript does not include any functions to left pad or right pad a string, so these two functions will enable you to do just that.

Function LPad(StringToPad, Length, CharacterToPad)
  Dim x : x = 0
  If Length > Len(StringToPad) Then x = Length - len(StringToPad)
  LPad = String(x, CharacterToPad) & StringToPad
End Function

Function RPad(StringToPad, Length, CharacterToPad)
  Dim x : x = 0
  If Length > Len(StringToPad) Then x = Length - len(StringToPad)
  RPad = StringToPad & String(x, CharacterToPad)
End Function

Add a Leading Zero

Left pads the number four to a length of two with a zero:
LPad("4", 2, "0")

Add a Trailing Zero

Right pads the number four to a length of two with a zero:
RPad("4", 2, "0")

Left Pad with spaces

Left pads a string with spaces to a length of ten:
LPad("Hello ", 10, " ")

Right Pad with spaces

Right pads a string with spaces to a length of ten:
RPad("hello", 10, " ")

It is a shame there isn’t a native way to pad with VBScript. But these small functions make it simple. I’ve also written up how to do padding with Powershell, which makes things much easier.

Filed Under: Scripts & Utilities Tagged With: VBScript

String.Format in VBScript or VBA

March 10, 2019 by Paulie Leave a Comment

Because I often switch from writing C# back to VBScript or VBA. The lack of a string.format() method in both languages gets is annoying. So I wrote a small function which performs string substitutions in the similar way. It does not have all the formatting capability of the C# method.

String.Format Function for VBScript

Here is the function and example usage:

Option Explicit
Dim strTest : strTest = "Hello {0}, the weather today is {1} and {2}." & vbCrLf & "Have a {3} Day {0}!"
WScript.Echo StrFormat(strTest, Array("Paulie", "Cloudy", "Very Windy", "Great"))

Function StrFormat(FS, Args())
    Dim i
    StrFormat=FS
    For i = 0 To UBound(Args)
        StrFormat = Replace(StrFormat, "{" & i & "}", args(i))
    Next
End Function

This will produce output like this:

Image showing output of a string formatting function in VBScript

Of course in Classic ASP you would substitute wscript.echo for response.write, but the function would work just the same.

String.Format Function for VBA

You can use almost exactly the same function for VBA. Insert this function into a module:

Function StrFormat(FS As String, ParamArray Args()) As String
    Dim i As Integer
    StrFormat = FS
    For i = 0 To UBound(Args)
        StrFormat = Replace(StrFormat, "{" & i & "}", Args(i))
    Next
End Function

Then use VBA Code to call it like this:

Image showing output for string.format function for Excel / VBA

The great thing about adding this as a User Defined function in Excel that you can use it directly in a cell formula like this:

=strFormat("Hello {0}, I hope you have a {1} day. {2} the weather is {3}", "Paulie", "Great", "Shame", "bad")

And you will get this output:

Image showing use of a user defined function in Excel to replicate the functionality of the string.format method from C# in VBA

Hope this helps to make your code a bit easier to read. If it did, or if you can see a way to improve the code, Please let me know in the comments.

Filed Under: How To Tagged With: VBA, VBScript

Use CDO to send email from VBScript or VBA through Office 365

December 16, 2018 by Paulie 18 Comments

Sending email via VBScript or VBA using CDO is easy to do, but the correct configuration to relay through Office 365 is confusing to say the least and it took me me a while to find the correct settings.

I knew from configuring other devices and software that the preferred way to setup SMTP to relay to Office 365 was to use TLS on port 587. The problem is that officially CDO does not support TLS, but unofficially it does. So I tried in vain to develop some code that would send email via smtp.office365.com using TLS and always came up with the following error:

The server rejected the sender address. The server response was: 530 5.7.57 SMTP; Client was not authenticated to send anonymous mail during MAIL FROM

So clearly the TLS support in CDO is not sufficient to be able to work with the Office 365 SMTP Server. The trick is not to use TLS at all, but to use SSL instead on port 25 instead, which seems to work fine:

VBScript to Send Email via Office 365 (smtp.office365.com)

This is just quick sample code to get you on the right path:

Dim objMessage, objConfig, Fields
Set objMessage = CreateObject("CDO.Message") 
Set objConfig = CreateObject("CDO.Configuration")
Set Fields = objConfig.Fields
With Fields
  .Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 
  .Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.office365.com"
  .Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
  .Item ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
  .Item ("http://schemas.microsoft.com/cdo/configuration/sendusername") = "[email protected]"
  .Item ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "Office365Password"
  '.Item("http://schemas.microsoft.com/cdo/configuration/sendtls") = True
  .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
  .Update
End With
Set objMessage.Configuration = objConfig

With objMessage
  .Subject = "Test Message"
  .From = "[email protected]"
  .To = "[email protected]"
  .HTMLBody = "Test Mesage"
end With
objMessage.Send

VBA to send email via Office 365 (smtp.office365.com)

The code for VBA is almost the same as VBScript and I tested it using Excel 2016 without any problems at all. But first you need to add a reference to your project:

  1. In the Office VBA Code Editor go to Tools, and then References:
    Image showing how to add references to an Office VBA Project
  2. In the References dialog box add a reference to the “Microsoft CDO for Windows 2000 Library”:
    Image showing how to add the Microsoft CDO Library to the Office VBA Environment
  3. Use the following sample code….
Sub Office365_Email_Test()
    Dim objMessage, objConfig, fields
    Set objMessage = New CDO.Message
    Set objConfig = New CDO.Configuration
    Set fields = objConfig.fields
    With fields
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.office365.com"
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "[email protected]"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "Office365Password"
        '.Item("http://schemas.microsoft.com/cdo/configuration/sendtls") = True
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
        .Update
    End With
    Set objMessage.Configuration = objConfig
    
    With objMessage
        .Subject = "Test Message"
        .From = "[email protected]"
        .To = "[email protected]"
        .HTMLBody = "Test Message"
    End With
    objMessage.Send
End Sub

A few things to note:

  • The account that you use must have at least an Exchange Online license.
  • You will only be able to send from addresses that account has send as permission for or an alias of that account.
  • It would probably be a good idea to use an account dedicated for sending SMTP because the password is being stored in clear text.
  • Your Firewall or your ISPs Firewall may block outbound port 25.
  • I’ve left the code for TLS in-place above but commented out, in case anyone else wants to have a play with it.

I hope this helps, I spent ages trying to relay through Office 365 over TLS with VBA, but SSL works just fine.

 

Filed Under: How To, Office 365, Scripts & Utilities Tagged With: Office 365, VBA, VBScript

  • Go to page 1
  • Go to page 2
  • Go to Next Page »

Primary Sidebar

Link to my LinkedIn Profile

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 107 other subscribers.

Go to mobile version