Dlookup - To access and send multiple email addresses

[Swordfish]

Good Everyone,

My question of the day: I am using Lotus Notes as a mail client and
therefore am using it to send email in Access. Per the coding below when I
select a name in the combo box named Production Assigned, it creates a new
email and enters the email address in the To: field. The email addresses are
located in a table called Benefits Employee.

How can I add to the coding below to include another combo box named QA, to
send in the same email, on the To: field. The names from the drop down are
located in the Table QAassigned (row source coding below). The e-mail
addresses for QA are also located in the Benefits Employee.

Thank you for any assistance

QA row source: SELECT tblQAassigned.QAassignedID, tblQAassigned.LastName &
", " & [FirstName] AS Expr1, tblQAassigned.FirstName FROM tblQAassigned ORDER
BY tblQAassigned.LastName & ", " & [FirstName] WITH OWNERACCESS OPTION;



Private Sub SendEmail_Click()
Dim SendTo As String
SendTo = DLookup("[EmailAddress]", "tblBenefitsEmployee", "[EmployeeID] =
" & Me.Productionassignedname)
Call ComposeNotesMemo(SendTo, "ProductionAssigned and QA Assigned", "The
following DataTRAK NUMBER has been assigned to you:" & Me.Datatrak_Number)
End Sub

Public Sub ComposeNotesMemo(SendTo As String, Subject As String, body As
String)
Dim sess As Object, ws As Object, uidoc As Object
Dim mailServer As String, mailFile As String
Set sess = CreateObject("Notes.NotesSession")
Set ws = CreateObject("Notes.NotesUiWorkspace")
mailServer = sess.GetEnvironmentString("MailServer", True)
mailFile = sess.GetEnvironmentString("MailFile", True)
Set uidoc = ws.ComposeDocument(mailServer, mailFile, "Memo")
Call uidoc.FieldSetText("EnterSendTo", SendTo)
Call uidoc.FieldSetText("Subject", Subject)
uidoc.GotoField ("Body")
uidoc.InsertText (body)
AppActivate (uidoc.WindowTitle)
End Sub

 

[Graham Mandeno]

Hi Swordfish

Do you want the message to go to *both* recipients - ProductionAssigned and
QAassigned?

Assuming Lotus Notes accepts multiple addresses separated by semicolons in
the "EnterSendTo" string, then you will need to:

1. Retrieve the emain address of the person selected in the cboQAassigned
combo box (I can't tell you how to do this because I do not know the
structure of your tblQAassigned table, but you should be able to figure it
out.)

2. Append this value to the SendTo string that you already have.

It will look something like this:

Dim SendTo As String
SendTo = DLookup("[EmailAddress]", "tblBenefitsEmployee", _
"[EmployeeID] = " & Me.Productionassignedname)
If not IsNull( cboQAassigned ) Then
SendTo = SendTo & ";" & DLookup("[EmailAddress]", "tblQAAssigned", _
"[QAassignedID] = " & Me.cboQAassigned )
End If

Rather than using DLookup, a better idea would be to add the email address
as a hidden column in your combo box(es) [I assume Productionassignedname is
also a combo box]

Say you RowSource for cboQAassigned is:
SELECT QAassignedID, LastName & ", " & [FirstName] AS FullName,
EmailAddress FROM tblQAassigned ORDER BY LastName, FirstName
WITH OWNERACCESS OPTION;

If you set ColumnCount to 3 then the EmailAddress will be available as
..Column(2), the third column in the combo box.

Do a similar thing with Productionassignedname and then you can lose the
DLookups:
Dim SendTo As String
SendTo = Me.Productionassignedname.Column(2)
If not IsNull( cboQAassigned ) Then
SendTo = SendTo & ";" & Me.cboQAassigned.Column(2)
End If

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Good Everyone,

My question of the day: I am using Lotus Notes as a mail client and
therefore am using it to send email in Access. Per the coding below when
I
select a name in the combo box named Production Assigned, it creates a new
email and enters the email address in the To: field. The email addresses
are
located in a table called Benefits Employee.

How can I add to the coding below to include another combo box named QA,
to
send in the same email, on the To: field. The names from the drop down are
located in the Table QAassigned (row source coding below). The e-mail
addresses for QA are also located in the Benefits Employee.

Thank you for any assistance

QA row source: SELECT tblQAassigned.QAassignedID, tblQAassigned.LastName
&
", " & [FirstName] AS Expr1, tblQAassigned.FirstName FROM tblQAassigned
ORDER
BY tblQAassigned.LastName & ", " & [FirstName] WITH OWNERACCESS OPTION;



Private Sub SendEmail_Click()
Dim SendTo As String
SendTo = DLookup("[EmailAddress]", "tblBenefitsEmployee", "[EmployeeID] =
" & Me.Productionassignedname)
Call ComposeNotesMemo(SendTo, "ProductionAssigned and QA Assigned", "The
following DataTRAK NUMBER has been assigned to you:" & Me.Datatrak_Number)
End Sub

Public Sub ComposeNotesMemo(SendTo As String, Subject As String, body As
String)
Dim sess As Object, ws As Object, uidoc As Object
Dim mailServer As String, mailFile As String
Set sess = CreateObject("Notes.NotesSession")
Set ws = CreateObject("Notes.NotesUiWorkspace")
mailServer = sess.GetEnvironmentString("MailServer", True)
mailFile = sess.GetEnvironmentString("MailFile", True)
Set uidoc = ws.ComposeDocument(mailServer, mailFile, "Memo")
Call uidoc.FieldSetText("EnterSendTo", SendTo)
Call uidoc.FieldSetText("Subject", Subject)
uidoc.GotoField ("Body")
uidoc.InsertText (body)
AppActivate (uidoc.WindowTitle)
End Sub

 

[Swordfish]

Thank you for the quick response.

In regards to structure, the email addresses for the names in the combo
boxes reside only in the BenefitsEmployee table.

For ProductionAssigned the email for the selected name from the dropdown in
the combo box comes from the BenefitsEmployee table.

The row source is: SELECT tblBenefitsEmployee.EmployeeID,
tblBenefitsEmployee.LastName & ", " & [FirstName] AS Expr1 FROM
tblBenefitsEmployee ORDER BY tblBenefitsEmployee.LastName & ", " &
[FirstName], tblBenefitsEmployee.FirstName WITH OWNERACCESS OPTION;


Would you suggest I place in design for ProductionAssigned and QA Assigned a
colomn for email and populate the table?


Also, for Call ComposeNotesMemo(SendTo, "ProductionAssigned and QA
Assigned", "The following DataTRAK NUMBER has been assigned to you:" &
Me.Datatrak_Number). How would I add spacing between "The following
DataTRAK NUMBER has been assigned to you: " (spacing here) &
Me.Datatrak_Number (Example: The following DataTRAK Number has been assigned
to you: 55555)

Thank you for your assistance.

Hi Swordfish

Do you want the message to go to *both* recipients - ProductionAssigned and
QAassigned?

Assuming Lotus Notes accepts multiple addresses separated by semicolons in
the "EnterSendTo" string, then you will need to:

1. Retrieve the emain address of the person selected in the cboQAassigned
combo box (I can't tell you how to do this because I do not know the
structure of your tblQAassigned table, but you should be able to figure it
out.)

2. Append this value to the SendTo string that you already have.

It will look something like this:

Dim SendTo As String
SendTo = DLookup("[EmailAddress]", "tblBenefitsEmployee", _
"[EmployeeID] = " & Me.Productionassignedname)
If not IsNull( cboQAassigned ) Then
SendTo = SendTo & ";" & DLookup("[EmailAddress]", "tblQAAssigned", _
"[QAassignedID] = " & Me.cboQAassigned )
End If

Rather than using DLookup, a better idea would be to add the email address
as a hidden column in your combo box(es) [I assume Productionassignedname is
also a combo box]

Say you RowSource for cboQAassigned is:
SELECT QAassignedID, LastName & ", " & [FirstName] AS FullName,
EmailAddress FROM tblQAassigned ORDER BY LastName, FirstName
WITH OWNERACCESS OPTION;

If you set ColumnCount to 3 then the EmailAddress will be available as
..Column(2), the third column in the combo box.

Do a similar thing with Productionassignedname and then you can lose the
DLookups:
Dim SendTo As String
SendTo = Me.Productionassignedname.Column(2)
If not IsNull( cboQAassigned ) Then
SendTo = SendTo & ";" & Me.cboQAassigned.Column(2)
End If

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Good Everyone,

My question of the day: I am using Lotus Notes as a mail client and
therefore am using it to send email in Access. Per the coding below when
I
select a name in the combo box named Production Assigned, it creates a new
email and enters the email address in the To: field. The email addresses
are
located in a table called Benefits Employee.

How can I add to the coding below to include another combo box named QA,
to
send in the same email, on the To: field. The names from the drop down are
located in the Table QAassigned (row source coding below). The e-mail
addresses for QA are also located in the Benefits Employee.

Thank you for any assistance

QA row source: SELECT tblQAassigned.QAassignedID, tblQAassigned.LastName
&
", " & [FirstName] AS Expr1, tblQAassigned.FirstName FROM tblQAassigned
ORDER
BY tblQAassigned.LastName & ", " & [FirstName] WITH OWNERACCESS OPTION;



Private Sub SendEmail_Click()
Dim SendTo As String
SendTo = DLookup("[EmailAddress]", "tblBenefitsEmployee", "[EmployeeID] =
" & Me.Productionassignedname)
Call ComposeNotesMemo(SendTo, "ProductionAssigned and QA Assigned", "The
following DataTRAK NUMBER has been assigned to you:" & Me.Datatrak_Number)
End Sub

Public Sub ComposeNotesMemo(SendTo As String, Subject As String, body As
String)
Dim sess As Object, ws As Object, uidoc As Object
Dim mailServer As String, mailFile As String
Set sess = CreateObject("Notes.NotesSession")
Set ws = CreateObject("Notes.NotesUiWorkspace")
mailServer = sess.GetEnvironmentString("MailServer", True)
mailFile = sess.GetEnvironmentString("MailFile", True)
Set uidoc = ws.ComposeDocument(mailServer, mailFile, "Memo")
Call uidoc.FieldSetText("EnterSendTo", SendTo)
Call uidoc.FieldSetText("Subject", Subject)
uidoc.GotoField ("Body")
uidoc.InsertText (body)
AppActivate (uidoc.WindowTitle)
End Sub

 

[Graham Mandeno]

Hi Swordfish

Would you suggest I place in design for ProductionAssigned and QA Assigned
a
colomn for email and populate the table?

You don't need to "populate the table" (at least not for ProductionAssigned)
because the field is already there in the table. You just need to add it to
youe combo box.

Set the RowSource to:
SELECT EmployeeID, LastName & ", " & FirstName AS EmpName,
EmailAddress FROM tblBenefitsEmployee
ORDER BY LastName, FirstName
WITH OWNERACCESS OPTION;

Set these other properties for the combo box also:

ColumnCount: 3
ColumnWidths: 0;;0

This ColumnWidths setting hides the first and third columns by setting their
widths to 0, and allows the second column to use all the available space by
not specifying any width.

For your other combo box (QAassigned) I don't know where you can get the
email address from because you have not described the structure of the
tblQAassigned table. Does it have an EmailAddress field? If so then
include it in the RoeSource in the same way.

For adding spaces, simply insert them in the string after the colon:

"The following DataTRAK NUMBER has been assigned to you: " _
& Me.Datatrak_Number

will insert five spaces before the number.

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thank you for the quick response.

In regards to structure, the email addresses for the names in the combo
boxes reside only in the BenefitsEmployee table.

For ProductionAssigned the email for the selected name from the dropdown
in
the combo box comes from the BenefitsEmployee table.

The row source is: SELECT tblBenefitsEmployee.EmployeeID,
tblBenefitsEmployee.LastName & ", " & [FirstName] AS Expr1 FROM
tblBenefitsEmployee ORDER BY tblBenefitsEmployee.LastName & ", " &
[FirstName], tblBenefitsEmployee.FirstName WITH OWNERACCESS OPTION;


Would you suggest I place in design for ProductionAssigned and QA Assigned
a
colomn for email and populate the table?


Also, for Call ComposeNotesMemo(SendTo, "ProductionAssigned and QA
Assigned", "The following DataTRAK NUMBER has been assigned to you:" &
Me.Datatrak_Number). How would I add spacing between "The following
DataTRAK NUMBER has been assigned to you: " (spacing here) &
Me.Datatrak_Number (Example: The following DataTRAK Number has been
assigned
to you: 55555)

Thank you for your assistance.

Hi Swordfish

Do you want the message to go to *both* recipients - ProductionAssigned
and
QAassigned?

Assuming Lotus Notes accepts multiple addresses separated by semicolons
in
the "EnterSendTo" string, then you will need to:

1. Retrieve the emain address of the person selected in the cboQAassigned
combo box (I can't tell you how to do this because I do not know the
structure of your tblQAassigned table, but you should be able to figure
it
out.)

2. Append this value to the SendTo string that you already have.

It will look something like this:

Dim SendTo As String
SendTo = DLookup("[EmailAddress]", "tblBenefitsEmployee", _
"[EmployeeID] = " & Me.Productionassignedname)
If not IsNull( cboQAassigned ) Then
SendTo = SendTo & ";" & DLookup("[EmailAddress]", "tblQAAssigned", _
"[QAassignedID] = " & Me.cboQAassigned )
End If

Rather than using DLookup, a better idea would be to add the email
address
as a hidden column in your combo box(es) [I assume Productionassignedname
is
also a combo box]

Say you RowSource for cboQAassigned is:
SELECT QAassignedID, LastName & ", " & [FirstName] AS FullName,
EmailAddress FROM tblQAassigned ORDER BY LastName, FirstName
WITH OWNERACCESS OPTION;

If you set ColumnCount to 3 then the EmailAddress will be available as
..Column(2), the third column in the combo box.

Do a similar thing with Productionassignedname and then you can lose the
DLookups:
Dim SendTo As String
SendTo = Me.Productionassignedname.Column(2)
If not IsNull( cboQAassigned ) Then
SendTo = SendTo & ";" & Me.cboQAassigned.Column(2)
End If

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Good Everyone,

My question of the day: I am using Lotus Notes as a mail client and
therefore am using it to send email in Access. Per the coding below
when
I
select a name in the combo box named Production Assigned, it creates a
new
email and enters the email address in the To: field. The email
addresses
are
located in a table called Benefits Employee.

How can I add to the coding below to include another combo box named
QA,
to
send in the same email, on the To: field. The names from the drop down
are
located in the Table QAassigned (row source coding below). The e-mail
addresses for QA are also located in the Benefits Employee.

Thank you for any assistance

QA row source: SELECT tblQAassigned.QAassignedID,
tblQAassigned.LastName
&
", " & [FirstName] AS Expr1, tblQAassigned.FirstName FROM tblQAassigned
ORDER
BY tblQAassigned.LastName & ", " & [FirstName] WITH OWNERACCESS OPTION;



Private Sub SendEmail_Click()
Dim SendTo As String
SendTo = DLookup("[EmailAddress]", "tblBenefitsEmployee",
"[EmployeeID] =
" & Me.Productionassignedname)
Call ComposeNotesMemo(SendTo, "ProductionAssigned and QA Assigned",
"The
following DataTRAK NUMBER has been assigned to you:" &
Me.Datatrak_Number)
End Sub

Public Sub ComposeNotesMemo(SendTo As String, Subject As String, body
As
String)
Dim sess As Object, ws As Object, uidoc As Object
Dim mailServer As String, mailFile As String
Set sess = CreateObject("Notes.NotesSession")
Set ws = CreateObject("Notes.NotesUiWorkspace")
mailServer = sess.GetEnvironmentString("MailServer", True)
mailFile = sess.GetEnvironmentString("MailFile", True)
Set uidoc = ws.ComposeDocument(mailServer, mailFile, "Memo")
Call uidoc.FieldSetText("EnterSendTo", SendTo)
Call uidoc.FieldSetText("Subject", Subject)
uidoc.GotoField ("Body")
uidoc.InsertText (body)
AppActivate (uidoc.WindowTitle)
End Sub