Emailing Recordset Problem 2

[GKWilly]

Hi folks,

I have a module that calls a recordset and emails to the relevant person. The problem I have is that I can't manage to get all the data into the message body, the variable 'strmessage' fails to grab the data.

If anyone can see what's wrong with my code any assistance/advice would be muchly appreciated.


Function fSendEmail()
On Error GoTo fSendMail_Err

Dim rs As DAO.Recordset
Dim strEmailTo As String
Dim strForename As String
Dim strusername As String
Dim strpassword As String
Dim strtext1 As String
Dim strtext2 As String
Dim strtext3 As String
Dim strmessage As String

Set rs = CurrentDb.OpenRecordset("SELECT * FROM StaffPasswords where emailed = 0")
rs.MoveFirst

While Not rs.EOF

If strEmailTo <> "" Then
strEmailTo = strEmailTo & "; " & rs!EMAIL
Else
strEmailTo = rs!EMAIL
End If

If strForename <> "" Then
strForename = strForename & "; " & rs![Employee First Name]
Else
strForename = rs![Employee First Name]
End If

If strusername <> "" Then
strusername = strusername & "; " & rs!UserName
Else
strusername = rs!UserName
End If

If strpassword <> "" Then
strpassword = strpassword & ": " & rs!Password
Else
strpassword = rs!Password
End If

If strmessage <> "" Then
strmessage = "Dear " & rs![Employee First Name] & ", Your username is " & rs!UserName & " and your password is " & rs!Password
Else
End If

rs.MoveNext

Wend

DoCmd.SendObject , "", "", strEmailTo, "", "", "IMPORTANT-IRIS/Unit-e User Account", strmessage, True, """"""

fSendMail_Exit:
Exit Function

fSendMail_Err:
MsgBox Err.Number & " " & Err.Description, vbOKOnly + vbExclamation
Resume fSendMail_Exit:
End Function

 

[HarleyQuinn]

You will only ever get the last record that is retrieved from the query as you keep overwriting the strmessage variable everytime you loop through the recordset. If you want to do it how you are currently i would send the e-mail before the wend in your code and then just add the the recordset values to the variables rather than concatenating them. Therefore sending each person an individual e-mail.

Hope this helps

Harleyquinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[PHV]

If strmessage <> "" Then strmessage = strmessage & vbNewLine
strmessage = strmessage & "Dear " & rs![Employee First Name] & ", Your username is " & rs!UserName & " and your password is " & rs!Password

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[HarleyQuinn]

One question springs to my mind though.
Why would you want to send out a bulk e-mail telling people of usernames and passwords?
Surely usernames and passwrods are for security purposes, telling all the people in the e-mail each others seems to completely bypass the security side of it??

Harleyquinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[GKWilly]

Hi Harleyquinn,

What I'm trying to do is send out the user details to the individual user when the emailed flag is zero, I'm only testing this on one record so far so I may have made a mess of it.
Anymore guidance you can give would be great.
Thanks

 

[HarleyQuinn]

If you try the suggestion in my first post that should do what you require.

Cheers

Harleyquinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[PHV]

Set rs = CurrentDb.OpenRecordset("SELECT * FROM StaffPasswords where emailed = 0")
rs.MoveFirst
While Not rs.EOF
strmessage = "Dear " & rs![Employee First Name] & ", Your username is " & rs!UserName & " and your password is " & rs!Password
DoCmd.SendObject , "", "", rs!EMAIL, "", "", "IMPORTANT-IRIS/Unit-e User Account", strmessage, True
rs.MoveNext
Wend

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[GKWilly]

Thanks Harleyquinn, works a treat.
Could I ask you one more question though?
How would I update the emailed field in the recordset to 1 so I do't end up looping through?

 

[HarleyQuinn]

Once you have used the rs.movenext you won't loop back through to that record in the rs again (unless you do an rs.movefirst to go to the start again).
After you have sent all the e-mails do an update query to update the database to set every record in the table with a '0' in emailed to have a '1'.

That should sort it for you.

Harleyquinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[GKWilly]

Sorry to be a pain and take up your time but the problem I'm having now is that after the first email is sent and it moves to the next record, the email it creates is the same as the first one but with the first and 2nd emailto, ie, the first person gets their correct email then the 1st and 2nd get the s1st email etc.
Any thoughts?

Function fSendEmail()
On Error GoTo fSendMail_Err

Dim rs As DAO.Recordset
Dim strEmailTo As String
Dim strmessage As String

Set rs = CurrentDb.OpenRecordset("SELECT * FROM StaffPasswords where emailed = 0")
'rs.MoveFirst

While Not rs.EOF

If strEmailTo <> "" Then
strEmailTo = strEmailTo & "; " & rs!EMAIL
Else
strEmailTo = rs!EMAIL
End If


If strmessage <> "" Then
strmessage = strmessage & vbNewLine
Else
strmessage = strmessage & "Dear " & rs![Employee First Name] & "," & vbNewLine & " A user account has been created for you and your login details are as follows:" & vbNewLine & "Username: " & rs!UserName & vbNewLine & "Password: " & rs!Password
End If

'rs.MoveNext

' Wend

DoCmd.SendObject , "", "", strEmailTo, "", "", "IMPORTANT-IRIS/Unit-e User Account", strmessage, True, """"""

rs.MoveNext

Wend

fSendMail_Exit:
Exit Function

fSendMail_Err:
MsgBox Err.Number & " " & Err.Description, vbOKOnly + vbExclamation
Resume fSendMail_Exit:
End Function

 

[HarleyQuinn]

What you are doing is that if the string variables are not "" then you are concatenating the values into the string. PHV demonstrated exactly what I meant in my first post by just using the current values in the recordset.

If strEmailTo <> "" Then
             strEmailTo = strEmailTo & "; " & rs!EMAIL
            Else
             strEmailTo = rs!EMAIL
             End If

The above code you use chcks to see if the variable is "". If it is it assigns the rs!EMAIL value to it. If it is not null it adds the value to the current value it has in the string.
Have a look at PHV's example and that should do it for you perfectly.

Hope this helps

Harleyquinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[GKWilly]

Thanks to both PHV and Harleyquinn
Stars for you both )
Your help is very much appreciated guys

 

[HarleyQuinn]

I'm glad we could help

Harleyquinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.