Send email notification of new added/changed items once every month

I would start by using a Site Workflow that runs monthly using a schedule.

Then, in the workflow, query your list using filters for the specific category and modified date (newly created items will have the same date/time for created and modified fields). 

You're probably going to need to run a Calculate Date action first to calculate the current date -1 month so you can use that in your filter. 

Once you have your filter figured out, you'll want to set the fields to be returned in that Query List action.

There are two ways to do this:

1. You can query all of the fields for each item, store each field into a collection variable, use a For loop to loop through one of the collections, and then use a Collection action inside the loop for each additional field.

2. Query only the ID. Then use a For loop to loop through the IDs and, inside the loop, run another query that filters based off the item ID where you then grab each field in individual string variables.

It's up to you which process you want to use. However, if you're only querying one field value, like document name, then you don't need to bother with the additional query or multiple collection actions.

Once you've figured out the process you want to use, you'll grab each result's value(s) and concatenate them using a Build String action. 

After the loop, you'll take the string variable you've built inside the loop and reference it inside a Send Notification action.

Hopefully that gets you started.

Thank you very much for your reply!

I've figured out the filter for my list and can see that the correct list items are picked from my list.

I did a query for just the ID but it seems like I'm doing something wrong in the loop action.

When I send out the notification, (just using the doc name) only one document name is displayed. Based on my filter it should be 2. At least thats the amount that got stored in the collection variable based on my list query.

I'm not quiet sure what you mean with:

---

@chaddavis wrote:

It's up to you which process you want to use. However, if you're only querying one field value, like document name, then you don't need to bother with the additional query or multiple collection actions.

Even when I just use the doc name, isn't it necessary to do another query/collection action if I want to get every single doc name to use in my notification?

In the Build string action I referenced to the field value variable (DocName) which I've set inside the loop for the query action. 

I can share some screenshots if needed. Tried to explain everything as best as possible.

Most likely, if you're only getting one document name, you're either storing the queried values into a Single Line of Text variable instead of a Collection variable, OR you're not concatenating the string values correctly.

Option 1: Query Everything Once - Use Collection Operations

(Arguably more efficient)

Query all fields you wish to return. In my case, it's ID, Name, and Year.

Take notice, I'm using Collection variables, not string variables.

(I generally prefix my variable names with their type, so 'col' means 'collection'.)

In the For Each, you pick one of the Collection variables to loop through.

I've used ID. You need to store each value pulled from the Collection variable into another variable.

I've created String (single line of text) variables for each of my Collection variables.

Here you'll see I use 'strID'. 

Notice, I've also included an 'index' variable to hold the loop iteration number. This is a Number.

In the first Collection action, I need to Get the Name value that correlates with the current ID value.

To do that, I use the same 'index' variable from the For Each.

I store the Name value into the 'strName' variable.

Repeat the last step for the Year value in the 'colYear' Collection variable.

The last action in the loop is to concatenate the values you've pulled in this iteration of the loop.

I've used a Build String action and then I setup the layout I want.

In my case, I want "<ID>: <Name> - <Year>".

Now, this might be a little confusing:

I need to store this new concatenated string into a variable so, I've created 'strMessage'.

However, I also need to concatenate the previous value from 'strMessage' back into 'strMessage' with the new values on each iteration.

So, I've included 'strMessage' in the Text field on a new line.

Each time the For Each loops, it will add the newest pulled values to the top of the overall string.

Lastly, I add my Send Notification action outside the loop and drop the 'strMessage' variable into the body of the email.

Here's the end result in my email message:

I used Name, but since this is a list, the Name field doesn't have useful data.

However, you can see the format still worked. "<ID>: <Name> - Year"

(If you wanted 1 at the top and 9 at the bottom, simply place the 'strMessage' variable first in the Build String action.)

Option 2: Query Only IDs - Use Additional Query by ID Inside Loop

(Easier to build/maintain)

