Hi Bryan,
Okay, I'm going to take a guess at the structure of your Table1 table:
ECR Number (Text)
Originator (Text)
Engineer Name (Text)
Manufacturing and Test Name (Text)
Master Scheduler Name (Text)
Procurement Name (Text)
P&L Managers Name (Text)
You need a union query to bring these names into one column. To create a Union query, start with
a brand new query, but do not select any tables. In query design view, click on View > SQL View.
You should see the word SELECT highlighted. Replace this word with the following SQL statement:
SELECT Originator AS Recipient FROM Table1
WHERE (((Table1.[ECR Number])=[Forms]![frmECN]![ECR Number]))
UNION
SELECT [Engineer Name] AS Recipient FROM Table1
WHERE (((Table1.[ECR Number])=[Forms]![frmECN]![ECR Number]))
UNION
SELECT [Manufacturing and Test Name] AS Recipient FROM Table1
WHERE (((Table1.[ECR Number])=[Forms]![frmECN]![ECR Number]))
UNION
SELECT [Master Scheduler Name] AS Recipient FROM Table1
WHERE (((Table1.[ECR Number])=[Forms]![frmECN]![ECR Number]))
UNION
SELECT [Procurement Name] AS Recipient FROM Table1
WHERE (((Table1.[ECR Number])=[Forms]![frmECN]![ECR Number]))
UNION
SELECT [P&L Managers Name] AS Recipient FROM Table1
WHERE (((Table1.[ECR Number])=[Forms]![frmECN]![ECR Number]))
UNION
SELECT [Quality Name] AS Recipient FROM Table1
WHERE (((Table1.[ECR Number])=[Forms]![frmECN]![ECR Number]));
I recommend saving this query as: quniAllRecipients instead of "ECN Participants". The quni
prefix is a Hungarian naming convention that identifies the object as a q=query + uni=union, or
union query.
I am using a criteria that includes:
[Forms]![FormName]![FieldName]
I've chosen frmECN as the name of the form, which gives me:
[Forms]![frmECN]![ECR Number]
as the criteria on the ECR Number field, instead of the form that you indicated:
[screen].[Activeform].[ECR Number]
When you run the union query, with the frmECN form open to a current record, you should see each
person's name as a separate record. This rather ugly looking union query is not necessary when
one has a proper database design.
Paste the following code into the code module for the frmECN form. Please note the following
points:
1.) I added my style of an error handler. This includes using
On Error Resume Next in the ExitProc section, just prior to closing objects
and setting them equal to nothing.
2.) You have a Me.Refresh that I'm not sure is required.
3.) You need to include a reference to the "Microsoft DAO 3.6 Object Library" if you don't
already have this reference set. When viewing code, click on Tools > References and scroll down
the list until you find this library. Place a check to select it.
4.) I had to modify the original BulkEmail function, in order to prevent run-time error 3061 with
a parameter query.
See:
http://support.microsoft.com/?id=210244 for more details.
5.) I eliminated two lines of code from the original BulkEmail function:
Dim strSQL As String and
strSQL = "SELECT Email FROM MyTable WHERE Email Is Not NÂull;"
since I was using a saved querydef instead.
6.) I'm not positive, but I think there are additional objects that you should be closing and/or
setting equal to nothing in the ExitProc section of Private Sub sendemail_Click(). This includes
Session, db, doc rtitem, and tmp.
Finally, I wasn't able to test your code thoroughly, since I don't have Lotus Notes available.
However, I did verify that the Recipients string was returning a semi-colon delimited list of
names.
Good Luck!
Please let me know how it goes for you.
Tom
PS. Work on improving that database design, so that you can eliminate the ugly union query.
'*******************Begin Code***************************
Option Compare Database
Option Explicit
Private Sub sendemail_Click()
On Error GoTo ProcError
Dim Recipients As String, strBody As String
Dim SendTo As String, attachpath As String
Dim Session As Object, db As Object, doc As Object, rtitem As Object, tmp As Object
'Sets up Notes Session and opens the mail database
Me.Refresh '<---I'm not sure this is necessary
'Set Session = CreateObject("Notes.Notessession")
'Set db = Session.GetDatabase("", "")
'Call db.OPENMAIL
Recipients = BulkEmail()
Exit Sub
'Creates the mail document, sets the body, sendto, and Subject fields
Set doc = db.CreateDocument
Set rtitem = doc.CreateRichTextItem("body")
DoCmd.OutputTo acOutputReport, "Backlog Quality", acFormatRTF, "s:\ECR Temp\Backlog Quality.rtf",
False
strBody = "Please implement the enclosed ECN"
Call rtitem.AppendText(strBody)
Call rtitem.AddNewLine(2)
'Here is the way to attach a file
Set tmp = rtitem.EmbedObject(1454, "", "s:\ECR Temp\Backlog Quality.rtf")
Call doc.replaceitemvalue("SendTo", Recipients)
Call doc.replaceitemvalue("Subject", "NEW ECN")
Call doc.Send(False)
MsgBox ("Message has been sent.")
Set Session = Nothing
ExitProc:
'Cleanup
On Error Resume Next '<-----Add this line
Set Session = Nothing
Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure sendemail_Click..."
Resume ExitProc
End Sub
Function BulkEmail() As String
On Error GoTo ProcError
'Purpose: Return a concatenated string from individual records in a recordset
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strOut As String
Dim lngLen As Long
Const conSEP = ";"
Set db = CurrentDb()
Set qdf = db.QueryDefs("quniAllRecipients")
qdf![[Forms]![frmECN]![ECR Number]] = [Forms]![frmECN]![ECR Number]
Set rs = qdf.OpenRecordset(dbOpenSnapshot)
With rs
Do While Not .EOF
strOut = strOut & !Recipient & conSEP
.MoveNext
Loop
End With
lngLen = Len(strOut) - Len(conSEP)
If lngLen > 0 Then
BulkEmail = Left$(strOut, lngLen)
End If
Debug.Print BulkEmail
ExitProc:
'Cleanup
On Error Resume Next
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
Exit Function
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure BulkEmail..."
Resume ExitProc
End Function
'*********************End Code**********************
______________________________________
Hi Bryan,
I need to head to work now, so it will be at least 10 hours or so before I can reply back. In the
meantime, I suggest reading the following articles:
http://www.datatexcg.com/Downloads/DatabaseDesignTips1997.pdf
http://www.eade.com/AccessSIG/downloads.htm
(See the last download titled "Understanding Normalization")
http://support.microsoft.com/?id=234208
http://support.microsoft.com/?id=289533
Thanks for including the code for Lotus Notes. That may come in handy some day for me.
Tom
______________________________________
Hey Tom,
I am not using SendObject at all. I've included my code so you can see
where I am:
Private Sub sendemail_Click()
Dim Recipients As String, strBody As String
Dim SendTo As String, attachpath As String
Dim Session As Object, db As Object, doc As Object, rtitem As Object, tmp As
Object
'On Error GoTo Err_SendEMail
'Sets up Notes Session and opens the mail database
Me.Refresh
Set Session = CreateObject("Notes.Notessession")
Set db = Session.GetDatabase("", "")
Call db.OPENMAIL
Recipients = "Bryan Daniels"
'Creates the mail document, sets the body, sendto, and Subject fields
Set doc = db.CreateDocument
Set rtitem = doc.CreateRichTextItem("body")
DoCmd.OutputTo acOutputReport, "Backlog Quality", acFormatRTF, "s:\ECR
Temp\Backlog Quality.rtf", False
strBody = "Please implement the enclosed ECN"
Call rtitem.AppendText(strBody)
Call rtitem.AddNewLine(2)
'Here is the way to attach a file
Set tmp = rtitem.EmbedObject(1454, "", "s:\ECR Temp\Backlog Quality.rtf")
Call doc.replaceitemvalue("SendTo", Recipients)
Call doc.replaceitemvalue("Subject", "NEW ECN")
Call doc.Send(False)
MsgBox ("Message has been sent.")
Set Session = Nothing
Exit_SendEMail:
Set Session = Nothing
Set db = Nothing
Exit Sub
Err_SendEMail:
MsgBox Err.Description
Resume Exit_SendEMail
End Sub
You got the ECN right!!
I am a little confused by the improper database design thing. Everything
within each record varies, including the names of the participants. Lotus
Notes allows me to simply use the names and not have to input email
addresses, i.e., FirstName LastName. That was my original logic for not
having to create a separate table. There is no list I'm drawing from, just
using form entries. I've also included my query SQL so you can see how I've
ended up with columns. Am I making any sense at all??!! I really appreciate
your input and improving my understanding!
Thanks again!
SELECT Table1.Originator, Table1.[Engineer Name], Table1.[Manufacturing and
Test Name], Table1.[Master Scheduler Name], Table1.[Procurement Name],
Table1.[P&L Managers Name], Table1.[Quality Name]
FROM Table1
WHERE (((Table1.[ECR Number])=[screen].[Activeform].[ECR Number]));
Tom Wickerath said:
Hi Bryan,
This is not a proper database design. You have a one-to-many (1:M) relationship between ECN
(Engineering Change Notice?
http://www.acronymfinder.com/af-query.asp?String=exact&Acronym=ECN&Find=Find) and participants. A
table should represent a single subject. Your table seems to describe two different subjects:
ECN's and participants. The problem with such a design is that if you need to add an 8th
participant (or reduce the number of participants to 5 or 6, for example), you will need to add
or remove fields in your table, and make the necessary adjustments to your queries, forms, Data
Access Pages (if you use these) and reports. The general rule of thumb is "Adding fields is
expensive; adding records is cheap". Your database design should be such that you can add or
remove similar data (for example, participants and their e-mail addresses) without having to make
design changes. You can likely use a union query with your current design to return the e-mail
addresses as separate records. The results of the union query would be fed into the BulkE-mail
function that I showed earlier. However, that's a lot of extra work to have to go through to
accommodate an improper database design.
Storing ECN attributes in one table and Participant attributes in another table will allow you to
take advantage of the relational characteristics of your data. Are you familiar with creating
relationships between tables, such as 1:M and M:N (many-to-many, which is simply two 1:M with a
linking table)?
I'm never used Lotus Notes. KB article 231797 (
http://support.microsoft.com/?id=231797) includes
the following statement:
"When you use the SendObject method within Microsoft Access, you must have a messaging
application (for example, Microsoft Outlook), that supports the Microsoft Mail Applications
Programming Interface or MAPI."
A quick search of the IBM web site seems to indicate that Notes can be set up to support MAPI:
http://www.developer.ibm.com/tech/faq/individual?oid=2:22171
"This includes using Microsoft Outlook or Exchange, or IBM Lotus Notes using MAPI instead of the
VIM mail interface. Look at the documentation for your mail service to set up the MAPI
interface."
Your initial message seemed to indicate that you had SendObject working correctly with Lotus
Notes, when you manually type in the names in the module. So, it doesn't appear that the problem
is with Notes. What happens if you manually type in a semicolon delimited string of e-mail
addresses, such as "(e-mail address removed); (e-mail address removed); (e-mail address removed)"? I
recommend using a semicolon to delimit your list of names versus a comma.
Other reasons for SendObject failing in Access include the following:
http://support.microsoft.com/?id=260819
Are you trying to enter a delimited list of names, instead of e-mail addresses? If so, do the
names include commas, as in Lastname, Firstname? This KB article might help you out:
http://support.microsoft.com/?id=210309
Good Luck!
Tom
____________________________________
Hey Tom,
I have 7 separate fields to pick up the names of the "participants." Each
of these is "signing" their form. The query I wrote picks up these names.
Different people could sign these depending upon the ECN, so I am picking up
only those names relating to that particular record. So now my query has 7
separate fields, i.e. columns. I have a report based on that record that I
am sending to initiate implementation. It has been an interesting challenge
in that my lotus Notes laughed at my attempt to use SendObject and so I had a
tough job of creating the module. I am going to try and incorporate the the
ideas already generated. Thanks!
____________________________________
Hi Bryan,
If I understand you correctly, you need to create a delimited string of e-mail addresses,
something like this:
(e-mail address removed); (e-mail address removed); (e-mail address removed); etc.
Is this correct?
The part that is troubling me a little is your statement: "The trouble(s) I am having is how to
take my query, which has multiple columns, and convert them into one column."
If you are trying to concatenate e-mail addresses, I certainly hope your query is returning
multiple records (not columns), and that you need to convert them into one record. You need to
take another look at your database design if you are storing e-mail addresses in different fields
(columns) in the table.
Anyway, Allen Browne has the tip that I think you are looking for:
http://groups-beta.google.com/group...4be3100?tvc=1&scrollSave=&&d#f8ef46d604be3100
Tom
____________________________________
I already have my lotus Notes email working to send my report, but I need to
send to recipients based on a query, "ECN Participants." The trouble(s) I am
having is how to take my query, which has multiple columns, and convert them
into one column. I have a recipients = "" line in my module which works if I
manually type in the names in the module, but I need to be able to send to
those who participated in the initial process. Bottom line: I need to
replace the "" with code to send the email to those selected by the query. I
need a miracle here, please!!!!!!
