Solved

Emailing from a distribution List in Lotus Notes

Posted on 2006-11-01
13
808 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
 

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
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: 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

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)

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

910 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now