Forum Discussion

IvanWeiss's avatar
IvanWeiss
Qrew Captain
6 years ago

Start Email in Outlook with To and Subject Filled Out

I am very new to Quick Base so I kind of am looking for help on this with some baby steps (appreciate that in advance)!  I am creating a project management app and created a "Project Directory"  I hav...
APIs and custom code
Forms
NotificationsEmail
JordanMcAlister's avatar
JordanMcAlister
Qrew Captain
6 years ago
Hey! Below is some of my code that I use for you to copy if you'd like. My URL formula button opens up an email in outlook with the "To" and "Subject" already filled in as well as some of the body based on some conditions.

______________________________________________________________________

var text URLONE = "MailTo:emailaddress1"
&
If([District]="Longview",
"; emailaddress2; emailaddress3",""
)
&"?cc= "
&"&subject=" &
If(
[Time Frame]="Priority","Approved Priority Job WR ","Approved Job WR "
)
&[WR #]

&"&body=WR "&[WR #] & " has been approved. Attached is the job packet" &
If([District]="Longview",
" and locate sheet." & "%0d%0a%0d%0a" & "CIR " & [Circuit] & " " & [Substation],"."
)
;

$URLONE
______________________________________________________________________

My URL button with this code gives me this result when I click the button.




So, for your case, I think you would need a text field that puts all the users in the project into the format of "email1; email2; email3; etc." and name it for example - [Project Email List].

Your URL formula code might look like this:
_____________________________________________________________________

var text URLONE = "MailTo:" & [Project Email List]

&"?cc= "
&"&subject=" & [Project Name];

$URLONE
______________________________________________________________________

Then your users can fill out the rest, unless you want some sort of pre constructed body format.

Hope this helps.
  • IvanWeiss's avatar
    IvanWeiss
    Qrew Captain
    6 years ago
    Okay so this helps A LOT and I appreciate the time to post this!

    Now what I am not sure of is how I can create that email list.....

    Is it an automation where I need to append to data in a field and add the email address with a semi colon?

    Or is it a formula and do I have it set to add the text strings together?

    But how do I access the email address from the user table for my variable?

    So I have (3) users in my app as a test

    Aaron Weiss
    John Brooks
    Ivan Weiss


    Lets say I am the "project designer" which is a field in my table and John Brooks is my "project estimator" which is a seperate field in my table.

    I have (3) other roles that are fields in my table but they are blank....

    How do I generate the email list for the two of us?
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    The field you would create in your Project record would be like

    List(";", 
    UserToEmail([Project designer userid field]),
    UserToEmail([Project estimator userid field]),
    UserToEmail([Project planner userid field]))

    That will provide a semi colon delimited list of email addresses.

  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    .. a few other tips

    to introduce a carriage return use

    &"%0A%0A"

    The above will do two of them to create a new line with  as pace between the previous line.

    The other tip is that for any text in thew subject or body, you need to URLEncode because you are creating a URL and URLs do now allow for special characters.  So for example


    & "&subject=" & URLEncode( ..... the subject)

    & "&body=" & URLEncode( ..... the body)

  • IvanWeiss's avatar
    IvanWeiss
    Qrew Captain
    6 years ago
    Thanks for the List comment!  That is exactly what I was missing.  I was thinking I needed to iterate through them to see if they were blank first.  That is what tripped me up....

    Okay so one more question and we got it set....

    On the projects table I am displaying the fields we used above and I have the button created to email them.

    I have a "child" table labeled Project Members for Contacts that are external to us.  So on my projects form I want to have a button that is able to email those child table of project members (all of the ones that relate to the project table I am on) and cc the internal members of the team.

    CC'ing the internal members is easy based on the information I have.  What I am not sure is how to get the record of that child data to a button on the parent table....
  • JordanMcAlister's avatar
    JordanMcAlister
    Qrew Captain
    6 years ago
    Hmmm, Mark may be more experienced to help with that one. The only I can think of for that is, perhaps, creating the button in the Project Members table and then making a report with just that button in it. Then, adding a report link field in the Projects Table and adding it to the Project Form as an embedded report, then you'll see the button from the Project Form.

    Something similar to this..


    Maybe Mark will have a simpler solution.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    Yep, I knew that question was coming but I wanted to eat this elephant just one bite at a time.

    So last December or so we got a new type of Summary field called Combined text summary.

    So make a Combined Text summary field of the email addresses of the external members.  Call it like

    [Combined Text summary of all external emails (raw)]   

    On the parent table it will be a combined text summary field type, so then make a new field with a clean name like

    [External Team member emails]

    The formula will be]

    ToText([Combined Text summary of all external emails (raw)])

    Conveniently that results in a semi colon delimited list, and you should be able to use that  in your mailto formula field.

    If you need to have commas as a separator, then

    the formula would be
    SearchAndReplace(
    ToText([Combined Text summary of all external emails (raw)]),";",","
    )


      
  • IvanWeiss's avatar
    IvanWeiss
    Qrew Captain
    6 years ago
    So that is where I assumed this was headed as well and I tried to create the summary field in my Projects Table.  However my Project members table is a many to many table that is related to my contacts table.

    I am trying to summary Combined Text over Contact-Email and it says that summary function cannot be applied to this field.

    Is it because I am in a multi level relation?
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    Can you try this

    Make a formula field in the many to many child table called 
    [email in text format]

    the formula will be

    ToText([email field])

    Now you have a text field to summarize and it will work.
  • IvanWeiss's avatar
    IvanWeiss
    Qrew Captain
    6 years ago
    That worked!  But I am still getting one bug that I am struggling with....  For some reason my cc line is inserting the email address correctly but the ?subject code is also appearing in the cc line.  This only happens on the external list.  With the internal list there is no cc line and it works perfectly.  Here is my code:

    var text URLONE = "MailTo:" & [Combined Text External  Email List]

    &"?cc=" & [Internal Email List]
    &"?subject=" & [Name];

    $URLONE
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    6 years ago
    There is only one ?, and that is after the before the cc, like you have.

    The subject and the body indicators are not preceded with a ?

    & "&subject=" & URLEncode([Name]);
    & "&body=" & & URLEncode([your body text field])