Silly Me! Lesson Learned!

OK, I'm going to leave this page here, but lesson learned.

After all this debugging it was NOT a technical issue it was a process issue.

Well, the debugging allowed me to work out that it was NOT a technical issue, so go back to the very basics.

Draw out the steps on paper.

My process was doing this:

If the status is X then
- Email the contact.
- Set the status to Reset
- Set the field to say the email has been sent (to prevent further loops).

Then there was another workflow

If the status is Reset
Delete the link to ObjectA (clear the field with the lookup to ObjectA) - not the best thing to be doing, but it was a bit of a hack workaround for another issue rather than changing some very complex custom code that no one knows much about (yes, we do have it on the roadmap to rebuild this code).

So, what was happening?

~~Because you can't guarantee the order of execution, it seems that the link to ObjectA was deleting before the email was sent.~~

When the workflow was first built, there was a 1 hour gap between the email sending and the status being set to reset, so in quickly reconfiguring the workflow I did not twig that the order of execution problem would exist.

EDIT AGAIN! OMG How stupid am I, it is NOT an issue of order of execution at all! (I worked this out after another 2 hours of trying to split the workflows into smaller chunks and still it was not working, but as soon as I edited the record it was working. DOH! Stupid Stupid Stupid! The one thing I did not mention in this post yesterday, (and would have helped me if I had mentioned it, because I would have spotted the issue right away) is that the process that triggers this email is done on a public Site. (ALL of my other emails are triggered from internal processes).

Now, if I had built this public site myself, I would have probably guessed this a lot earlier.

I googled visualforce email fields external sites user and found this post http://salesforce.stackexchange.com/questions/76078/email-alert-fails-with-sites-user-insufficient-access-or-readonly-user-does-no

Of course it's a permissions issue! Of course! And as soon as I knew that it took 15mins to fix and test then another 15 mins to delete all my testing artefacts.

Job done!

So lessons learned:

Is it a technical issue - eg is it something that Salesforce just won't do or you've hit limits - there should be something if you google - I found nothing.
Is it a data issue - I tried re-creating the data from scratch to recreate this with a new set of data - still had the issue.
Is it a permissions issue - if only I had tried that first!
Are there red herrings that lead you down a rabbit hole? - I think the custom fields vs standard fields are a red herring, but I can't be sure. Watch out for those rabbit holes! Set some time limits.
Find another way to do it - For me, trying it from the other object was the turning point - because other emails work from that object, so it could not be technical.
Simplify the workflow - I had a complex workflow so I had to try to simplify it - does it work if I just send the email on every update of the record? This is how I eventually found the problem.

I don't know if I will find an answer to this, but I am having a mysterious problem with visualforce emails not populating with data when being sent via a workflow, but they do send fine when sent via the Send Test and Verify Merge Fields button.

Other Reports

http://salesforce.stackexchange.com/questions/22568/approval-process-step-field-update-doesnt-appear-in-same-steps-visualforce-ema (It's not an approval).
https://developer.salesforce.com/forums/ForumsMain?id=906F00000008lpRIAQ (no answer, and their data model is simpler, so I think it is not to do with the data model)
https://success.salesforce.com/issues_view?id=a1p30000000SdBfAAK (It's not the same because this is saying the workaround is to use relatedTo, which I am).
https://success.salesforce.com/answers?id=90630000000h2ywAAA (does not actually answer the question).

Data Model

Notes

This email HAS WORKED! I can NOT reproduce the situation where it did work, but it definitely has!
VF Email is being sent from Custom Object B and contains fields from all related objects.
Emails sent from Custom Object A work with no problem.
Values from Custom Object C show fine.

The VF Email Template header is

<messaging:emailTemplate subject="My Email Subject" recipientType="Contact" relatedToType="ObjectB__c" rendered="true">

Steps

Steps to try to work out what the problem is and fix it

Step	Notes	Result
Create formula fields for data	rather than {!relatedTo.ObjectA__r.Campaign__r.Account__r.Notes__c} I added the Notes field to Object B as a formula field so it was {!relatedTo.Notes__c} I did this for 3 fields	No fields displayed
Send email immediately	The workflow was running after an hour	No fields displayed
Simplified the HTML Email		No fields displayed
Added the IDs for all the objects into the email body		ALL IDs Displayed!!
Added the Names	If the Standard Fields of IDs display, do the standard fields of Names display? Because the field that is displaying from ObjectC is the Name field.	All Name fields displayed!!
Added the new fields without any HTML embellishment	Just replaced the name fields that did display with the fields I wanted to display.	No Custom fields displayed!
Wrapped the fields in the output tags	To try to see if there was anything to the non-answer in article 4. above. I also added Created Dates (standard fields) in output tags.	No Custom fields displayed. Standard fields displayed
Changed to plaintextemailbody		Nope, so it's not about HTML
Changed the Contact lookup to be directly from Custom Object B	So instead of {!relatedTo.ObjectA__r.Contact.Notes__c} I had {!relatedTo.Contact__r.Notes__c}	Nope, so it's not about how far away the object is at all.
Send via process builder	Hey, it's worth a shot! But of course, since it just uses the standard email alert, it won't do anything different.	Nope, no difference.
Sent the email from another object	I used a workflow to update ObjectA then send the email from there - it did not work even though there are other emails sending from that object quite fine.	! AAAGHHH!
Dialed back the API version	So, what was the difference between the email that was working and the one that was not? The API Version. The working email was on 32, the non working was on 33	Nope!

So, at this stage we know, it's not about the data model and it's not about HTML visualforce. It's not about the HTML structure and it's not about whether the fields are enclosed in output tags or not. It is only about whether the fields are custom fields or not.

I could test with another object but I need to send from this object.

I could test without visualforce emails but I need to use visualforce emails to have conditional text in this email.

Aha! Found it! See notes at the top! Stupid stupid me!