By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,581 Members | 2,023 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,581 IT Pros & Developers. It's quick & easy.

Access Send Email Issue

100+
P: 178
Here is the code As a whole...

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdEmail_Click()
  2.  
  3.   On Error GoTo EH
  4.  
  5.     Dim strSendTo     As String
  6.     Dim strSubject    As String
  7.     Dim strEMailBody  As String
  8.     Dim MyDB As DAO.Database
  9.     Dim rstEMail As DAO.Recordset
  10.  
  11.     Set MyDB = CurrentDb
  12.     Set rstEMail = MyDB.OpenRecordset("Select * From tblEMail", _
  13.     dbOpenSnapshot, dbOpenForwardOnly)
  14.  
  15.    With rstEMail
  16.             If Not (.BOF And .EOF) Then
  17.                 Call .MoveFirst
  18.                 Do While Not .EOF
  19.                     'Build the Recipients String
  20.                     strSendTo = ""
  21.                     strSendTo = _
  22.                         strSendTo & _
  23.                         IIf(strSendTo = "", _
  24.                             "", _
  25.                             ";") & _
  26.                         !Email
  27.                     Call .MoveNext
  28.                 Loop
  29.             End If
  30.             Call .Close
  31.         End With
  32.  
  33.         Call MyDB.Close
  34.         Set rstEMail = Nothing
  35.         Set MyDB = Nothing
  36.  
  37.     strSubject = "New Part Number Tasks"
  38.     strEMailBody = "Please check for tasks you need to perform"
  39.  
  40.     'Generate and Display the E-Mail
  41.     Call SendAnEmail(olSendTo:=strSendTo, _
  42.                      olSubject:=strSubject, _
  43.                      olEMailBody:=strEMailBody, _
  44.                      olDisplay:=True, _
  45.                      SendAsHTML:=True)
  46.  
  47.     Exit Sub
  48. EH:
  49.     MsgBox "There was an error sending mail!" & vbCrLf & vbCrLf & _
  50.         "Error: " & Err.Number & vbCrLf & _
  51.         "Description: " & Err.Description & vbCrLf & vbCrLf & _
  52.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  53.     Exit Sub
  54.  
  55. End Sub
The area that seems to not work is thes one:

Expand|Select|Wrap|Line Numbers
  1.    Set MyDB = CurrentDb
  2.     Set rstEMail = MyDB.OpenRecordset("Select * From tblEMail", _
  3.     dbOpenSnapshot, dbOpenForwardOnly)
  4.  
  5.    With rstEMail
  6.             If Not (.BOF And .EOF) Then
  7.                 Call .MoveFirst
  8.                 Do While Not .EOF
  9.                     'Build the Recipients String
  10.                     strSendTo = ""
  11.                     strSendTo = _
  12.                         strSendTo & _
  13.                         IIf(strSendTo = "", _
  14.                             "", _
  15.                             ";") & _
  16.                         !Email
  17.                     Call .MoveNext
  18.                 Loop
  19.             End If
  20.             Call .Close
  21.         End With
  22.  
  23.         Call MyDB.Close
  24.         Set rstEMail = Nothing
  25.         Set MyDB = Nothing
It will display the email but with only one address in the To: area. It should have multiple as I have 5 entries in my table.

So my guess is that I have something typed incorrectly that I cannot see. Can someone please look it over and tell me where I went wrong.

Thanks,

Rhino
2 Weeks Ago #1

✓ answered by ADezii

A couple of minor adjustments should do the trick:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rstEMail As DAO.Recordset
  3. Dim strSendTo As String
  4.  
  5. Set MyDB = CurrentDb
  6. Set rstEMail = MyDB.OpenRecordset("Select * From tblEMail", _
  7.                     dbOpenSnapshot, dbOpenForwardOnly)
  8.  
  9. With rstEMail
  10.   If Not (.BOF And .EOF) Then
  11.     Do While Not .EOF
  12.       If Not IsNull(![email]) Then
  13.         strSendTo = strSendTo & !Email & ";"
  14.       End If
  15.         .MoveNext
  16.     Loop
  17.   End If
  18. End With
  19.  
  20. 'Remove Trailing Semi Colon (';')
  21. strSendTo = Left$(strSendTo, Len(strSendTo) - 1)
  22.  
  23. 'Verify String
  24. MsgBox strSendTo
  25.  
  26. rstEMail.Close
  27. Set rstEMail = Nothing
  28. MyDB.Close
  29. Set MyDB = Nothing

Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,668
A couple of minor adjustments should do the trick:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rstEMail As DAO.Recordset
  3. Dim strSendTo As String
  4.  
  5. Set MyDB = CurrentDb
  6. Set rstEMail = MyDB.OpenRecordset("Select * From tblEMail", _
  7.                     dbOpenSnapshot, dbOpenForwardOnly)
  8.  
  9. With rstEMail
  10.   If Not (.BOF And .EOF) Then
  11.     Do While Not .EOF
  12.       If Not IsNull(![email]) Then
  13.         strSendTo = strSendTo & !Email & ";"
  14.       End If
  15.         .MoveNext
  16.     Loop
  17.   End If
  18. End With
  19.  
  20. 'Remove Trailing Semi Colon (';')
  21. strSendTo = Left$(strSendTo, Len(strSendTo) - 1)
  22.  
  23. 'Verify String
  24. MsgBox strSendTo
  25.  
  26. rstEMail.Close
  27. Set rstEMail = Nothing
  28. MyDB.Close
  29. Set MyDB = Nothing
2 Weeks Ago #2

100+
P: 178
Worked perfectly, apparently I had to much in the code

Thanks a million ADezii
2 Weeks Ago #3

ADezii
Expert 5K+
P: 8,668
You are quite welcome.
2 Weeks Ago #4

Post your reply

Sign in to post your reply or Sign up for a free account.