Solved

Emailing from a distribution List in Lotus Notes

Posted on 2006-11-01
13
811 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

785 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