Here, you'll see I'm only querying the ID field. I'll get everything else inside the loop.

The For Each loop is identical, except I no longer need the 'index' variable.

Now, in the second Query List, I add in a filter to only grab the item matching the current ID.

In the bottom section, I'm grabbing the Name and Year fields that I want. I already have ID.

Notice, now I only need to use String variables for the Name and Year fields in the query.

There will only be one result for each so, there's no need for a Collection variable.

The Build String action will be identical to before:

As will my Send Notification action:

And the end result will be identical too:

**Note**

I should add that if you only need one field value, like Document Name, then you can trim this down even further. 

In that case, your initial Query List would only need to grab that one field.

Then, in your loop, you would just use that field's Collection variable.

You wouldn't need to do another Query List because you'll already have the field you need.

Just add the Build String action to the loop, build your Send Notification action, and you'll be good to go.

It's working as intended now! Thank you so much again!

I did something wrong while concatenating the string values.

Now its showing up like this.

Something that came into my head after receiving the correct email for the first time is, that it would be great to have the document name, link directly to the specific document. Basically a hyperlink for each document pulled from my list.

I've played around with this a bit and tried to get it to work. What I've found out is, that the "Encoded Absolute URL" field provides me with the specific document links for each document being pulled in the query list action.

The one thing I struggle to figure out right now, is how to replace the hyperlink text with the document name (if that makes sense) so I don't have that ugly URL send out to users by the WF.

I've tried to insert a "link" reference in my notification where I played around with the Text to display and the actual hyperlink behind it. But then all document names got referenced to the same URL. 

Now you're getting into the really fun stuff: Making the data work for you!

So, since we're building the string that will go into the email, you're correct. You cannot use the Insert Reference in the Notification action because the string is built before that point. You have to add the URL link into the Build String action.

To do that, you need to add in a little HTML code.

Step 1: Get the URL Value

In my following example, I'm working with a list so, I actually have to build out the entire URL in pieces. Since you're working with documents you should be able to use that Encoded Absolute URL variable.

This means you'll also need to modify your Query List action to be something like this:

Don't forget to create a new string variable to hold the value.

You'll add the 'strURL' to the Build String action in the 'href' value instead of all the other stuff I add.

Step 2: Build String Action

Modify your Build String action:

Here's the easier to read code:

{WorkflowVariable:strID}: <a href="{Common:WebUrl}/Lists/TestData/DispForm.aspx?ID={WorkflowVariable:strID}">{WorkflowVariable:strCategory} - {WorkflowVariable:strYear}</a><br>
{WorkflowVariable:strMessage}

Since I'm querying a list and have to build the URL manually, I'll walk through each piece.

Yours will be easier. You'll just add the 'strURL' variable.

The important change is right here:

<a href="{Common:WebUrl}/Lists/TestData/DispForm.aspx?ID={WorkflowVariable:strID}">
    {WorkflowVariable:strCategory} - {WorkflowVariable:strYear}
</a>
<br>

This is what adds the link to the Category - Year text in my example.

