Opening Program outside of RD Web Access Via VBA

Question

Hi All
We have recently moved our Microsoft Access Database to a RD Web Access - RemoteApp and Desktop Connection, all VBA / Macros all work as it should apart from one issue.

Prior to moving to RD Web we had VBA code that when a button is pressed by the user it opens op MS Outlook fills out an Email template automatically and all the user has to to is click send. 

Since moving to RD Web using VBA to open Outlook no longer works as security does not allow outlook to open within the RD Web. I am hoping there is a way that I can force MS Outlook to open outside of the Remote window and complete the task instead and create an Email. anyone have any ideas how we can do this as I am not afraid to so it this is well above my knowledge of VBA?


Unfortunately the security levels do not allow outlook to be opened within the remote access so trying to force it open outside of this remote window,

Below is my current code that worked on the standalone system, but since moving to the RD Web I currently get a run-time error of '429', ActiveX Component cant create object.



[CODE] Dim oWo  As Object 'Outlook.Application
    Dim oDoc1 As Object 'Word.Document
    'Set oWd = New Word.Application
    Set myOlApp = CreateObject("Outlook.Application")
    Set myitem = myOlApp.CreateItemFromTemplate(CurrentProject.Path & "\TemplateFiles\PasswordEmail.oft")

    myitem.Subject = UCase("HR RECORDS - " & "[" & [Forms]![FRM_TBLALL_FullDetails].[Form]![LblHRRef])

    myitem.To = UCase([Forms]![FRM_TBLALL_FullDetails]![SFRM_TBLALL_STAFFDetails]![SSFRM_TBLALL_INTERVDetails].[Form]![CmbStaffNo].Column(0))

    myitem.display

    myitem.body = "Hello" & Chr(13) & Chr(10) & "You will receive an encrypted report and all relevant files required." & Chr(13) & Chr(10) & _

    "Your encryption password is:" & [Forms]![FRM_TBLALL_FullDetails]![SFRM_TBLALL_STAFFDetails]![SSFRM_TBLALL_INTERVDetails].[Form]![Password]


End Function

[/CODE]

Thank you in Advance!
Stu




               

Answer 1

Hi St.

Since you mentioned that the security levels in RD Web Access do not allow Outlook to be opened within the remote session, you can try using a workaround to create the email outside of the RD Web session. One way to achieve this is by using a PowerShell script to create the email and send it through Outlook on the local machine where the RD Web Access is being accessed from.

Here's how you can modify your VBA code to call a PowerShell script that handles the email creation and sending:

1. Create a PowerShell script (e.g., SendEmail.ps1) with the following content:

param(
    [string]$to,
    [string]$subject,
    [string]$body
)

$ol = New-Object -ComObject Outlook.Application
$mail = $ol.CreateItem(0)

$mail.To = $to
$mail.Subject = $subject
$mail.Body = $body
$mail.Display()

$ol.Quit()

2. Modify your VBA code to call the PowerShell script:

Sub SendEmail()
    Dim oShell As Object
    Dim scriptPath As String
    Dim toAddress As String
    Dim emailSubject As String
    Dim emailBody As String

    ' Set the email details here
    toAddress = UCase([Forms]![FRM_TBLALL_FullDetails]![SFRM_TBLALL_STAFFDetails]![SSFRM_TBLALL_INTERVDetails].[Form]![CmbStaffNo].Column(0))
    emailSubject = UCase("HR RECORDS - " & "[" & [Forms]![FRM_TBLALL_FullDetails].[Form]![LblHRRef])
    emailBody = "Hello" & Chr(13) & Chr(10) & "You will receive an encrypted report and all relevant files required." & Chr(13) & Chr(10) & _
                "Your encryption password is: " & [Forms]![FRM_TBLALL_FullDetails]![SFRM_TBLALL_STAFFDetails]![SSFRM_TBLALL_INTERVDetails].[Form]![Password]

    ' Define the path to the PowerShell script
    scriptPath = "C:\path\to\SendEmail.ps1" ' Replace with the actual path to the PowerShell script

    ' Create the Shell object
    Set oShell = CreateObject("WScript.Shell")

    ' Execute the PowerShell script passing parameters
    oShell.Run "powershell.exe -ExecutionPolicy Bypass -File " & scriptPath & " -to """ & toAddress & """ -subject """ & emailSubject & """ -body """ & emailBody & """", 1, True

    ' Release the Shell object
    Set oShell = Nothing
End Sub

Replace "C:\path\to\SendEmail.ps1" in the VBA code with the actual path to your SendEmail.ps1 PowerShell script.

This modified code will execute the PowerShell script outside of the RD Web session, and the PowerShell script will handle the creation and display of the email using Outlook on the local machine. This way, you can overcome the limitations of RD Web Access security and still send emails using Outlook as desired.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".