Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Send e-mail to all (2003)

    One of my forms has a command button E-mail that launches an Outlook e-mail message already addressed to the e-dress showing for the current record. I built this with nothing more than the little wizard for creating command buttons. The code:

    <font face="Georgia">Private Sub cmdSendEmail_Click()
    Dim strEmailAddress As String
    strEmailAddress = "mailto:" & Me![Participant_Email]
    Application.FollowHyperlink strEmailAddress, , True
    End Sub</font face=georgia>

    It works exactly like I wanted at the time I built it. Now I need something more: I want a command button that will launch a single Outlook e-mail message addressed to all 43 people in this database. It seems to my amateur brain that the code for such a button would be not very different from the above, but I don't have a clue how to write it. I don't need to send any objects from the database (a report, for example). I just need a way to quickly address a message to a group of people. I'll greatly appreciate any help. Thanx!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Send e-mail to all (2003)

    You could use code like the following. You need to have a reference to the Microsoft DAO 3.6 Object Library in Tools | References.
    <code>
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strEmailAddress As String

    Set dbs = CurrentDb
    ' Replace tblSomething with the name of the table or query containing the e-mail addresses
    Set rst = dbs.OpenRecordset("tblSomething", dbOpenDynaset)
    ' Loop through records
    Do While Not rst.EOF
    strEmailAddress = strEmailAddress & ";" & rst!Participant_Email
    rst.MoveNext
    Loop

    rst.Close
    Set rst = Nothing
    Set dbs = Nothing

    If Not strEmailAddress = "" Then
    strEmailAddress = "mailto:" & Mid(strEmailAddress, 2)
    Application.FollowHyperlink strEmailAddress, , True
    End If
    </code>
    The code concatenates the e-mail addresses separated by semicolons.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Send e-mail to all (2003)

    The code I posted was supposed to go into Private Sub cmdSendEmail_Click() because that's what you asked about.

    Your link doesn't work, and it would help if you told us which line causes the error message.

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Send e-mail to all (2003)

    Good morning, Hans, and thank you for the code. Now, I must confess that at first I was at a loss as to what to do with the code, so this morning I thought I'd explore the help file and the MSKB to learn what to do next. Well, I found MSKB article http://support.microsoft.com/kb/318881/en-us which looks like exactly what I need. Microsoft created the product (Access), and Microsoft wrote the article

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Send e-mail to all (2003)

    Hans, I apologize for the bad link. I've fixed the link, but kindly disregard that part of the discussion. I've gone back through the instructions, this time using a new database. I followed the steps precisely, and it worked.

    I've gone back to Plan A and used the code you furnished earlier. I created a separate command button ("cmdMailAll"), and I'm using tblMailingList as the source for the e-mail addresses. This new table has just one field: EmailAddress (a text field). When I click on the button, I get Run-time Error 87: An unexpected error has occurred. Following is the code as it appears in the VBA Editor

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Send e-mail to all (2003)

    If you hover the mouse button over strEmailAddress when you have clicked Debug, you can inspect its value. Perhaps this gives you an idea.

    Otherwise, could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Send e-mail to all (2003)

    Hans: When I hover over strEmailAddress, I see strEmailAddress = "mailto: FName.LName@domain.domain.com". Looks good to me, but it's not working. Now, that line of code includes "FollowHyperlink"

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Send e-mail to all (2003)

    This is strange - I'd expect 43 addresses in the string, not 1. Your code works in my test database if I use an appropriate table name and field name. You'd better post your database.

    BTW the EmailAddress field should be a text field.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Send e-mail to all (2003)

    It works OK on my PC. What happens if you replace the semicolon in the code with a comma?
    <code>
    strEmailAddress = strEmailAddress & "," & rst!EmailAddress</code>

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Send e-mail to all (2003)

    I've created a new database and imported only the table tblMailingList. Then I created a form with just one command button to fire the code, which I copied and pasted from the working database. I still get the same error. I've attached this new database for your inspection.

    Update: Something told me to try running the code on this new scaled-down database. Guess what: It works! However, the e-dresses I put into the attached database are bogus. They're not real ones from my working database. The only thing I can see that makes the real e-dresses different is that the real ones have an extra element in the part that follows the "@". For example: adam.west@holy.batman.com.

    Could that explain my problem? If yes, how do I fix it?

    My previous message was a bit unclear. When I mouse-over that line of code, I do see more than one e-mail address, and they're separated by semicolons.

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Send e-mail to all (2003)

    Same problem, but please re-read my previous post (I edited and re-posted it with some new information).

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Send e-mail to all (2003)

    No, it doesn't make any difference (or it shouldn't). You can test this by changing the bogus e-mail addresses in your sample database. If that works for you, we know that the code is OK, so there must be some kind of other problem in your original database.

  13. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Send e-mail to all (2003)

    Hm-m-m-m...that's really strange. In my little test database, I replaced my bogus e-dresses with a few real ones, and

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Send e-mail to all (2003)

    OK, I found it - the total string becomes too long. On my system, the limit is about 900 characters. With 43 addresses, this limit is easily passed. A workaround could be to send the e-mail to a maximum of (say) 25 people at a time. Another workaround would be to automate Outlook, this offers more flexibility but is more work.

  15. #15
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Send e-mail to all (2003)

    Oh, do I feel better now! I finally got this thing working after I stayed at work an extra hour, but I didn't understand the problem until I got your last post. I figured the problem lay with some invisible defect in one or more e-dresses, so I started deleting the more suspicious ones one at a time till it worked. Now I see there was nothing wrong with the e-dresses

Page 1 of 2 12 LastLast

Posting Permissions

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