Triggering the SAP BPA bot when an email arrives via Outlook using VBA: A Comprehensive Guide

[ShahIndrajit]

In today's fast-paced business environment, automation is key to increasing productivity and efficiency. Integrating different tools and platforms can streamline workflows and reduce you SAP BPA bot execution significantly. One such integration is between Microsoft Outlook and SAP BPA , where emails trigger actions in SAP's Build Process Automation. If you bot starts with an email arrival then with this solution you can save useless bot executions and that lead to saving money

Enabling Developer Mode

Before we dive into the code, let's first enable Developer Mode in Outlook:

Open Outlook.
Go to File > Options.
In the Outlook Options dialog box, click on Customize Ribbon.
Check the box next to "Developer" in the right column.
Click OK to save the changes.

Enabling Macro Settings

To ensure that macros are enabled, follow these steps:

Go to File > Options > Trust Center.
Click on Trust Center Settings.
In the Trust Center dialog box, select Macro Settings.
Choose "Enable all macros" or "Enable all macros (not recommended; potentially dangerous code can run)" based on your organization's security policies.
Click OK to save the changes.

Opening the Visual Basic Editor

Press Alt + F11 on your keyboard. This shortcut opens the Visual Basic for Applications (VBA) editor within Outlook.

Creating a Module

In the VBA editor, go to Insert > Module. This creates a new module where you can write your VBA code.

Adding References

To use MSXML2.XMLHTTP, we need to add a reference to "Microsoft XML, v6.0" in the VBA editor:

In the VBA editor, go to Tools > References.
Check "Microsoft XML, v6.0" from the list.
Click OK to add the reference.

Now, let's write the VBA code that will automate the SAP Build Process from Outlook.

Sub CheckEmailSubject(Item As Outlook.MailItem)
    ' This subroutine triggers when a new email arrives.
    ' If the subject contains "Process Invoice" it calls the CallAPI subroutine.
    If InStr(1, Item.Subject, "Process Invoice", vbTextCompare) > 0 Then
        Call CallAPI
    End If
End Sub

This subroutine CheckEmailSubject triggers when a new email arrives. If the subject contains "Process Invoice" it calls the CallAPI subroutine.

Sub CallAPI()
    ' Variable declarations
    Dim xmlhttp As Object
    Dim url As String
    Dim data As String
    Dim accessToken As String
    Dim response As String
    
    ' Create a new XMLHTTP object
    Set xmlhttp = CreateObject("MSXML2.XMLHTTP")
    
    ' First request to get access token
    url = "https://[URL]/oauth/token?grant_type=client_credentials"
    xmlhttp.Open "POST", url, False
    xmlhttp.setRequestHeader "Authorization", "Basic clientid:clientsecret"
    xmlhttp.setRequestHeader "Content-Type", "application/json"
    xmlhttp.Send
    
    ' Check if the request was successful
    If xmlhttp.Status = 200 Then
        ' Extract the access token from the response
        Debug.Print xmlhttp.responseText
        accessToken = ExtractAccessToken(xmlhttp.responseText)
        
        ' Print the access token value to the Immediate window
        Debug.Print "Access Token: " & accessToken
    Else
        Debug.Print "Error in getting access token"
        Exit Sub
    End If
    
    ' Prepare PayLoad data for the second request/ API trigger
    data = "{""invocationContext"":""${invocation_context}"",""input"":{""Name"":""ABC""}}"
    
    ' Second request to the API endpoint
    Debug.Print "Bearer " & accessToken
    url = "[API trigger URL]"
    xmlhttp.Open "POST", url, False
    xmlhttp.setRequestHeader "irpa-api-key", "[API KEY]"
    xmlhttp.setRequestHeader "Authorization", "Bearer " & accessToken
    xmlhttp.setRequestHeader "Content-Type", "application/json"
    xmlhttp.Send (data)
    
    ' Check if the second request was successful
    If xmlhttp.Status = 200 Or xmlhttp.Status = 201 Then
        ' Get the response from the second request
        response = xmlhttp.responseText
        
        ' Print the API response to the Immediate window
        Debug.Print "API Response:"
        Debug.Print xmlhttp.responseText
    Else
        Debug.Print "Error in sending request"
        Debug.Print xmlhttp.Status
        Debug.Print xmlhttp.responseText
    End If
    
    ' Clean up
    Set xmlhttp = Nothing
End Sub

