The problem is that the SRPL would change ALL the commas to a ~~, so both the
actual delimiter that I need for an SSUB to work, as well as the embedded comma
in the name. I would have to be able to distinguish the comma that separates
addresses from a comma that's embedded in a name. Yes, I could write a couple
dozen lines to code to look for " or look for <... and have the program figure
out where that comma is embedded. For now we've scrapped it and we'll
investigate how the commas get in the names to begin with, and prevent that
from happening in the future.
In case this code would help anyone else with a similar RMail issue when trying
to convert from 7.6 RMail where everything is on one row to RMail 9.5/10 where
each recipient needs a separate line, here's what I came up with.
Even if your email addresses are in multiple rows, the last section of code
helps construct your individual Plugin statements.
Karen
My data & email setup:
EmailDetail table has columns: EmailTo, EmailCC, EmailBCC, EmailID (PK)
Rather than a multi-row table, this has a single row per email, and all 3 email
columns would have multiple email addresses separated by commas
What we do: emails are NOT sent out right away. They are held in a table
with a "DateTimeSent" column. We have a Scheduled Task set up to run every 15
minutes to send out emails based on the date being null. In this way we have
an RBase table with a record of every email sent. Only the server sends out
emails, none of the workstations. We have a "view emails" menu option so they
can use RBase to search for emails sent, which they like much better than an
Outlook search!
RMail send program:
-- this temp table will hold email addresses on separate rows
CREATE TEMP TABLE tmpEmailAdd (EmailID INT, EmailType TEXT 3, EmailAddress TEXT
40)
-- This is my code to parse out email addresses. The "continue" section is
what would ignore commas embedded in a name
-- There's 3 of these code sections, for EmailTo, EmailCC and EmailBCC
-- You'll notice my SRPL of the quotes character. Another difference with
RMail running in version X is
-- that RMail automatically puts its own " around the name in an email address.
So if your data already
-- has quotes in it (like mine sometimes does), you get double "" and RMail
will fail. Took me a while to figure that
-- one out! Thank you RMail log files!
SELECT EmailCC INTO vText IND iv1 FROM EmailDetail WHERE EmailID =
.vEmailID
IF vText IS NOT NULL THEN
SET VAR vText = (SRPL(.vText, (CVAL("QUOTES")), "", 0))
SET VAR vLoop INT = 1
WHILE 1 = 1 THEN
SET VAR vText1 = (SSUB(.vText,.vLoop))
IF vText1 IS NULL THEN
BREAK
ENDIF
-- See if there's a valid email in there. Could be a name comma
SET VAR vCount = (SLOC(.vText1,"@"))
IF vCount = 0 THEN
CONTINUE
ENDIF
INSERT INTO tmpEmailAdd (EmailID, EmailType, EmailAddress) +
VALUES .vAlertID, "CC", .vText1
SET VAR vLoop = (.vLoop + 1)
ENDWHILE
ENDIF
-- So now we're in the code where the RMail Plugin runs.
-- Again, separate sections for To, CC and BCC
DROP CURSOR c2
DECLARE c2 CURSOR FOR SELECT emailaddress FROM tmpEmailAdd +
WHERE EmailID = .vEmailID AND EmailType = "CC"
OPEN c2
WHILE 1 = 1 THEN
FETCH c2 INTO vEAdd
IF SQLCODE = 100 THEN
BREAK
ENDIF
SET VAR vCommand = ("PLUGIN RMail V1|ADD_CC_RECIPIENT" & .vEAdd)
&vCommand
ENDWHILE
Karen
-----Original Message-----
From: Bruce A. Chitiea <[email protected]>
To: karentellef <[email protected]>
Sent: Fri, Oct 23, 2015 4:35 pm
Subject: [RBASE-L] - RE: Help with parsing email addresses?
Karen:
It seems like the variably embedded comma is the bottleneck to the SSUB
solution.
Drawing upon Jeff Richardson’s earlier post, why not SRPL the embedded comma to
a really unlikely string like ‘~~” (double tilde), let SSUB do it’s magic, then
SRPL the comma back into play when you’ve rendered it harmless?
Bruce Chitiea
SafeSectors, Inc.
909.238.9012 mobile
From: [email protected] [mailto:[email protected]] On Behalf Of Karen Tellef
Sent: Friday, October 23, 2015 6:42 AM
To: [email protected]
Subject: [RBASE-L] - RE: Help with parsing email addresses?
I should have mentioned that for now we've decided on a solution.
In most of the cases, using a simple SSUB will extract the email
addreses. The only thing that SSUB doesn't work on is if a comma
is embedded in the name:
"tellef, karenf" <[email protected]>, [email protected]
In the above case, a SSUB(...1) would extract just the word "Tellef".
So I just test that the result contains a "@" and if it doesn't just
throw it away. The SSUB(..2) would extract just "karen" as the
name and they're fine with that. I could have added code for the
SSUB(...1) so get characters from the first ", but they said not to
bother right now.
We're going to update the list of frequent emails to get rid of those
commas and add code to prevent it from happening in the future.
Karen
-----Original Message-----
From: Karen Tellef <[email protected]>
To: rbase-l <[email protected]>
Sent: Fri, Oct 23, 2015 8:28 am
Subject: Re: [RBASE-L] - RE: Help with parsing email addresses?
Well yes and no. I actually want to KEEP the "name", not parse out
just the address. And actually the data is more complicated than what
I posted because I wanted to focus on one issue. The problem is that
the app (written by someone else) allows them to (a) type in email
addresses freehand, and/or (b) select them from a list of frequent emails
that sometimes have a name and sometimes do not. So for example,
my string can have values like below. I need to extract individual addresses
and they want me to keep the "name". This wasn't an issue in RMail for
7.6 because we sent them all on one line, but the newer RMail requires
each address on a different line. I might suggest that we totally redo the
method of them selecting an address. We can't upgrade until I have RMail
code redone so I have some time to wrestle with this.
[email protected]
[email protected], [email protected]
"karen tellef" <[email protected]>
"karen tellef" <[email protected]>, [email protected]
Nice data, huh?? I need to be off the allergic meds to tackle this one!
Karen
-----Original Message-----
From: Buddy Walker <[email protected]>
To: karentellef <[email protected]>
Sent: Thu, Oct 22, 2015 6:34 pm
Subject: [RBASE-L] - RE: Help with parsing email addresses?
Karen
Could you try something like this it should work with or without the comma
************************
SET VAR vLen INTEGER = 0
SET VAR vLoc INTEGER = 0
SET VAR vRem INTEGER = 0
SET VAR vEMail TEXT = NULL
SET VAR vEmail1 TEXT = NULL
--SET VAR vEMail1 = ('tellef, karen <[email protected]>')
SET VAR vEMail1 = ('karen tellef <[email protected]>')
IF vEMail1 CONTAINS '<' THEN
SET VAR vLen = (SLEN(vEMail1))
SET VAR vLoc = (SLOC(.vEmail1,'<'))
SET VAR vRem = (.vLen - .vLoc)
SET VAR vEMail = (SGET(.vEMail1,(.vRem -1),(.vLoc +1)))
ENDIF
SHOW VAR vEMail
****************************
Buddy
From: [email protected] [mailto:[email protected]] On Behalf Of Karen Tellef
Sent: Thursday, October 22, 2015 12:45 PM
To: Richardson, Jeff <[email protected]>
Subject: [RBASE-L] - Help with parsing email addresses?
Converting older RMail code into new RMail code. One of the things I have to
do is parse email addresses into individual RMail commands. I have no real
control over how it's getting to me.
If all data was like this, I could obviously parse using SSUB:
[email protected], [email protected]
Some of the data comes in with the "names" as part of the address. Again, this
would be easy, use SSUB
karen tellef <[email protected]>, mary smith <[email protected]
But guess what... Some of the names actually have a comma embedded:
tellef, karen <[email protected]>, mary Smith <[email protected]>
Sigh... Any suggestions on how I could program to get this into 2 lines for
an RMail send?
Thanks much!
Karen
