E-mail Example 1

[AlanBreck1000]

Does anybody have an example of using email templates in clarion 6. Basically what I would like to do is query the database and come up with a list, which I can do right now, then be able to bulk e-mail that list. I have tried the example in the examples folder of Clarion 6, works once then does not work again. I have also tried Vivid Help Systems demo but the documentation is not all that good and I am having some difficulty with it. Is there any real simple solution out there?

 

[ShankarJ]

Hi!

Check out vuMail from

http://www.valutilities.com.

Regards

 

[AlanBreck1000]

Thanks Shankar, I went ahead and purchashed the program, works great except for one function and maybe its me, more than likely. I tried to qbe a database, save the results, then make the CSV file using vuMailCreate() function and then append using vuAppendCSV() function. I can get the qbe to filter and then send the results to a local variable and I can make the blank csv file, I just cannot get the append to work with the query results, I have e-mailed them but I was wondering if you could shed some light on the subject, it is a great program and the demo does show how to email from within my app, thanks for the point in the right direction.

 

[ShankarJ]

Hi Alan,

I have never used the CSV bit so far but I am sure Bill Roe will reply to you soon.

You could always create your own CSV file in the required format and use vuSendMailFromFile(CSVFileName, AttachmentFile).

Regards

 

[AlanBreck1000]

Shankar,
Bill did get back to me and he is working on an app to test the append CSV portion of his program.

I am apparently not smart enough to figure out how to make a CSV file and populate from a query, which is what I would like to do but all my attempts have failed.

Duncan

 

[ShankarJ]

Hi Duncan,

1. Do you hand code at least a little?
2. Do you know how to use a SET ; NEXT loop?

How do you query the database & retrieve the e-mail list?

Based on what you know, I can help you write the code.

Regards

 

[AlanBreck1000]

Shankar,
I can handcode a little bit, find the embed points and write the code.

I used the qbe template on a browse to query the database to get a list of people that meet the query's criteria, it returns a list of names along with their email address.

It is these email addresses I would like to put into a csv file for later bulk emailing using Bill Roe's vumail program, he has a function called vuMailCreate() which if you go to the accept embed point on say a button you can handcode the filename using a local variable i.e.

filename='c:\temp\emails.csv'
vuMailCreate(Filename)

Bill's program shows a function called vuMailAppendCSV() which I cannot get to work, this is supposed to populate the emails.csv file, you would then simply call his other function of vuSendMail() utilizing the csv.

What I guess it boils down to is querying the database to get a list of names and emails, making a csv file and populating it with that query. I always seem to have problems after querying to get that query into a report or utilizing it in any other procedure, followed the book examples but to no avail thats why I was going to use Bill's vuMail program, he was going to get back to me but I need to try and get this incorporated in my app but have been getting confused. Any help you could give would be appreciated.

Duncan

 

[ShankarJ]

Hi Duncan,

If you look at the generated source (CLW) of your browse procedure, you will find the VIEW definition for your browse.

In the example below ::

[red]BRW1::View:Browse[/red]    VIEW(DEPMNT)
                       PROJECT(DEP:Code)
                       PROJECT(DEP:Desc)
                     END
[red]Queue:DPT[/red]            QUEUE 
DEP:Code               LIKE(DEP:Code)
DEP:Desc               LIKE(DEP:Desc)
Mark                   BYTE          
ViewPosition           STRING(1024)  
                     END

[red]BRW1::View:Browse[/red] - Name of Browse View (<BrowseView>)
[red]Queue:DPT[/red] - Name of Browse Queue (<BrowseQueue>)

Create a Data embed with the following definition ::

MyEmailFile        FILE,DRIVER('BASIC'),PRE(EML),CREATE
Record               RECORD
FROM                   STRING(256)
TO                     STRING(256)
CC                     STRING(256)
BCC                    STRING(256)
SUBJECT                STRING(256)
BODY                   STRING(256)
ATTACH                 STRING(256)
                   . .

