Automate sending emails with tasks

  • 1
  • 1
  • Question
  • Updated 2 years ago
  • Answered

My Project Management processes (App containing a Customer table, with related Projects with multiple tasks) require that I send my customers regular emails based on "tasks / mileposts" completed.  Ex.:

  • I order a product from ACME to be delivered to my Customer
  • I need to send an email to my Customer with an ETA of when they can expect to receive that ACME product
  • Later, when ACME knows the *exact* date of the delivery, they let me know
  • I need to send another email to my Customer with the better/specific date/time of delivery
  • etc.

A whole bunch of repetitive follow-ups --- which I would like to automate.

I have like 10-15 text templates which I re-use constantly, with maybe some minor customizations needed before sending.  So my question is:

Can I:

  1. Pre-configure / pre-enter those text templates in QB somewhere?
  2. Have "Tasks" *trigger" a new "event" (or I can select a drop-down) whereas a specific email text-template is generated, ready to be sent to my Customer
  3. I can make the required customizations --- and then send the email
  4. That becomes an "event" in my "Task" with a timestamp.


Thanks!

Photo of Nicolas

Nicolas

  • 0 Points

Posted 3 years ago

  • 1
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,422 Points 50k badge 2x thumb
There are two directions here and I'm not sure which one you want to go.

1. You can set up a selection of "Open" type email Notifications which would be triggered automatically based on tasks record being changed and certain conditions being true.  The email Notification can be set up to come from the [Last Modified by] so that the customer can "reply" if they have questions. The advantage of that method is that it takes no exyrta work as you need to update QuickBase anyways.

2. You can configure URL to generate what is called a MAILTO command, which will call up your local email client such as  as Outlook or Gmail and prepopulate the TO, CC, BCC, SUBJECT and BODY.  You would need to click a button(s) to call up that templated email which you would hand tailor and then send.
Photo of Nicolas

Nicolas

  • 0 Points
Even as I would LOVE to know more about the first direction (because I can see that it would be useful under certain circumstances / kindly point to more details in the help file somewhere if you can?  :smile: ), it sounds like the first kind would not permit customization before being sent automatically.

Therefore, for this specific problem, I think the URL would probably be best --- We use Outlook.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,422 Points 50k badge 2x thumb
The mailto command is really nothing specifically to do with QuickBase.  It's a generic URL which all browsers recognize. 



It will be a URL formula field type.  Since you are building a URL, you may not include any spaces or special characters, so all text in the subject and the body needs to be URLEcoded.  To get a paragraph break you use "%0A%0A" 



I suggest that you start with a small formula and build it up step by step



You can use a URL Formula to pre-populate many fields in your email message in addition to the recipient, such as the CC, BCC, Subject, and message body, like this:

"mailto:" & [To:] & "?cc=" & [CC] & "&bcc=" & [BCC] & "&subject=" & [Subject] & "&body=" & [Body]
&"%0A%0A"
&"more body goes here"


// the above text does the subject line, below that is the body
// the %0A is a line feed type indicator. 

&"&body=URLEncode("The following Go Truck had a Quality Problem:")
&"%0A%0A"
& URLEncode("Stat " &[Go Truck Status Code] & ", Shipped " & [Ship date])

&"%0A" & "Thx " & "%0A" & URLEncode(UserToName([Last Modified By])) 
Photo of Nicolas

Nicolas

  • 0 Points
Thank you SO much.

So, I guess what I will do is to create a new table, that I will label "Email Communications" (with a T2T relationship with the "Customer" table, to be able to gain access to the email address field), and I will prepare each email template in there.

How would I print a "timestamp" every time I use one of those fields?

Thanks!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,422 Points 50k badge 2x thumb
Can you clarify this:
How would I print a "timestamp" every time I use one of those fields?

Do you mean to record somewhere the date / time that you pushed the button to create a Mailto email?
Photo of Nicolas

Nicolas

  • 0 Points
Kinda -- I do realize it is kinda silly.

There is a "log the edits" option on some text fields.
I guess I am looking for a way to track when / whether a specific email was sent on a specific date?
Photo of Nicolas

Nicolas

  • 0 Points
Concerning the "mailto:" & [To:]

With a T2T relationship from a table containing the email address, how would I "call" the email field to automatically fill-in the TO ?

Thanks!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,422 Points 50k badge 2x thumb
On the time stamp issue, when you get the MailTo command working working, post back and i will show you how to stamp the current date / time when the butotn is pushes and the put up that MailTo command.  .  

