?
Solved

Emailing from a distribution List in Lotus Notes

Posted on 2006-11-01
13
Medium Priority
?
830 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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 …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

649 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