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

E-mail with Access

100+
P: 185
I copied and pasted code I currently use with no issue, but I'm having an issue now. I have the following code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnGroupEmail_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 strCCLine     As String
  9.     Dim strBCCLine    As String
  10.     Dim strOnBehalfOf As String
  11.     Dim MyDB          As DAO.Database
  12.     Dim rstEMail      As DAO.Recordset
  13.     Dim strEMail      As String
  14.  
  15.     DoCmd.SetWarnings False
  16.     DoCmd.OpenQuery "qryEmail", acViewNormal
  17.     DoCmd.SetWarnings True
  18.  
  19. Set MyDB = CurrentDb
  20.  
  21. Set rstEMail = MyDB.OpenRecordset("Select * From tblEMail", _
  22.     dbOpenSnapshot, dbOpenForwardOnly)
  23.  
  24.    With rstEMail
  25.             If Not (.BOF And .EOF) Then
  26.                 Call .MoveFirst
  27.                 Do While Not .EOF
  28.                     'Build the Recipients String
  29.                     strSendTo = ""
  30.                     strSendTo = _
  31.                         strSendTo & _
  32.                         IIf(strSendTo = "", _
  33.                             "", _
  34.                             ";") & !Email
  35.                   Call .MoveNext
  36.                 Loop
  37.             End If
  38.             Call .Close
  39.         End With
  40.  
  41.         Call MyDB.Close
  42.         Set rstEMail = Nothing
  43.         Set MyDB = Nothing
  44.  
  45.     'Generate and Display the E-Mail
  46.  
  47.         Call SendAnEmail(olSendTo:=strSendTo, _
  48.                      olSubject:=strSubject, _
  49.                      olEMailBody:=strEMailBody, _
  50.                      olDisplay:=True, _
  51.                      SendAsHTML:=True)
  52.  
  53.     Exit Sub
  54. EH:
  55.     MsgBox "There was an error sending mail!" & vbCrLf & vbCrLf & _
  56.         "Error: " & Err.Number & vbCrLf & _
  57.         "Description: " & Err.Description & vbCrLf & vbCrLf & _
  58.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  59.     Exit Sub
  60.  
  61. End Sub
My issue is its not building the sting correctly in the loop. Currently I only have 2 email address' in my table, but it is only putting in one email address. I'm not getting any errors, so I feel like I'm missing something in the loop.

Can anyone see something I'm missing?
Feb 18 '20 #1

✓ answered by twinnyfo

You are setting strSendTo to "" every new email address. Lines 27-36 should be:

Expand|Select|Wrap|Line Numbers
  1.                 strSendTo = ""
  2.                 Do While Not .EOF
  3.                     'Build the Recipients String
  4.                     strSendTo = _
  5.                         strSendTo & _
  6.                         IIf(strSendTo = "", _
  7.                             "", _
  8.                             ";") & !Email
  9.                   Call .MoveNext
  10.                 Loop
Hope this hepps!

Share this Question
Share on Google+
3 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,394
You are setting strSendTo to "" every new email address. Lines 27-36 should be:

Expand|Select|Wrap|Line Numbers
  1.                 strSendTo = ""
  2.                 Do While Not .EOF
  3.                     'Build the Recipients String
  4.                     strSendTo = _
  5.                         strSendTo & _
  6.                         IIf(strSendTo = "", _
  7.                             "", _
  8.                             ";") & !Email
  9.                   Call .MoveNext
  10.                 Loop
Hope this hepps!
Feb 18 '20 #2

100+
P: 185
Good fined Twinny, I don't think I would have ever caught that.

Thanks a million
Feb 18 '20 #3

twinnyfo
Expert Mod 2.5K+
P: 3,394
Glad I could be of service.

Let us know if we can hepp in any other way!
Feb 18 '20 #4

Post your reply

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