Sending Email

[LarryB]

Good morning,

I have a form button containing the following code to send an email to multiple recipients. It currently reads from a table (tblEmailList) with one field (EMail). The code reads all email addresses using a recordset

Dim stSubject As String
Dim strEmailAddress
Dim strEmailMessage
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("tblEmailList")
Do Until rst.EOF
strEmailAddress = strEmailAddress & rst("EMail") & ","
rst.MoveNext
Loop

stSubject = "Test Subject:- "
strEmailMessage = "Test:"
strEmailAddress = Left(strEmailAddress, Len(strEmailAddress) - 1)
DoCmd.SendObject , , , strEmailAddress, , , stSubject, strEmailMessage

*This code works fine as it is. However based on whoever is sending the email, that is, an admin sends an email to all supervisors or a supervisor sends the email to a user. Rather then referring to different email tables, I want to try and use the one table.

In the tblEmailList, I have added an additional field called Type. There are two different types in this data field - User and Super

Is there any way I can add to the code above so that it only pulls in email addresses that have Type "Super"??

Many thanks

Larry B

 

[JANR]

Instead of opening the hole table just open a selected recordset.

Set rst = CurrentDb.OpenRecordset("Select EMail From tblEmailList Where [Type] = 'Super'")

or you can pass on to the Sub/Function the parameter so you don't have to have 2 different sub/function.

Function MyEmail(SendType As String)
...
Set rst = CurrentDb.OpenRecordset("Select EMail From tblEmailList Where [Type] ='" & SendType & '")
....

BTW Type is a reserved word so I would choose another name for the field ex eType

JR

 

[LarryB]

Thank you JR,

I have decided to use your first suggestion and use the Select and Where statement and this works perfectly

I have also changed the field name.

Thanks again

LarryB

 

[LarryB]

In addition to the send email function I am trying to use a value of a combo box to look up an email address based on the email table.

I have added the following code to the code in my first post

Dim strUser As String

struser = Me.cboEmailSelect.column(1)
Set rst = CurrentDb.OpenRecordset("Select EMail From tblEmailList Where [uName] = 'strUser'")

If I do not use the 'strUser' in the set rst line, and I use a name in the table, such as "Larry", the email will open with the correct email address.

However, with the code as it is the email address is blank. I have output the struser string to a text box and teh value is what I selected, so that is fine. Any ideas?? Code in full

Dim stSubject As String
Dim rst As DAO.Recordset
Dim strEmailAddress
Dim strEmailMessage
Dim strUser As String

strUser = Me.cboEmailSelect.Column(1)

Set rst = CurrentDb.OpenRecordset("Select EMail From tblEmailList Where [uName] = 'strUser'")

Do Until rst.EOF
strEmailAddress = strEmailAddress & rst("EMail") & ","
rst.MoveNext
Loop
stSubject = " Test:- "
strEmailMessage = "Test:"
strEmailAddress = Left(strEmailAddress, Len(strEmailAddress) - 1)
DoCmd.SendObject , , strEmailAddress, , , stSubject, strEmailMessage
rst.Close
Set rst = Nothing
Me!cboEmailSelect = Null

 

[JANR]

Try:

Set rst = CurrentDb.OpenRecordset("Select EMail From tblEmailList Where [uName] ='" & strUser & "'")

You need refrence the variable strUser outside of the sql-string.

JR

 

[LarryB]

Thanks again JR.

That code works as far as the open mail. strEmailAddress is being given the correct data, ie the email address based on combo box selection as I can ouput the strEmailAddress data to a text box

As soon as the email is opened there is nothing in the "To" box.

I've used this code before for multiple email procedures. Is there anything wrong with these lines?

strEmailAddress = Left(strEmailAddress, Len(strEmailAddress) - 1)
DoCmd.SendObject , , strEmailAddress, , , stSubject, strEmailMessage

 

[JANR]

Add another comma.

DoCmd.SendObject , , [COLOR=red][B],[/B][/COLOR] strEmailAddress, , , stSubject, strEmailMessage

JR