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

Tachytelic.net

  • Get in Touch
  • About Me

VBA

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

Emailing from Turbosoft TTwin 4 Terminal Emulator with VBA Script

November 12, 2019 by Paulie Leave a Comment

I’ve got a lot of customers with legacy systems, by chance most of them use Century TinyTerm. One, who uses TTwin 4 by Turbosoft asked if it would be possible to initiate an email from the contents of the emulation screen. Take this screen for example:

Image of typical text based legacy system using TTWin 4 to connect.

I investigated and thought it should be possible. TTwin uses VBA engine provided by WinWrap . It is easy to capture the contents of a fixed portion of the screen, but the email address could be anywhere in the display. A slight complication.

So I write a script which could be activated by a mouse shortcut (which is quite a neat feature of this emulator). The script scans the screen for email addresses and then initiates the default mail client on the PC to send an email. Here is the code:

Option Explicit

Sub Main
	Dim screenContents() As String, emailAddress As String
	ScreenContents = getScreenContents(24, 80)
	emailAddress = findEmailOnScreen()
	If emailAddress <> "notFound" Then
		Shell ("rundll32.exe url.dll,FileProtocolHandler mailto:" & emailAddress, 1)
	End If
End Sub

'Function to search for an email address on screen
Function findEmailOnScreen() As String
	Dim ScreenContents() As String, Screenline() As String
	Dim regExp As New RegExp, i As Integer, j As Integer
	ScreenContents = getScreenContents(24,80)

	With regExp
		.Global = True
		.Multiline = True
		.IgnoreCase = True
		.Pattern = "((?:[A-Z0-9_%+-]+\.?)+)@((?:[A-Z0-9-]+\.)+[A-Z]{2,4})$"
	End With

	For i = 0 To UBound(ScreenContents)
		Screenline() = Split(ScreenContents(i), " ")
		For j = 0 To UBound(Screenline)
			If(regExp.Test(Screenline(j))) Then
				findEmailOnScreen=Screenline(j)
				Exit Function
			End If
		Next		
	Next

	findEmailOnScreen = "notFound"
End Function

'Function to get the entire contents of the screen as an array
Function getScreenContents(Height As Integer, width As Integer)
	Dim screenLines() As String
	ReDim screenLines(Height)
	Dim lineText As String, count As Integer

	Do
		TTWin.DispReadText  count,0,lineText,width
		screenLines(count) = lineText
    	count=count+1
	Loop Until count = Height
	getScreenContents=screenLines()
End Function

It works like this:

  • Loads each line of the display into an array
  • Splits each line of the array by the space character into another array.
  • Uses regex to see if any element of the array looks like an email address
  • Opens the default mail client if a match is found using rundll32.exe

The script could be improved but it works well and it is a decent start.

Image showing Email being initiated from a VBA Script within Powersoft TTWin 4
Mouse Shortcut of Right-Click Shift initiates the script and a new email is created.

TinyTerm has the same scripting engine as TTWin 4, I tried and failed to create the same function with TinyTerm. I am sure it is possible, but the programmers documentation wasn’t clear to me.

This is the first time I have used TTWin, it seems like a really good product, things I like about it:

  • Multiple sessions support is good with thumbnails in a dedicated panel.
  • Scripting engine is easy to get to grips with.
  • Hot spots, keyboard and mouse events are easy to setup to provide quick automation.
  • The macro recorder is easy to use and works well.

The programmers documentation could be better, more example code would help a lot.

Do you have a text based legacy system? If so I offer lots of ways to enhance and modernise, just get in touch to discuss your requirements.

Filed Under: Scripts & Utilities Tagged With: VBA

VBA – Add Items to Array

September 4, 2019 by Paulie 8 Comments

VBA does not allow you to append items to an array. The standard method to work around this is to re-dimension to array to the required size and then add the required items.

Take the following code as an example:

Dim VbaArrayNames() As String
ReDim Preserve VbaArrayNames(0)
VbaArrayNames(0) = "Peter"
ReDim Preserve VbaArrayNames(1)
VbaArrayNames(1) = "Paul"
ReDim Preserve VbaArrayNames(2)
VbaArrayNames(2) = "Andy"

There are a number of problems with this method:

  • Performance – The array is copied into a new array each time it is re-dimensioned.
  • Readability – Lots of additional code we can do without.
  • Sorting – There is no easy way to sort the data.
  • Removal – No Simple way to remove an element from the array.

There is another method which is much easier, faster and has more functionality, and that is to use the ArrayList class from .net framework:

Set DotNetArray = CreateObject("System.Collections.ArrayList")
DotNetArray.Add "Peter"
DotNetArray.Add "Paul"
DotNetArray.Add "Andy"
DotNetArray.Sort
Dim Name As Variant
For Each Name In DotNetArray
    Debug.Print Name
Next
Image showing how to add items to a VBA array.
It’s easy to add or remove items and sorting is a one liner.

As you can see the array can easily be sorted without having to resort to bubble sort methods and the code is much cleaner.

If you are working with large arrays there will certainly be a performance boost, and even if you aren’t, the code will be much cleaner.

Some of the other useful methods:

Remove an element from the array

DotNetArray.Remove "Andy"

Check if the array contains a particular value using the contains method

This will return true or false depending on the result.

If DotNetArray.contains("Paul") Then
    Debug.Print "Paul is in the array"
Else
    Debug.Print "Paul is not in the array"
End If

Reverse the Array with the reverse method

Just like sorting, reversing the array can be done in an instant:

DotNetArray.Reverse

Adding items to a VBA array is just much easier when using the arraylist class. You can also do exactly the same in VBScript and Powershell, which I have covered in the linked posts.

Filed Under: How To Tagged With: VBA

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 35 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
Buy me a coffee

Subscribe to Blog via Email

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

Join 259 other subscribers.

Go to mobile version