Link to home
Start Free TrialLog in
Avatar of tomhwtt
tomhwtt

asked on

CFMAIL using a database and queries

I have created a database that holds emails I can send off later using a query.  I am having a hard time wrapping my head around something that I hope I can explain properly.

Lets say the fields in the database are called.

Email_to
Email_from
Email_subject
Email_body

Then I call the emails using a query later, that sends out an email like this.

<cfmail to="#queryname.email_to#"  from= "#queryname.email_From#"  subject= "#queryname.email_Subject#">

#queryname.email_Body#

</cfmail>

This works fine for a basic email..but I want to call other fields into this email.

So, in the database the field email_Body would look like:

Hi, [first_name],

What is a good way to get this to pull the first_name from the related customer database? I have figured out how to do this if I store the email body as an html page ...but not if I pull it from the database.

Thanks for any insight or pushes in the right direction on this.

T
Avatar of SidFishes
SidFishes
Flag of Canada image

"I have figured out how to do this if I store the email body as an html page"

then  you've figured out how to do this...

<cfmail ...>

<html>
<head />
<body>
<cfquery...>
select * from contacts
</cfquery>

Hi #contact.firstname#,

We last talked #contacts.lastcontact#

blah blah


</body>
</html>



</cfmail>

by far the simplest

 you could store the html in a db but you need to create placeholders for your variable fields

Hi {firstname},

We last talked {lastcontact}

 then replace these on the fly with your query fields which can be a bit ugly

if you want templates..the easiest is to just have several different includes based on criteria












You will also need to tell the CFMAIL tag that the TYPE="html" if you plan to use html to build the body.  Otherwise all the formatting will appear in the text.
ASKER CERTIFIED SOLUTION
Avatar of tomhwtt
tomhwtt

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Sid...you would want to do the query OUTSIDE of the <cfmail> tag, otherwise you will loop the ALL the output in the mail itself instead of data for the individual user.

<html>
<head>
<body>
<cfquery...>
select [datafields]
from table
</cfquery>

<cfmail...>
Hi [usernameField],

We last spoke on [dateField]......
</cfmail>
"Sid...you would want to do the query OUTSIDE of the <cfmail> tag"

you'd be right if it was for looping... I was only thinking about a single result with my pseudo code (however the "query" should have had a where clause to make that clearer.)


ah gotcha...still to be on the safeside I always put the query outside the cfmail..JUST in case you get multiple returns.  But thats just me.