Email Notifications for new note entry and file upload

[dbeckner]

1. Is it possible to make "Notify current approver" and "Notify requester" automatically checked by default instead of unchecked when adding a new note entry to a request?

2. Is it possible to add an email notification to the approver or requester when a file has been uploaded to a request?

[CharlesW · Accepted solution]

It could be done by making a small change to web/RequestDetails.aspx... Look for the following lines in the file, and add Checked = "True". 

<br />
<asp:CheckBox ID="cbNotifyCurrentApprover" runat="server" Text="<%$Resources:resources, NotifyCurrentApprover%>" Checked = "True"/>
<br />
<asp:CheckBox ID="cbNotifyTarget" runat="server" Text="<%$Resources:resources, NotifyTarget%>" />
<br />
<asp:CheckBox ID="cbNotifyRequester" runat="server" Text="<%$Resources:resources, NotifyRequester%>" Checked="True" />

 A word of caution, however.. There is no guarantee that changes to this file will not be overwritten when applying a patch or update to App Portal.. As such, you may need to apply the same change again after updating..

[CharlesW · Accepted solution]

It could be done by making a small change to web/RequestDetails.aspx... Look for the following lines in the file, and add Checked = "True". 

<br />
<asp:CheckBox ID="cbNotifyCurrentApprover" runat="server" Text="<%$Resources:resources, NotifyCurrentApprover%>" Checked = "True"/>
<br />
<asp:CheckBox ID="cbNotifyTarget" runat="server" Text="<%$Resources:resources, NotifyTarget%>" />
<br />
<asp:CheckBox ID="cbNotifyRequester" runat="server" Text="<%$Resources:resources, NotifyRequester%>" Checked="True" />

 A word of caution, however.. There is no guarantee that changes to this file will not be overwritten when applying a patch or update to App Portal.. As such, you may need to apply the same change again after updating..

[CharlesW]

With regards to the second part of your question, there is no way to send a notification when a file has been added.

[dbeckner]

Thanks @CharlesW 

[jdempsey]

---

@CharlesW wrote:
With regards to the second part of your question, there is no easy way to send a notification when a file has been added.

---

Correction above.  You could create a PowerShell script and set it up as a scheduled task.  Have the script check for new/updated entries in the WD_Uploads table and send an email to the desired recipients.  You will likely need to join to multiple other tables to get the correct email addresses (e.g. WD_PackageRequests, WD_ApprovalProcess, and WD_User).

If you're super-savvy with SQL, you could do all of that as an insert/update trigger on the WD_Uploads table, but I don't generally recommend making those types of changes to the database (do so at your own risk).  The external scheduled task is much safer and would not be at risk of being wiped out by patches/upgrades.

[dbeckner]

@jdempsey Thanks this helps as well.

[dbeckner]

@jdempsey Looking closer at the suggested solution here -- would the underlying email delivery service that App Portal uses to send notification emails be exposable for this? I checked into various web services in the /ws folder. I didnt see anything but may have missed it. If not i'm guessing your solution would require leveraging the SMTP server directly from the powershell script?

[jdempsey · Accepted solution]

There is nothing built in to App Portal for this.  You would just use normal PowerShell commands for sending the email.  Here is some sample code you can adjust as needed...

$Script:ConnectionStringAppPortal = <your connection string here>

##############################################################################
# ExecuteSimpleSQLQuery
##############################################################################
function ExecuteSimpleSQLQuery($szConnect, $szQuery) {
    $result = 0
    $con = New-Object System.Data.SqlClient.SqlConnection
    Try {
        $con.ConnectionString = $szConnect
        $con.Open()
        $command = $con.CreateCommand()
        $command.CommandText = $szQuery
        $result = $command.ExecuteScalar()
    }
    Catch [System.Exception] {
        write-error ('EXCEPTION: ' + $_.Exception)
        $Error.Clear()
    }
    Finally {
        $con.Close()
    }
    return $result
}

