Solved

Emailing from a distribution List in Lotus Notes

Posted on 2006-11-01
13
814 Views
Last Modified: 2008-03-10
I'm having a problem e-mailing a distribution list given certain criteria.  I think it might have to do with my table structure.  

I have one table (tblGEFSAll) that has a field called TraningTypeID with the values Automation, General,...
I have another table (tblTraining).  This table list all the names, with e-mail addresses.  It then has check boxes for training type, eg. Automation, General.....

I want to email individuals that a document is effective that they are required to train on.  I'm trying to do this based upon the ApprovedDate field in tblGEFSAll.  Something like...when the tblGEFSAll.ApprovedDate = Date and tblGEFSAll.TrainingTypeID = Automation and tblTraining.Automation = True then SendObject.  

Is this possible given the structure of my tables?

I tried some code I got from here that I thought might work, but it doesn't work correctly.  It's just puts someone’s name in the TO: field.  

The e-mail app I'm using is Lotus Notes.


 Dim strToList As String
    Dim strSQL As String

    strSQL = "SELECT tblGEFSAll.Approved, tblGEFSAll.TrainingModuleID FROM tblGEFSAll WHERE (((tblGEFSAll.Approved) _     =Date()) AND ((tblGEFSAll.TrainingModuleID)=54));" '54 = Automation
    strToList = CurrentProject.Connection.Execute(strSQL).GetString(2, , "", "; ")
    DoCmd.SendObject acSendNoObject, , , strToList, , , "Test", , 0

I'm not quite sure what the CurrentProject.Connection part of this code is doing.  
0
Comment
Question by:gfortuna
  • 7
  • 5
13 Comments
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17852080
from your code, you're not pulling in an e-mail address. when you create your strSQL, are you pulling from the tblTraining at all? Is there a field that links those two tables together?

so far your logic has holes in it.

1) get all dates = today where automation = 54.
2) Hole - Based on that record, where do I get my e-mail address from?

Lotus notes is irrelevent with the Send Object command...it uses your default mail program.

    Dim strToList As String
    Dim strSQL As String
    Dim rs As DAO.Recordset
   

    strSQL = "SELECT tblGEFSAll.Approved, tblGEFSAll.TrainingModuleID FROM tblGEFSAll WHERE (((tblGEFSAll.Approved)" & _
    " =Date()) AND ((tblGEFSAll.TrainingModuleID)=54));" '54 = Automation
    Set rs = CurrentProject.OpenConnection(strSQL)
    If rs.EOF Then
    Exit Sub
    End If
    rs.MoveFirst
    Do Until rs.EOF
    strToList = strToList & "; " & rs.Fields("SomeFieldThatisNOTinyourrecordset").Value
    rs.MoveNext
    Loop
    If Left(strToList, 1) = ";" Then
    strToList = Right(strToList, Len(strToList) - 1)
    End If
    DoCmd.SendObject acSendNoObject, , , strToList, , , "Test", , 0
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17852084
You're just emailing them - not attaching anything?
(Lotus Notes doesn't have as tight integration - so you're leaving it at the default email client being used?)

You'll need to get your SQL string right.
However - the two tables need to be linked somehow yes?
You say you have a checkbox in tblTraining that identifies it as being for Automation?
That sounds a bit nasty structure wise (a link tables would be more standard) - but none the less you must have some link between the tables?
Is it just that?
How does the Date field then come into play at all?

You'd have though it would be something like

strSQL = "SELECT tblTraining.Email FROM tblTraining WHERE tblTraining.Automation = True AND tblTraining.Email Is Not Null"

That'll just get you the emails of people who are on that course?
But you need to link the ID's somehow to use the date value.

As for CurrentProject.Connection, that's the application's current data connection (to itself - the tables within it) and the Execute action opens an implicit recordset and the GetString method returns a delimited list of fields from all records fetched in the recordset.
0
 

Author Comment

by:gfortuna
ID: 17852727
That's the main problem.  I need to redo my table structure.  I'll do that and get back to this post.  Thanks for both of your responses.

0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:gfortuna
ID: 17868019
Ok, restructured my tables.  Sorry for the delay in getting back to you.  
I have a query now that pulls the e-mails to be sent given the particular criteria.  
The query works correctly, but I'm still unsure of the correct format in the VB.  I don't understand the code in Jeff’s response maybe that will work, but I can't decipher it.

Here is what I have right now.  Doesn't work, not even close.  It puts the sql statement Email in the TO: of the e-mail.

Dim Email as String

EMail = "SELECT
FROM (tblTrainingModule INNER JOIN tblGEFSAll ON tblTrainingModule.TrainingModuleID = tblGEFSAll.TrainingModuleID) INNER JOIN tblTrainingEmployee ON tblTrainingModule.TrainingModuleID = tblTrainingEmployee.TrainingType
WHERE (((tblGEFSAll.DocumentNumber)=[Forms]![frmEditDetails]![DocumentNumber]) AND ((tblTrainingEmployee.TrainingType)=[Forms]![frmEditDetails]![TrainingModuleID]) AND ((tblGEFSAll.Approved)<=Date()));"

DoCmd.SendObject acSendReport, "rptGEFSSummary", , Email, , , "Test", , 0

It looks at the Training ModuleID on the frmEditDetails and the Approved Date.  If these meet the criteria then it looks to see if an employee is assigned that Training Module in tblTrainingEmployee.  It then selects the e-mail addresses.

