I have just been setting up some new workflow generated emails in CRM 2011. This was largely an exercise in frustration as the rich text editor is pretty flaky. Getting your fonts and spacing correct is very hit and miss.

Happily, this area is getting an overhaul in CRM 2015:

Microsoft Dynamics CRM 2015 Release Preview Guide

The new Email editor provides marketers with the ability to select from pre-defined templates or create an Email from scratch using an interactive drag and drop build process or an advanced editor for the CSS & HTML experts.

Anyway, after moving on to testing, we noticed that whilst most of the emails were being picked up by the email router, a couple of the messages weren’t and were just sat at ‘Pending Send’.

The obvious difference between the stuck and non-stuck emails was that the former were created in a two stage process, first they were created with a ‘Draft’ status and at some point later they had their status changed to ‘Pending Send’.

The latter were created with the ‘Send Email’ workflow activity which creates and sends them in one action.

A mockup of both types is show here:

emailBlog1

We obtained the email router query via a sql trace, a slightly tidied version is:

[sourcecode language=”sql”]
select
top 5 "email0".Subject as "subject"
, "email0".Description as "description"
, "email0".PriorityCode as "prioritycode"
, "email0".ActivityId as "activityid"
, "email0".ModifiedOn as "modifiedon"
, "email0".StateCode as "statecode"
, "email0".StatusCode as "statuscode"
, "email0".DeliveryAttempts as "deliveryattempts"
from
Email as "email0" (NOLOCK) join ActivityParty as "activityparty1" (NOLOCK)
on ("email0".ActivityId = "activityparty1".ActivityId
and ("activityparty1".ParticipationTypeMask = @ParticipationTypeMask0
and ("activityparty1".PartyId in (@PartyId0
, @PartyId1
, @PartyId2
, @PartyId3
, @PartyId4))))
where
(("email0".StateCode = @StateCode0
and ("email0".StatusCode != @StatusCode0 or "email0".StatusCode is null)
and "email0".DirectionCode = @DirectionCode0
and ("email0".DeliveryAttempts = @DeliveryAttempts0))) order by
"email0".ActualEnd asc
[/sourcecode]

The problem for us was the ‘Delivery Attempts’ filter. The router will never try and pick up an email if the Delivery Attempts field is null.

So, a simple change to the ‘Create’ workflow activity to set this to 0 initially fixed our problem:

emailBlog2