Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Mar 2004
    Posts
    287

    Unanswered: Sending multiple emails

    Hi,

    I have a database with each record having an email address.
    I know how to send an email from clicking a button (command button) and vi VBA but I need to link a report (or letter wording) to an email and send to all records that meet a certain criteria (eg: all ACTIVE True), but make sure each of the emails are sent individually, so each can not see anyone else's email address?

    How could I do this please.

  2. #2
    Join Date
    Mar 2004
    Posts
    287
    or even better, a way to stop outlook requesting me to send the email everytime.

  3. #3
    Join Date
    Mar 2004
    Posts
    287
    actually if i have a query of just email addresses, whats the best way to connect this to emails being sent automatically (individual emails if possible not distribution lists) with a set email body.

  4. #4
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    I have a seperate table called tblEmail with a Memo field that stores the body of the email. When the user clicks the button to email their client, the program does a Dlookup to get the value of the fld in the table, and then uses
    Code:
    DoCmd.SendObject , , , strEmail, , , , strMessage, False
    where strEmail is the user's email address and strMessage is the result of the DLookup.

  5. #5
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    I have a seperate table called tblEmail with a Memo field that stores the body of the email. When the user clicks the button to email their client, the program does a Dlookup to get the value of the fld in the table, and then uses
    Code:
    DoCmd.SendObject , , , strEmail, , , , strMessage, False
    where strEmail is the user's email address and strMessage is the result of the DLookup.

  6. #6
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    take a look at Ostrosoft's SMTP control...
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


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

  8. #8
    Join Date
    Mar 2004
    Posts
    287
    jmahaffie - could you explain a bit more i n depth please.

    many thanks.

  9. #9
    Join Date
    Mar 2004
    Posts
    287
    Tried your way and it does the same - you have to run it from a form like my original sendemail. Whereas I needed to run it from a query list of email addresses.

    Here is my original code (that works):
    Code:
        
    Dim mli As Outlook.MailItem
        
        InitOutlook
        Set mli = ola.CreateItem(olMailItem)
        mli.To = [Forms]![Main]![Email]
        mli.Subject = "Test"
        mli.Body = "Dear " & [Forms]![Main]![Title] & " " & [Forms]![Main]![Surname] & "," & vbCrLf & [Forms]![LetterInput]![Letter1] & vbCrLf & [Forms]![LetterInput]![Letter2] & vbCrLf & [Forms]![LetterInput]![Letter3] & vbCrLf & [Forms]![LetterInput]![Letter4]
    
        mli.Send
        
        Set mli = Nothing
        CleanUp
    This creates a personal email depending on a hidden window (LetterInput) text and works well. Apart from I have to do this to every record and outlook asks me to confirm YES or NO.
    So my next problem is how to remove the confirmation dialog box upon sending each email as well as possibly sending emails from a query list - if possible? Thanks.

    ADDED: Also, does anyone know how to add a reply address to the above code as mli.reply doesn't work?

  10. #10
    Join Date
    Feb 2007
    Posts
    348
    make sure you are using False after the last comma in your send object command, as jmahaffie said. That indicates that you do not need to review the email before it goes out. You may have a setting in your email app that overrides this. For example I have my signature set up so that I have to approve its addition to each email that goes out, via my email client. That means that even if I set that to false, my email client halts the process for my approval of the signature.

  11. #11
    Join Date
    Mar 2004
    Posts
    287
    ok thanks. I have appeared to got the email working and have found the best way to send is using BCC field, which works. That way only one email can be sent via a list of email addresses (query).

    But how would I like the BCC field to the query email addresses please?

    I have it working with one email (from the top of the query) but need it to grab all email addresses in the query (under EMAIL field) and place ';' between them. Thanks.

  12. #12
    Join Date
    Feb 2007
    Posts
    348
    you probably just need to concatenate them all in a string with semicolons as separators.
    But I haven't had the pleasure of working with outlook yet.

  13. #13
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    Quote Originally Posted by NeilMansell
    Tried your way and it does the same - you have to run it from a form like my original sendemail. Whereas I needed to run it from a query list of email addresses...
    So my next problem is how to remove the confirmation dialog box upon sending each email as well as possibly sending emails from a query list - if possible? Thanks.

    Couldn't you grab the query into a recordset and then loop through while rs.EOF=False, sending to each address in the query?
    Code:
    Dim rs as DAO.Recordset
    Dim strSQL as String
    
    'stores the query SQL in a string, so that you can put it in a recordset
    strSQL=CurrentDB.QueryDefs("QueryName").SQL
    'stores the results of the query in a recordset
    set rs=CurrentDB.OpenRecordset(strSQL)   
    
    Do While rs.EOF = False
      'send email to the address in the recordset
      DoCmd.SendObject , , , rs.Fields("fldEmail"), , , , strMessage, False
      rs.MoveNext  'go to the next record in the recordset
    Loop
    
    Set rs = Nothing
    As far as removing the confirmation window goes, I believe that is something that was patched, to prevent emails sent out on the user's behalf without their knowledge. To my knowledge, there's no way to do that through VBA by accessing some property of the SendObject or Outlook application. HOWEVER,
    if you can't find any other way to do it, you could use SendKeys to switch to the outlook app, click OK and switch back each time. So maybe the following code between the SendObject and the MoveNext lines:

    Code:
        SendKeys "%{TAB}", 5000 'switch to the other application
        SendKeys "{ENTER}", 10000 'presses the enter key, to send
        SendKeys "%{TAB}", 5000  'switches back
    That can get messy, but if you have to do it, then you have to. Maybe someone else has some better suggestions.

  14. #14
    Join Date
    Mar 2004
    Posts
    287
    thanks, i like your first idea, but how where would this code go - in the form command button or in the module that sends the email?

    I have tried both but both error.

  15. #15
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    You could put in behind a command button, no problem. Anywhere you want, really. What kind of errors are you getting? Make sure you're replacing the
    strSQL=CurrentDB.QueryDefs("QueryName").SQL with the name of your query. And since the recordset was DAO, make sure you've gone to Tools-References and added in Microsoft ActiveX Data Objects library. Lemme know what errors you're getting.

Posting Permissions

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