Thanks for your help!
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17868105
We can keep it more direct for you by sticking to the code in your original post.
(You still don't select the email field though - surely that one field is the pivotal point).

    Dim strToList As String
    Dim strSQL As String
    Dim Email as String
'----------------------------\/ change this field for your own email field
    strSQL = "SELECT EmailField " & _
                  "FROM (tblTrainingModule INNER JOIN tblGEFSAll ON tblTrainingModule.TrainingModuleID = tblGEFSAll.TrainingModuleID) " & _
                  "  INNER JOIN tblTrainingEmployee ON tblTrainingModule.TrainingModuleID = tblTrainingEmployee.TrainingType " & _
                  "WHERE (((tblGEFSAll.DocumentNumber)=[Forms]![frmEditDetails]![DocumentNumber]) " & _
                  "  AND ((tblTrainingEmployee.TrainingType)=[Forms]![frmEditDetails]![TrainingModuleID]) " & _
                  "  AND ((tblGEFSAll.Approved)<=Date()));"

    strToList = CurrentProject.Connection.Execute(strSQL).GetString(2, , "", "; ")
    DoCmd.SendObject acSendReport, "rptGEFSSummary", acFormatRTF, strToList, , , "Test", , 0
0
 

Author Comment

by:gfortuna
ID: 17868202
Thanks LPurvis

I tried to run that but it gives me the following error.  "No Value given for one or more required parameters" on the strToList
I changed the sql statement to get the e-mail address.  Missed that on the previous one.


  Dim strToList As String
    Dim strSQL As String
    Dim Email As String

    strSQL = "SELECT tblTraining.[E-Mail]" &_
"FROM ((tblTrainingModule INNER JOIN tblGEFSAll ON tblTrainingModule.TrainingModuleID = tblGEFSAll.TrainingModuleID)"&_
"INNER JOIN tblTrainingEmployee ON tblTrainingModule.TrainingModuleID = tblTrainingEmployee.TrainingType)" &_
"INNER JOIN tblTraining ON tblTrainingEmployee.EMP_ID = tblTraining.EMP_ID"
&_"WHERE (((tblGEFSAll.DocumentNumber)=[Forms]![frmEditDetails]![DocumentNumber]) AND ((tblTrainingEmployee.TrainingType)=[Forms]![frmEditDetails]![TrainingModuleID]) AND ((tblGEFSAll.Approved)<=Date()));"

    strToList = CurrentProject.Connection.Execute(strSQL).GetString(0, , "", "; ")
    DoCmd.SendObject acSendReport, "rptGEFSSummary", acFormatRTF, strToList, , , "Test", , 0

Thanks.
0
 

Author Comment

by:gfortuna
ID: 17868241
Also, in the above post i changed the GetSting to 1 then 0.  I thought that might be the problem.

Thanks again!
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17868314
No - that has to be 2 (in fact, as I recall that's the only acceptable value - which is an odd parameter).

strSQL = "SELECT tblTraining.[E-Mail]" &_
              "FROM ((tblTrainingModule INNER JOIN tblGEFSAll ON tblTrainingModule.TrainingModuleID = tblGEFSAll.TrainingModuleID) " & _
              "    INNER JOIN tblTrainingEmployee ON tblTrainingModule.TrainingModuleID = tblTrainingEmployee.TrainingType) " & _
              "    INNER JOIN tblTraining ON tblTrainingEmployee.EMP_ID = tblTraining.EMP_ID " & _
              "WHERE (((tblGEFSAll.DocumentNumber)=" & Forms!frmEditDetails!DocumentNumber & ") " & _
              "   AND ((tblTrainingEmployee.TrainingType)=" & Forms!frmEditDetails!TrainingModuleID & ") AND ((tblGEFSAll.Approved)<=Date()));"
0
 

Author Comment

by:gfortuna
ID: 17868373
It's still giving me the same error.  "No Value given for one or more required parameters" on the following code

strToList = CurrentProject.Connection.Execute(strSQL).GetString(2, , "", "; ")

Is there some add in I'm missing to run this?
0
 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 500 total points
ID: 17868468
strSQL = "SELECT tblTraining.[E-Mail]" &_
              "FROM ((tblTrainingModule INNER JOIN tblGEFSAll ON tblTrainingModule.TrainingModuleID = tblGEFSAll.TrainingModuleID) " & _
              "    INNER JOIN tblTrainingEmployee ON tblTrainingModule.TrainingModuleID = tblTrainingEmployee.TrainingType) " & _
              "    INNER JOIN tblTraining ON tblTrainingEmployee.EMP_ID = tblTraining.EMP_ID " & _
              "WHERE (((tblGEFSAll.DocumentNumber)='" & Forms!frmEditDetails!DocumentNumber & "') " & _
              "   AND ((tblTrainingEmployee.TrainingType)=" & Forms!frmEditDetails!TrainingModuleID & ") AND ((tblGEFSAll.Approved)<=Date()));"
0
 

Author Comment

by:gfortuna
ID: 17868505
I have no clue what you did there but it worked.  The sql statement looks exactly the same.  

Thanks for your help!
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17868616
I assumed that DocumentNumber mustn't be an actual number - and delimited the expression with single quotes.

Glad you're sorted.
0
 

Author Comment

by:gfortuna
ID: 17868745
You were right.  The Doc Num is numbers and letters.

Thanks again for your help!
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question