Tom Crowfoot
asked on
Email with peronalised content
Dear Experts
I am building a reporting tool to sit on the back of our CRM – the tool is designed to email users a list of records they set up that have been badly coded (aka Sins)
I would like to be able to email each user their list of sins – however rather than emailing a report I would like the list of Sins to be in the body of the email. The data comes from a query called sinners and has the following fields in it:
EmailAddress (who the email goes to)
Creator (the name of the recipient)
Person (the name of the record that is badly coded)
Role (more of the record that is badly coded)
Offences (why that record is badly coded)
Each recipient can have multiple ‘Sins’ and so their email address can / is repeated in the query against each 'Sin'.
I have attached an example of the query in excel
Sinners.xlsx
Can anyone help?
I am building a reporting tool to sit on the back of our CRM – the tool is designed to email users a list of records they set up that have been badly coded (aka Sins)
I would like to be able to email each user their list of sins – however rather than emailing a report I would like the list of Sins to be in the body of the email. The data comes from a query called sinners and has the following fields in it:
EmailAddress (who the email goes to)
Creator (the name of the recipient)
Person (the name of the record that is badly coded)
Role (more of the record that is badly coded)
Offences (why that record is badly coded)
Each recipient can have multiple ‘Sins’ and so their email address can / is repeated in the query against each 'Sin'.
I have attached an example of the query in excel
Sinners.xlsx
Can anyone help?
ASKER
HI thanks for this
I am however getting a run time error (3075) - Syntax error (missing operator) in query expression 'Offences qrySinners ORDER BY Creator' - any ideas?
I am however getting a run time error (3075) - Syntax error (missing operator) in query expression 'Offences qrySinners ORDER BY Creator' - any ideas?
Sure. I forgot the from clause. Use this
Select EmailAddress, Creator, Person, Role, Offences from qrySinners ORDER BY Creator, Person
ASKER
Thanks for that, that's cleared the error, alas now nothing happens when I try to run the code - any ideas?
Try debugging the code. Check that the emailing sub routine is executed without errors
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Works a treat, thank you very much for all your help
ASKER
Hi, Just spotted one issue that I hadn't noticed - the email for the first recipient never has his / her email address added, the rest of it is fine (i.e. the email is personalised & has the right data in it) - any ideas?
Same bug. TheirEmail was not initialised.
If creator <> "" And creator <> rst!Creator Then
body = body & vbCrLf & "Regards," & vbCrLf & "The Administrator"
Call doEmailOutlook(theirEmail, "Your sins", body, "")
body = ""
End If
creator = rst!Creator
theirEmail = rst!EmailAddress
If body = "" Then
body = "Hi " & rst!Creator & "," & vbCrLf & vbCrLf
body = body & "You have sinned again. Please find some time today to fix it." & vbCrLf
End If
If creator <> "" And creator <> rst!Creator Then
body = body & vbCrLf & "Regards," & vbCrLf & "The Administrator"
Call doEmailOutlook(theirEmail,
body = ""
End If
creator = rst!Creator
theirEmail = rst!EmailAddress
If body = "" Then
body = "Hi " & rst!Creator & "," & vbCrLf & vbCrLf
body = body & "You have sinned again. Please find some time today to fix it." & vbCrLf
End If
ASKER
brilliant - thank you
Open in new window
Add this (or other functions) to your module in Access.Now, create a form and put a button there. Call it "Annoy sinners!". In the code of the function write this:
Open in new window