On the Accepted Embed of the E-MAIL button, do the following ::

  MyEmailFile{PROP:Name} = '<Name of CSV File to Create>'

  CREATE(MyEmailFile)
  IF ERRORCODE()
     MESSAGE(ERRORFILE() & ' : ' & ERROR() & ' [' & ERRORCODE() & ']', 'E R R O R - CREATE()')
  ELSE
     OPEN(MyEmailFile, 12H)
     IF ERRORCODE()
        MESSAGE(ERRORFILE() & ' : ' & ERROR() & ' [' & ERRORCODE() & ']', 'E R R O R - OPEN()')
     ELSE
        ... insert the BrowseView or BrowseQueue scanning code as shown below ... 
     END
  END
  CLOSE(MyEmailFile)

  ... CALL vuMail Function ...

If you Browse is PAGE loaded, you need to use the Browse View since the Browse queue will only contain one page of records BUT if your browse is FILE loaded, you can use the Browse Queue directly.

Code Using the Browse View ::

SET(<BrowseView>)
LOOP
   NEXT(<BrowseView>)

   IF ERRORCODE() THEN BREAK.

   CLEAR(EML:Record)
   EML:FROM    = '<DatabaseColumn>'  ! you can use the database columns directly here <PREFIX>:<COLUMNNAME>
   EML:TO      = '<DatabaseColumn>'  ! i.e. EML:FROM = DAT:Email
   EML:CC      = '<DatabaseColumn>'
   EML:BCC     = '<DatabaseColumn>'
   EML:SUBJECT = '<DatabaseColumn>'
   EML:BODY    = '<DatabaseColumn>'
   EML:ATTACH  = '<DatabaseColumn>'

   ADD(MyEmailFile)
   IF ERRORCODE()
      MESSAGE(ERRORFILE() & ' : ' & ERROR() & ' [' & ERRORCODE() & ']', 'E R R O R - ADD()')
   END
END

Code Using the Browse queue ::

LOOP Row# = 1 TO RECORDS(<BrowseQueue>)
   GET(<BrowseQueue>, Row#)

   CLEAR(EML:Record)
   EML:FROM    = '<QueueColumn>'  ! you HAVE to use the QUEUE columns ONLY AS <BrowseQueue>.<PREFIX>:<COLUMNNAME>
   EML:TO      = '<QueueColumn>'  ! i.e. EML:FROM = Queue:DAT.DAT:Email
   EML:CC      = '<QueueColumn>'
   EML:BCC     = '<QueueColumn>'
   EML:SUBJECT = '<QueueColumn>'
   EML:BODY    = '<QueueColumn>'
   EML:ATTACH  = '<QueueColumn>'

   ADD(MyEmailFile)
   IF ERRORCODE()
      MESSAGE(ERRORFILE() & ' : ' & ERROR() & ' [' & ERRORCODE() & ']', 'E R R O R - ADD()')
   END
END

Make sure you add the BASIC database driver in your project settings before compiling.

Regards

 

[AlanBreck1000]

Shankar,
Thank you for the code, I seem to have a problem at the accept embed of the email button, I get an unknown identifier of MyEmailFile everytime I compile.

 

[AlanBreck1000]

I must be declaring the data in the wrong area, I thought that data had to be declared after the Member and before the Procedure, all I get is errors when compiling.

 

[AlanBreck1000]

Shankar,
I think I finally got it to work with the exception of where you reference EML:TO, if I rem this out it works but everytime I use it I get compile errors. I do know that if I was to use this field name in a clarion database, clarion does not recognize it as a valid calrion name.

I have managed to get the names into a csv, I renamed EML:TO to EML:EMTO and it worked although I would assume it would not be reference correctly in the csv.

 

[ShankarJ]

Hi Duncan,

The problem is because TO is a reserved name (as used in LOOP xx = 1 to 10 ... END or CASE xxx OF 1 to 10 ... END). Change it to something else.

Regards