Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Apr 2014
    Posts
    89

    Unanswered: Code Help for access to send email

    Someone provided me this code. Its mission is to take all the email address out of a table and populate them into a outlook email.
    IT works but it only pulls out the first email and for some reason adds it 2 times. Not being a coder or knowing anything about code I could use some help on what would need to be changed to make it work right. Unfortunately the person who provided it is not longer around to ask.

    Option Compare Database

    Private Sub Command1_Click()
    Dim myEmailString As String
    Dim x As Integer
    Dim db As Database
    Dim rstEmails As Recordset

    Set db = CurrentDb()
    Set rstEmails = db.OpenRecordset("SELECT * FROM myEmails;", dbOpenSnapshot)

    For x = 1 To rstEmails.RecordCount + 1
    myEmailString = myEmailString & "; " & rstEmails.Fields("Email").Value
    Next

    SendEmailFinal myEmailString, " ", " ", " "

    End Sub

    Sub SendEmailFinal(EmailTo As String, EmailCC As String, EmailSubject As String, EmailBody As String)

    Dim olApp As Object
    Dim olItem As Object

    Set olApp = CreateObject("Outlook.Application")
    Set olItem = olApp.createitem(0)

    With olItem
    .To = EmailTo
    .CC = EmailCC
    .Subject = EmailSubject
    .BodyFormat = 2
    .HTMLBody = EmailBody
    End With
    SendEmail:
    olItem.Display 'display the email instead of sending it.
    Set olApp = Nothing
    Set olItem = Nothing
    End Sub

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You're not moving the recordset to the next record. Replace this:

    For x = 1 To rstEmails.RecordCount + 1
    myEmailString = myEmailString & "; " & rstEmails.Fields("Email").Value
    Next

    with this:

    Do While Not rstEmails.EOF
    myEmailString = myEmailString & "; " & rstEmails.Fields("Email").Value
    rstEmails.MoveNext
    Loop

    and give it a shot.
    Paul

  3. #3
    Join Date
    Apr 2014
    Posts
    89

    Thanks

    Quote Originally Posted by pbaldy View Post
    You're not moving the recordset to the next record. Replace this:

    For x = 1 To rstEmails.RecordCount + 1
    myEmailString = myEmailString & "; " & rstEmails.Fields("Email").Value
    Next

    with this:

    Do While Not rstEmails.EOF
    myEmailString = myEmailString & "; " & rstEmails.Fields("Email").Value
    rstEmails.MoveNext
    Loop

    and give it a shot.

    That worked perfect Paul Thanks..

  4. #4
    Join Date
    Apr 2014
    Posts
    89

    one more question

    Paul How do I get my email to auto populate in the to field?

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I'm not clear what you mean. Aren't you using that variable?
    Paul

  6. #6
    Join Date
    Apr 2014
    Posts
    89
    No due to the amount of personal emails. The emails that this pulls goes into the BCC field.. So I was wondering how to get my personal email to go in the To Field all the time as part of this.. I tried adding it to the To code but that did not work.. Don't know enough

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You mean like:

    .To = "You@Whatever.com"
    Paul

  8. #8
    Join Date
    Apr 2014
    Posts
    89
    yep awesome that was the issue I did not have Parenthesizes in there I assume you could do that for subject as well then if you wanted a common subject line.

    Thanks again really appreciate your help

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Yes, should work for subject too (I assume you mean quotes).
    Paul

  10. #10
    Join Date
    Apr 2014
    Posts
    89
    Yep. Thanks again

  11. #11
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Happy to help!
    Paul

  12. #12
    Join Date
    Apr 2014
    Posts
    89
    Hi Paul,
    I have this code to open email.. how do I get it to run a query before it. I have a query to pull the admin into a table first called AdminEmail which then creates a table called admin.. So before it pulls the names for email I would like it to create that table thru the query to make sure it is uptodate

    Private Sub EmailAdmin_Click()

    Dim myEmailString As String
    Dim x As Integer
    Dim db As Database
    Dim rstEmails As Recordset

    Set db = CurrentDb()
    Set rstEmails = db.OpenRecordset("SELECT * FROM admin;", dbOpenSnapshot)

    Do While Not rstEmails.EOF
    myEmailString = myEmailString & "; " & rstEmails.Fields("Email").Value
    rstEmails.MoveNext
    Loop

    SendEmailFinaladmin myEmailString, " ", " ", " "

    End Sub

    Sub SendEmailFinaladmin(EmailTo As String, EmailCC As String, EmailSubject As String, EmailBody As String)

    Dim olApp As Object
    Dim olItem As Object

    Set olApp = CreateObject("Outlook.Application")
    Set olItem = olApp.createitem(0)

    With olItem
    .To = EmailTo
    .CC = EmailCC
    .BCC = EmailBCC
    .Subject = "Union"
    .BodyFormat = 2
    .HTMLBody = EmailBody
    End With
    SendEmail:
    olItem.Display 'display the email instead of sending it.
    Set olApp = Nothing
    Set olItem = Nothing
    End Sub
    Last edited by dlafko; 02-01-15 at 12:10.

  13. #13
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I'm not sure it's appropriate to create a table; typically you'd just pull the names with a query. If I needed the values in a table, I'd empty and repopulate an existing table used for that purpose. For either, you can use the Execute method or DoCmd.OpenQuery.
    Paul

  14. #14
    Join Date
    Apr 2014
    Posts
    89
    OK that makes sense I don't need a table either just did not know how to do it other wise so the query is adminemail. Do I just at that at the end of the do command?

  15. #15
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    It sounds like you want to open the recordset on the query:

    Set rstEmails = db.OpenRecordset("SELECT * FROM adminemail;", dbOpenSnapshot)
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •