Most of it was quite easy. For example, it wasn't difficult to figure out that I'd need a teamsite containing two lists, one for the newsletter content and another for the subscribers.
But I ran into a bit of a barrier when I tried to load the subscribers' addresses into the "To" field of the Workflow email, as these were people outside the Business without access to our Intranet. As usual when I have a tricky problem, I Googled for a solution, but that got me nowhere, so I braced myself to figure out an answer for myself.
Subscribers list
I set this one up first as I knew I'd need to call it as a lookup in the Newsletter Content list.It doesn't need to be complicated, but I included other information to help identify the subscriber's company and who in our organisation was their contact or sponsor, so the columns of this list looked like this:
- Title - with the default value of "Subscriber" (Single line of text)
- Company - so we know who they work for (Single line of text)
- Email - obviously (Single line of text)
- Position - so we know their job title (Single line of text)
- Underwriter - name of their sponsor/contact in our company (Single line of text)
- UWteam - name of the team the subscriber deals with (Choice)
- Owner - admin responsible for taking care of this subscriber's profile (Choice)
I could have made the Underwriter field a Name field, but we had no plans to include this group of people on any communications, so I kept it simple. The Team and Owner fields are choices to minimise user error.
NewsletterFooter list
My first thought was to hard code the email footer text into the Workflow email, but then I decided to include it in a separate (third) list so that if any of the info changed, the administrators of the list could edit the text. The columns were simply:- Title - with a default value of "Edit details" (Single line of text)
- Address - just what it says (Single line of text)
- Phone - Phone numbers, fax and website URL (Single line of text)
Newsletter content list
How many fields you put in this list depends entirely on what kind of newsletter you want to send out. I kept our one simple, with just a few columns:- Title - used for the newsletter headline (Single line of text)
- Body - the main text goes here (Multiple lines of text)
- Link01 - to hold an embedded link (Hyperlink or Picture)
- Link02 - to hold an embedded link (Hyperlink or Picture)
- Link03 - to hold an embedded link (Hyperlink or Picture)
- Address - for the footer (Lookup)
- Phone - for the footer (Lookup)
- Disclaimer - for the footer (Multiple lines of text)
- Recipients - the subscribers (Lookup)
So far so good.
The Workflow email
I figured I could use a Workflow Email function to mail out the newsletter. Shouldn't be difficult, right?So using HTML and embedded CSS styles, I put together an email that would gather up the content from the list and compile it into a simple rich format email. As this was going to subscribers outside our company, I knew any imagery or attachments would have to be stored on an external server. None of this is difficult and is amply documented anywhere else.
The snag came when I wanted to load the subscribers' email addresses in the "To" field of the email.
Trying to use a WorkFlow lookup on the "To" field resulted in not very many options. Certainly no Recipients field. I figured that this was because the Worklow Lookup was only accomodating Single line of text fields. I had to find another way. The trouble was, after an hour or two of Googling, I was no nearer to finding out how to get the subscriber email addresses into the "To" field.
After sleeping on it, I wondered if I could use a variable to dump the text of the subscribers' emails in. So I set up a variable by clicking the Variables button.
I gave the variable a name, "var_recip", and set its Type to string.
Next I had to populate the variable. You can do that by finding Build a dynamic string on the Actions dropdown.
Then move this new action to sit above the Email action. Next click on the text workflow variable, and select the Variable: var_recip option. The click on dynamic string to bring up the String Builder.
In the String Builder, click on Add Lookup, then find Recipients in the drop down.
Click OK. Now click on Variable and select Variable: var_recip.
And that was pretty much it. You can test it by adding just your own email in the Newsletter's Recipients column. Maybe it was just too obvious for anyone to include in a blog ...
How to put the subscribers' emails in the BCC
The other thing was that I didn't really want to have the entire subscription list revealed in the "To" field of the email. It would be better to somehow include them in the BCC. Except that a Workflow doesn't have a BCC ... okay, that's not quite true. It does have a BCC, but you just can't see it.This one I was able to solve via Google. I found this anonymous post that explains how to hack the code and turn the CC field into a BCC field. [http://blog.summitcloud.com/2010/03/how-to-bcc-in-sharepoint-workflow-email/]
So, in SharePoint Designer, first add your var_recip variable to the CC field in the email, by clicking on the lookup icon next to the CC field (circled in red in the screengrab). Then select Workflow Lookup from the list or options. Click Add to bring up the Define Workflow Lookup dialogue box. Change Source to Workflow Data, and select Variable: var_recip in the Field window.
Click OK. Click OK again. And click OK again. Finally, click Finish to close the Workflow Designer window.
Now find the .xoml file in SharePoint Designer's left-hand navigation bar and right-click on it to reveal the drop-down menu. Select Notepad under the Open With option.
Now search the Notepad document for "BCC", then make the "BCC" text "CC", and the "CC" text "BCC".
Save the changes.
That's it. If it's worked, your CC field should now be blank.
Trigger the Workflow Email from the list back in the teamsite. My email looked like this ...
Hope that helps someone.