On the email address field, are you saying that the email address that you need to send to is not on the table that you are launching from?  if that is the case you will need to describe any relationships you have in place as to where the email address is stored in your app and which table you are sitting on when you push the button.
Photo of Nicolas

Nicolas

  • 0 Points
The email will be located in a separate table "Customers" / Field "email".

The relationships are as followed:

Customers --> many Projects --> many Tasks

I will create the EmailComm table to live basically alongside the Tasks table:

Customers --> many Projects --> many EmailComm

(Does that make sense?  :smile:   )
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,422 Points 50k badge 2x thumb
Actually
1 Customer has many Projects
1 Customer has many emailComm
Photo of Nicolas

Nicolas

  • 0 Points
I'm glad you ask the question --- Because, my confusion comes from the fact that the "Tasks" table:

Customers --> many Projects --> many Tasks

does not have a specific direct relationship of type:

Customers --> many Tasks

The tasks are only in relationship to the "Projects."


Similarly, conceptually, those "Email Communications" really should belong to a "Project", even as they need to use data (email address) contained within the "Customers" table.

Thoughts?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,422 Points 50k badge 2x thumb
Do you want to choose an email from the list of emails for that customer for every different Mailto email?  Or do you want to choose an email address (contact) to be used for emails from a particular project.
Photo of Nicolas

Nicolas

  • 0 Points
Contact.

So then I should add that relationship.

I will post a separate thread for the other question. :smile:
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,422 Points 50k badge 2x thumb
what does "Contact" mean.

Why do you think that you need a relationship directly from Customers to tasks?  That does not sound right to me.
Photo of Nicolas

Nicolas

  • 0 Points
In the Customers table, there is a field for "email" (which contains an email address)

*That* is the field I seek to fetch to auto-fill my EmailComm table (for the "TO").

And my question is --- Do I *need* this kind of T2T relationship?  Customers --> many EmailComms

Or ---

Is is sufficient to only establish the same kind of T2T relationshp that exists with the Tasks table:

Customers --> many Projects --> many Tasks

Thus:

Customers --> many Projects --> many EmailComms

:smile:

Am I making sense?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,422 Points 50k badge 2x thumb
sorry, maybe I'm not understanding your data setup.  Does each customer have many possible contacts and you need to choose which customer contact to send to, or does each customer record have a a couple of fields for the contact name and their emails
Photo of Nicolas

Nicolas

  • 0 Points
I am *so* sorry for all those silly questions.  I now understand what I was doing wrong and I realise my question did not make any sense.  I did not understand how to create "2-hoppers" (field lookups) but once I figured that out, I was able to conceptualise the whole thing.

I thank you again --- I have more questions but I will start new threads so as not to confuse people further :smile:   Thanks!
Photo of Nicolas

Nicolas

  • 0 Points
BTW ---

Here's what I ended up doing, and it works perfectly :smile:   

------

"MailTo:"&([Customer - Email])
&"?cc="&([Customer - Salesperson])
&"&subject="&([Customer Name])&" : SomethingSomething"
&"&body="&"Ref.: Contract n° "&([Contract ID])&"  ("&([Project Name])&" --- "&([Customer - Install Address A])&")"
&"%0A%0A"
&"Good day,"
&"%0A"
&"%0A"&"SomethingSomethingSomethingSomething."
&"%0A"
&"%0A"&"SomethingSomethingSomethingSomething."
&"%0A"
&"%0A"&"Please do not hesitate to contact me if you have any questions."
&"%0A"
&"%0A"&"Regards,"
&"%0A%0A"


------
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,422 Points 50k badge 2x thumb
Great, thx for letting me know.
Photo of Jay (DDS)

Jay (DDS)

  • 430 Points 250 badge 2x thumb
I'm attempting this, but it's not working out.  Thoughts?

"MailTo:"&([Staff member])
&"?cc="&([Staff Member - Assistant])
&"&subject="You Have a Visitor In the Lobby"
&"&body=
&"%0A%0A"
&"Hello! The following visitor is in the lobby"
&"%0A"
&"%0A"&"Name:  [First Name] [Last Name]"
&"%0A"
&"%0A"&"Arrival Time:  [Arrival Time]"
&"%0A"
&"%0A"&"Appointment Time:  [Appointment Time]"
&"%0A"
&"%0A"&"Regards,"

&"%0A%0A"