?
Solved

Emailing from a distribution List in Lotus Notes

Posted on 2006-11-01
13
Medium Priority
?
825 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
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…
Suggested Courses

777 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