(I changed my example to use the Category field instead of Name because it's a list.)

<a href=""> is used to add a link. 

You then add the text that will appear as the link.

Lastely you close the link tag using </a>.

So, in my example, my actual link is:

{Common:WebUrl}/Lists/TestData/DispForm.aspx?ID={WorkflowVariable:strID}

Again, you will probably only need to use 'strURL'.

In my example, I use the Common Web URL feature, followed by the hardcoded value '/Lists/' and the list name 'TestData'.

Then, because it's a list, I have to choose which form I'm going to use to display the item.

Here, I've chosen the Display form, which is represented by '/DispForm.aspx'. 

Lastly, I have to give it the querystring value of ID to tell the form which item to open.

By adding '?ID=', that gives the URL the querystring property, and then I follow it by using my 'strID' workflow variable to specify which item should be opened.

The last thing to note about the code is the '<br>'.

This element means break. It will create a line break so that each item is on its own line.

Step 3: Send Notification Action

Next, confirm that your email notification is using Rich Text. HTML will not be displayed correctly if you use Plain Text.

Step 4: Test

Here's my end result:

And just so you can see it, here's the Plain Text example if you forget Step 3.

Please let me know if you have any questions.

Thank you so much again!

It was really easy to follow your instructions and I've got everything to work like I want to. 

I've picked up a lot of useful information from all this and also learned a lot of new things.

Thank you for taking your time and going into detail this much in your responses, really helped me out a lot!

Thank you Chad for the detailed explanations.

But where is the filter function the select only the new or changed documents?

Because, my Mail contains everey time all documents not only the new or changed ones.

@bmb, you're completely correct. I didn't include the Query List filter details. 

For your specific situation, there are a couple possible solutions. First, we need to know how often an email should be sent to everyone. Do you want an email for each individual occurrence of a change? What if someone makes a change every 5 minutes? That would be a lot of emails.

At my organization, we have a slightly similar situation where all employees are required to receive an email notification when a new item is entered into a specific list. However, we decided to not go with a workflow solution. We liked the Alert Me option. Unfortunately, as you mentioned, it doesn't work with a distribution list. So, we created a system account with an Exchange mailbox. We set an Alert Me for that account on the list, and then within the Exchange mailbox, we setup a rule to automatically forward that email onto the All Staff distribution list. That may be a good solution for you.

Of course, that wasn't your original question so, I'll show you how to do it in a workflow as well. Again, though, we'll need to know the frequency at which you'd like to send out these emails, because that will be key in the filter. For this example, I'm going to use the OP's filter criteria of one month:

---

@mike_m wrote:
The email notification should only include added/changed documents from the previous month. Nothing older.

---

And as I stated in my reply, you'll want to use the Modified Date field with a Calculate Date action:

---

@chaddavis wrote:
I would start by using a Site Workflow that runs monthly using a schedule.

Then, in the workflow, query your list using filters for the specific category and modified date (newly created items will have the same date/time for created and modified fields).

You're probably going to need to run a Calculate Date action first to calculate the current date -1 month so you can use that in your filter.

---

You can use the same workflow example from above. Simply add a Calculate Date action to the beginning.

In the Calculate Date action, set the Date field to Current Date and select the decrement interval.

For this example, I've chosen to decrement by one month.

In the Query List action, expand the Filter section and set the filter to use the Modified column.

Select 'is greater than or equal to' and then set the variable to the one used in the Calculate Date action.

Please let me know if you have any additional questions.

If you're going to use SharePoint Alerts, there are a couple options.

If you decide to go the same route we did, by using a system account to forward the alert onto all staff, then you would simply need to log into that account's mailbox and disable the forwarding rule when you don't want it to run and renable it when you do. OR you can just remove the alert for that account on the SharePoint site and then add it back when you're ready.

NOTE: Keep in mind that creating and deleting alerts also sends an email so, if your rule is set to forward all emails, all staff will receive those as well. I would suggest customizing the rule to only forward specific emails.

Alternatively, you can use a little PowerShell to disable all alerts for the site and then re-enable them when you're ready.

I would suggest doing your own research on this or at the very least testing it thoroughly; however, a quick Google search gave me the following script:

DISCLAIMER: I have not tested this myself!

if ((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -eq $null)
{
    Write-Host "Plugging SharePoint modules"
    Add-PSSnapin Microsoft.SharePoint.PowerShell
}
 
$siteUrl = "https://spsite/child/site/"
$spweb = Get-SPWeb $siteUrl
 
foreach($alert in $spweb.Alerts) {
 
# Uncomment the line below to Enable SPAlert
#   $alert.Status = "On"
 
# Uncomment line below to Disable SPAlert
#   $alert.Status = "Off"
 
    $alert.Update()
 
# Uncomment line below for information 
#Write-Host $alert.Title $alert.Status $alert.User
}