Solved Power automate with excel button

[goncalo]

Hey everyone,first off im not sure if im allowed to post this here since it has more to do with power automate than excel itself but since i really dont know where to ask this i thought i should try it here.


Im developing an interface in excel and now i thought of adding a feature to it involving power automate.
Basically when the user clicks on a specific button in the excel sheet it runs a flow from power automate that i created (this flow basically just sends an e-mail to my work e-mail).
I found a piece of code to try and make this work but on the code itself they ask for a flow URL and i have no idea where to find it,been looking everywhere and have not been able to find it.
Tried going to the run history of the flow and copying the link for each of the runs and trying them out but none of them work correctly,the code runs well but it does not end up sending the e-mail.

Below is the code i found

Sub SendMessageToTeams()
    Dim url As String
    Dim httpRequest As Object
    
    ' Set the URL for the Power Automate flow
    url = "FLOW-URL"


    
    ' Create an HTTP request object
    Set httpRequest = CreateObject("MSXML2.XMLHTTP")
    
    ' Send an HTTP POST request to the Power Automate flow URL
    httpRequest.Open "POST", url, False
    httpRequest.send
    
    ' Handle the response if needed
    ' ...
End Sub

If someone could help me out i would really appreciate it!

Once again i apologize if this is not the correct forum to ask this,thank you for reading!

 

[Minty]

Does this link help at all?

How to run Power Automate from Excel with Office Scripts or VBA

Find out how to run a Power Automate flow from Excel using Office Scripts and VBA. Amazing automation technique to save time.

exceloffthegrid.com

I suspect it could be a URL formatting issue, I've found Power Automate very frustrating, from a syntax perspective, sometimes.

 

[goncalo]

uhh i don't think so because im not able to use that trigger,its a premium feature and you need to have a license to use it..
The flow i made was a instant cloud flow mixed with a send e-mail(v2) trigger.
I tested the flow and it works fine ,it sends the e-mail and everything.
I was looking online and found this link : https://tomriha.com/how-to-get-a-link-to-power-automate-flow-running-on-a-specific-sp-item/
i tried to figure out the link manually and when i insert it in the code i get an access denied error and it points to the httpRequest.send Line
I have no clue if the link i put there is causing the issue or if its something else.

Also yeah power automate is kinda frustrating,i think the UI is not very user friendly its a bit of a mess,i never know where i should click

 

[Minty]

I'll be brutally honest here, and say that most of the useful things in Power Automate are what they call Premium Connectors or Objects that attract a fee for use.

Generally speaking, they aren't actually costly to use, £0.00001p per use but you do need an account to make them work.

 

[theDBguy]

Hi. Not sure if I could help but do you need to log in to PA to run the flow manually? If so, maybe that is what's missing in your VBA.

 

[goncalo]

Well i ended up settling for something a bit more easier to do
i thought i should post the code i used incase there is someone else that is looking for the same thing as i was


I added the Microsoft outlook object library to VBA and then wrote this code:

Sub SendEmail()

    Dim OutlookApp As Object
    Dim OutlookMail As Object
    Dim MailSubject As String
    Dim MailBody As String
    Dim RecipientEmail As String
 
    MailSubject = "1412"
    MailBody = "123"
    RecipientEmail = "youremailhere@gmail.com"

    Set OutlookApp = CreateObject("Outlook.Application")
    Set OutlookMail = OutlookApp.CreateItem(0)
 
    With OutlookMail
        .Subject = MailSubject
        .Body = MailBody
        .To = RecipientEmail
       
        .send
    End With

    Set OutlookMail = Nothing
    Set OutlookApp = Nothing
 
    MsgBox "Email sent succesfully!", vbInformation

End Sub

I ended up assigning this code to a button that then will prompt a userform,this userform will then ask if the user requires help,incase they click on the button with the caption "Yes" it will then run this code.
Much easier than going through that whole thing with the flows and what not

Thank you for trying to help Minty and theDBGuy, i appreciate it!

 

[Minty]

Much easier, If I had read what you were trying to achieve more closely, I might have suggested that.
Glad you solved it!

 

[goncalo]

Much easier, If I had read what you were trying to achieve more closely, I might have suggested that.
Glad you solved it!

i probably did not explain myself well so that was probably an issue on my part aswell hahaha,thanks!