Function ExtractAccessToken(response As String) As String
    Dim tokenStart As Long
    Dim tokenEnd As Long
    Dim token As String
    
    ' Find the position of "access_token" in the response
    tokenStart = InStr(response, """access_token"":""") + Len("""access_token"":""")
    
    ' Find the position of the next quotation mark after "access_token"
    tokenEnd = InStr(tokenStart, response, """")
    
    ' Extract the access token value
    token = Mid(response, tokenStart, tokenEnd - tokenStart)
    
    ' Trim leading and trailing whitespace from the token
    token = Trim(token)
  
    ' Return the access token
    ExtractAccessToken = token
End Function

1. Sub CallAPI(): This subroutine is responsible for making API calls to a specified endpoint. Here's what each part does:
   1. Variable Declarations: Declares variables to store the necessary information for the API call, such as the URL, access token, request data, and response.
   2. Create XMLHTTP Object: Creates a new instance of the XMLHTTP object, which allows sending HTTP requests.
   3. First Request to Get Access Token: Sends a POST request to the authentication endpoint to obtain an access token. It sets the request headers for authorization and content type.
   4. Check if the Request was Successful: Checks the HTTP status code of the response. If it's 200 (OK), it extracts the access token from the response and prints it to the Immediate window. If not, it prints an error message and exits the subroutine.
   5. Prepare Data for the Second Request: Prepares the data payload for the second request to the API endpoint.
   6. Second Request to the API Endpoint: Sends a POST request to the API endpoint with the access token and request data. It sets the necessary headers for authorization and content type.
   7. Check if the Second Request was Successful: Checks the HTTP status code of the response. If it's 200 or 201, it prints the API response to the Immediate window. If not, it prints an error message along with the status code and response text.
   8. Clean Up: Sets the XMLHTTP object to Nothing to release system resources.
2. Function ExtractAccessToken(response As String) As String: This function extracts the access token from the response received after the first API request. Here's what each part does:
   1. Variable Declarations: Declares variables to store the starting and ending positions of the access token in the response string, as well as the extracted token itself.
   2. Find the Position of "access_token" in the Response: Uses the InStr function to find the starting position of the access token in the response string.
   3. Find the Position of the Next Quotation Mark After "access_token": Uses the InStr function again to find the ending position of the access token.
   4. Extract the Access Token Value: Uses the Mid function to extract the substring containing the access token from the response string.
   5. Trim Leading and Trailing Whitespace: Removes any leading or trailing whitespace from the extracted token.
   6. Return the Access Token: Returns the extracted access token as the result of the function.

These code blocks work together to make API calls and extract the access token from the response, demonstrating a basic implementation of interacting with APIs using VBA in Outlook.

Testing the Code

Now that we have written the code, let's test it:

Close the VBA editor.
Send an email with the subject containing "Fixed Assets" to your Outlook inbox.
Press Ctrl + G to view the Immediate window in the VBA editor.
The code will trigger, and you can monitor the SAP Build Process Automation from Outlook.

Running the Script from Rules

To run the script automatically when specific emails arrive, you can set up a rule in Outlook:

In Outlook, go to File > Manage Rules & Alerts.
Click on New Rule.
Select "Apply rule on messages I receive" and click Next.
Choose conditions as per your requirement (e.g., Subject contains "Fixed Assets") and click Next.
Check "Run a script" as the action to perform.
In the Step 2 box, click on "a script" and select the VBA script you wrote.
Click Next, specify any exceptions if needed, and click Finish.

If "Run a Script" Option is Not Found

If you don't see the "Run a script" option when creating a rule, it's likely because Outlook's security settings restrict script execution:

Close Outlook.
Open the Registry Editor by pressing Windows key + R, typing "regedit", and pressing Enter.
Navigate to the following path: HKEY_CURRENT_USER\Software\Policies\Microsoft\Office<version>\Outlook\Security
Replace <version> with your Outlook version (e.g., 16.0 for Outlook 2016).
If a DWORD value named "EnableUnsafeClientMailRules" exists, set its value to 1. If not, create a new DWORD value and set it to 1.
Close the Registry Editor and reopen Outlook.
Now, you should be able to see the "Run a script" option when creating rules.

Configuring Script Execution via Rules for Every New Email Arrival

Setting up a rule in Outlook to execute the script automatically for every new email arrival involves a few straightforward steps. Let's walk through the process:

• Open Outlook:
  • Launch Microsoft Outlook on your computer.
• Access Rules and Alerts:
  • Navigate to the "File" menu in Outlook.
  • Choose "Manage Rules & Alerts" from the dropdown menu. This will open the Rules and Alerts dialog box.
• Create a New Rule:
  • In the Rules and Alerts dialog box, click on the "New Rule" button. This initiates the process of creating a new rule.
• Select Condition for the Rule:
  • In the Rules Wizard, under "Start from a blank rule," select "Apply rule on messages I receive."
  • Click "Next" to proceed.
• Define Conditions (Optional):
  • If you want to apply the script to emails meeting specific conditions, such as containing certain keywords or being from specific senders, specify these conditions here.
  • If you wish to apply the script to all incoming emails, you can skip this step by leaving all conditions unchecked.
  • Click "Next" to continue.
• Specify Action for the Rule:
  • Check the box next to "run a script" under "What do you want to do with the message?"
  • In the Step 2 box, click on "a script."
  • Choose the VBA script you wrote for automating the SAP Build Process from the list of available scripts.
  • Click "Next" to proceed.
• Set Exceptions (Optional):
  • If there are specific conditions under which you don't want the script to execute, specify them here.
  • Otherwise, you can skip this step.
  • Click "Next" to continue.
• Name and Finalize the Rule:
  • Give your rule a descriptive name that helps you identify its purpose, such as "SAP Build Process Automation."
  • Review the summary of your rule settings to ensure they match your requirements.
  • Click "Finish" to create the rule.
• Apply and Activate the Rule:
  • Once you've finished creating the rule, click "Apply" and then "OK" in the Rules and Alerts dialog box to apply the changes.

Now, every new email that arrives in your Outlook inbox will trigger the execution of the specified VBA script, triggering the SAP BPA bot according to the conditions you've set. This automation helps streamline your task and ensures that important tasks are promptly addressed without manual intervention and saving bot execution and saving money.

With these simple steps, you can trigger your SAP BPA bot directly from Outlook whenever the relevant email arrives to bot mailbox. Happy automating! Happy reading! Hope it will Help