Streamlining SAP BPA Bot Execution with Outlook Integration: A Guide

In today's dynamic business landscape, maximizing efficiency through automation is paramount. Integrating various tools and platforms not only streamlines workflows but also optimizes resource utilization. One powerful integration lies between Microsoft Outlook and SAP Build Process Automation (BPA), where incoming emails can trigger actions within SAP's BPA framework. Leveraging this integration effectively can significantly reduce unnecessary bot executions, thereby leading to cost savings.

Enabling Developer Mode

Before delving into the code, it's essential to enable Developer Mode in Outlook:

1. Open Outlook.
2. Navigate to File > Options.
3. In the Outlook Options dialog box, select Customize Ribbon.
4. Check the box next to "Developer" in the right column.
5. Click OK to confirm the changes.

Enabling Macro Settings

To ensure smooth functioning of macros, follow these steps:

1. Go to File > Options > Trust Center.
2. Click on Trust Center Settings.
3. Within the Trust Center dialog box, select Macro Settings.
4. Choose "Enable all macros" or "Enable all macros (not recommended; potentially dangerous code can run)" based on organizational security policies.
5. Click OK to save the changes.

Opening the Visual Basic Editor

Press Alt + F11 to open the Visual Basic for Applications (VBA) editor within Outlook.

Creating a Module

In the VBA editor, navigate to Insert > Module to create a new module for writing VBA code.

Adding References

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

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

Now, let's proceed with writing the VBA code to automate SAP Build Process from Outlook.

Sub CheckEmailSubject(Item As Outlook.MailItem)
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:

• Variable Declarations: Declares variables to store the necessary information for the API call, such as the URL, access token, request data, and response.
• Create XMLHTTP Object: Creates a new instance of the XMLHTTP object, which allows sending HTTP requests.
• 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. 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.
• Prepare Data for the Second Request: Prepares the data payload for the second request to the API endpoint.
• 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.
• 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.
• Clean Up: Sets the XMLHTTP object to Nothing to release system resources.

1. 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:

• 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.
• 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.
• 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.
• Extract the Access Token Value: Uses the Mid function to extract the substring containing the access token from the response string.
• Trim Leading and Trailing Whitespace: Removes any leading or trailing whitespace from the extracted token.
• 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:

1. Close the VBA editor.
2. Send an email with the subject containing "Process Invoice" to your Outlook inbox.
3. Press Ctrl + G to view the Immediate window in the VBA editor.
4. Monitor the SAP Build Process Automation from Outlook as the code triggers.

Running the Script from Rules

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

1. In Outlook, navigate to File > Manage Rules & Alerts.
2. Click on New Rule.
3. Select "Apply rule on messages I receive" and click Next.
4. Choose conditions as per your requirement (e.g., Subject contains "Process Invoice") and click Next.
5. Check "Run a script" as the action to perform.
6. Choose the VBA script you wrote.
7. Follow the subsequent steps to finalize the rule.

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:

1. Close Outlook.
2. Open the Registry Editor by pressing Windows key + R, typing "regedit," and pressing Enter.
3. Navigate to HKEY_CURRENT_USER\Software\Policies\Microsoft\Office<version>\Outlook\Security.
4. Replace <version> with your Outlook version (e.g., 16.0 for Outlook 2016).
5. If a DWORD value named "EnableUnsafeClientMailRules" exists, set its value to 1. If not, create a new DWORD value and set it to 1.
6. 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

Follow these steps:

Open Outlook:

1. Launch Microsoft Outlook on your computer.

Access Rules and Alerts:

1. Navigate to the "File" menu in Outlook.
2. Choose "Manage Rules & Alerts" from the dropdown menu. This will open the Rules and Alerts dialog box.

Create a New Rule:

1. 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:

1. In the Rules Wizard, under "Start from a blank rule," select "Apply rule on messages I receive."
2. Click "Next" to proceed.

Define Conditions (Optional):

1. 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.
2. If you wish to apply the script to all incoming emails, you can skip this step by leaving all conditions unchecked.
3. Click "Next" to continue.

Specify Action for the Rule:

1. Check the box next to "run a script" under "What do you want to do with the message?"
2. In the Step 2 box, click on "a script."
3. Choose the VBA script you wrote for automating the SAP Build Process from the list of available scripts.
4. Click "Next" to proceed.

Set Exceptions (Optional):

1. If there are specific conditions under which you don't want the script to execute, specify them here. Otherwise, you can skip this step.
2. Click "Next" to continue.

Name and Finalize the Rule:

1. Give your rule a descriptive name that helps you identify its purpose, such as "SAP Build Process Automation."
2. Review the summary of your rule settings to ensure they match your requirements.
3. Click "Finish" to create the rule.
4. Apply and Activate the Rule:
5. 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.

By seamlessly integrating Outlook with SAP BPA, you can efficiently manage tasks and enhance productivity. Happy automating!