##############################################################################
# SendEmail - Sends a notification e-mail to the specified user.
##############################################################################
function SendEmail($ReqName, $EmailAddress) {

    write-host "Sending email notification to $ReqName ($EmailAddress)."
    # Get the name of the SMTP server.
    $SMTPServer = ExecuteSimpleSQLQuery $ConnectionStringAppPortal `
        "SELECT Value FROM WD_AppSettings WHERE KeyName = 'SMTP'"

    # Get the "from" e-mail address
    $FromPre = ExecuteSimpleSQLQuery $ConnectionStringAppPortal `
        "SELECT Value FROM WD_AppSettings WHERE KeyName = 'EmailPrefix'"

    $FromSuf = ExecuteSimpleSQLQuery $ConnectionStringAppPortal `
        "SELECT Value FROM WD_AppSettings WHERE KeyName = 'EmailSuffix'"

    $EmailFrom = ($FromPre + '@' + $FromSuf)

    # Create the e-mail body.
    $EmailBody = @"
<html>
<head>
	<style type="text/css">
		.style5 {
			font-family: Verdana, Arial, Helvetica, sans-serif;
			font-size: 11px;
		}
	</style>
</head>
<body class="style5">
	Dear $ReqName,<br />
	<br />
	Insert your message content here<br />
	<br />
	Thank you,<br />
	<br />
	The App Store Team<br />
</body>
</html>
"@

    Try {
        # Send mail to $EmailAddress

        Send-MailMessage -To $EmailAddress `
            -From $EmailFrom `
            -Subject "App Store File Upload Notification" `
            -Body $EmailBody `
            -SmtpServer $SMTPServer `
            -BodyAsHtml

        write-host "    Notification e-mail sent to $ReqName ($EmailAddress)."
    }
    Catch [System.Exception] {
        write-error ('EXCEPTION when sending notification e-mail: ' + $_.Exception)
        $Error.Clear()
    }

}

[dbeckner]

@jdempsey Thank you! As always your support and suggestions are outstanding.

[jdempsey]

I should mention that the code sample is taken out of context from a larger script, so I think I replaced/removed all the dependencies (e.g. replaced custom logging function calls with write-host/write-error statements), but if it won't run for you, let me know and I can try to fix it.

[dbeckner]

@jdempsey I made some tweaks and got it working in our lab environment. At the customer site im running into issues with the emails not sending. Its due to the from address not matching the address of the logged in user. For the email notifications that App Portal sends out -- in the email tab under admin settings you specify the email prefix and suffix. When i use that value in the script the SMTP relay server is stating that the client does not have "send as" permissions, but the SMTP server allows App Portal to send emails. I am going to be contacting the exchange server admin team to find out what their settings are but im wondering if you know how app portal builds its emails "from" address. Our prefix is "storefront support" but thats not actually the email address that is sending the emails.

[jdempsey]

If I'm not mistaken, the "from" address you see in the code is only the "displayed" From email address that you would see in your email client.  If you look at the SMTP header for the email, you may see a completely different address (think phishing emails or marketing emails going through a 3rd party mass mailing system), which is based on the account context under which the process is running.

In your email settings within App Portal, you should see an Email Prefix and an Email suffix that make up the "displayed" From email address, but the actual sender will be determined by authentication with the SMTP server.  Are you specifying credentials in the email settings to authenticate with your SMTP server (SMTP User and SMTP Password referenced here)?  If so, then you would need to make sure your email script is running under the context of that alternate account.  If not, then the SMTP server may be allowing either anonymous authentication or may be using Windows integrated authentication.  In the case of anonymous, I wouldn't expect the SMTP relay to have any issues sending mail from any address, so it is likely using Windows integrated authentication.  In that case, then it would be using the App Portal service account credentials, so you would want to make sure your email script is running under the context of the App Portal service account.

Bottom line is that there are many things that could be going on here and your email team will be the best people to help you figure it out.  From an App Portal perspective, we are either using the credentials you provide in the email settings or we are attempting unauthenticated (or integrated auth) access if no credentials are specified.

[dbeckner]

@jdempsey Thanks. All the points you made are the same ones I discussed with my teammates, but wanted to confirm it from an AP standpoint. Appreciate your assistance. This is going to allow us to create a repeatable process for many other types of notifications.