Results 1 to 6 of 6
  1. #1
    cnstarz is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Oct 2010
    Posts
    31

    send email to email addresses in database?

    I have a table that keeps track of different managers for different departments. Each department has their own row of the table where we keep track of first/last names and email addresses of all the managers of each department (each departement has multiple managers). So, each row will have multiple email addresses.



    How can I make a button that will automatically insert all the email addresses into the "To" field of a new Outlook message?

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    google "ms access send email VBA"

    do that first. these scripts are everywhere on the web. when you get to the ".to" field of the outlook email message object, you'll have to loop the table via recordsets in code. There's no other way to do it, really.

  3. #3
    cnstarz is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Oct 2010
    Posts
    31
    I haven't really found anything that I'm looking for. The closest thing I've found is:

    http://support.microsoft.com/?id=318881

    All I need is for Access to just open up a new Outlook message window and populate the "To:" field with email addresses.

  4. #4
    cnstarz is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Oct 2010
    Posts
    31
    I've come across this site, and used their code:

    http://www.jephens.com/2007/05/13/ho...using-outlook/

    I commented out all the subjectline, bodyfile, and fso stuff cuz none of that pertains to me.

    However, I'm getting a "Run-time error '287': Application-defined or object-defined error" at line 78 (code below). I have these References checked:

    Visual Basic for Applications
    Microsoft Access 12.0 Object Library
    Microsoft Outlook 12.0 Object Library
    OLE Automation
    Microsoft ActiveX DataObjects 2.1 Library
    Microsoft Office 12.0 Object Library
    Microsoft ADO Ext. 6.0 for DDL and Security
    Microsoft DAO 3.6 Object Library

    This is my button code. Line 78 is where I get the error:

    Code:
    Option Compare Database
    Option Explicit
    Private Sub Command6_Click()
    Dim db As DAO.Database
    Dim MailList As DAO.Recordset
    Dim MyOutlook As Outlook.Application
    Dim MyMail As Outlook.MailItem
    'Dim Subjectline As String
    'Dim BodyFile As String
    'Dim fso As FileSystemObject
    'Dim MyBody As TextStream
    'Dim MyBodyText As String
     
    'Set fso = New FileSystemObject
     
    ' First, we need to know the subject.
     
    ' We can??t very well be sending around blank messages...
     
    ' Subjectline$ = InputBox$("Please enter the subject line for this mailing.", _
    "We Need A Subject Line!")
     
    ' If there??s no subject, call it a day.
     
    'If Subjectline$ = "" Then
    'MsgBox "No subject line, no message." & vbNewLine & vbNewLine & _
    '"Quitting...", vbCritical, "E-Mail Merger"
    'Exit Function
    'End If
     
    ' Now we need to put something in our letter...
     
    'BodyFile$ = InputBox$("Please enter the filename of the body of the message.", _
    '"We Need A Body!")
     
    ' If there??s nothing to say, call it a day.
     
    'If BodyFile$ = "" Then
    'MsgBox "No body, no message." & vbNewLine & vbNewLine & _
    '"Quitting...", vbCritical, "I Ain??t Got No-Body!"
    'Exit Function
    'End If
     
    ' Check to make sure the file exists...
    'If fso.FileExists(BodyFile$) = False Then
    'MsgBox "The body file isn??t where you say it is. " & vbNewLine & vbNewLine & _
    '"Quitting...", vbCritical, "I Ain??t Got No-Body!"
    'Exit Function
    'End If
     
    ' Since we got a file, we can open it up.
    'Set MyBody = fso.OpenTextFile(BodyFile, ForReading, False, TristateUseDefault)
     
    ' and read it into a variable.
    'MyBodyText = MyBody.ReadAll
     
    ' and close the file.
    'MyBody.Close
     
    ' Now, we open Outlook for our own device..
    Set MyOutlook = New Outlook.Application
     
    ' Set up the database and query connections
     
    Set db = CurrentDb()
     
    Set MailList = db.OpenRecordset("quSecurityEmailAddressesTest")
     
    ' This creates the e-mail
    ' We need to move it BEFORE we start the loop, since
    ' we don't want to make a bunch of e-mails, we just want one.
    Set MyMail = MyOutlook.CreateItem(olMailItem)
    ' now, this is the meat and potatoes.
    ' this is where we loop through our list of addresses,
    ' adding them to the RECIPIENTS collection
    Do Until MailList.EOF
    ' This adds the address to the list of recipients
    MyMail.Recipients.Add MailList("Primary_Email")
    'And on to the next one...
    MailList.MoveNext
     
    Loop
    ' This addresses it
     
    'MyMail.To = MailList("Primary_Email")
     
    'This gives it a subject
    'MyMail.Subject = Subjectline$
     
    'This gives it the body
    'MyMail.Body = MyBodyText
     
    'If you want to send an attachment
    'uncomment the following line
     
    'MyMail.Attachments.Add "c:myfile.txt", olByValue, 1, "My Displayname"
     
    ' To briefly describe:
    ' "c:myfile.txt" = the file you want to attach
    '
    ' olByVaue = how to pass the file. olByValue attaches it, olByReference creates a shortcut.
    ' the shortcut only works if the file is available locally (via mapped or local drive)
    '
    ' 1 = the position in the outlook message where to attachment goes. This is ignored by most
    ' other mailers, so you might want to ignore it too. Using 1 puts the attachment
    ' first in line.
    '
    ' "My Displayname" = If you don??t want the attachment??s icon string to be "c:myfile.txt" you
    ' can use this property to change it to something useful, i.e. "4th Qtr Report"
     
    'This sends it!
     
    'MyMail.Send
     
    'Some people have asked how to see the e-mail
    'instead of automaticially sending it.
    'Uncomment the next line
    'And comment the "MyMail.Send" line above this.
     
    MyMail.Display
     
    'Cleanup after ourselves
     
    Set MyMail = Nothing
     
    'Uncomment the next line if you want Outlook to shut down when its done.
    'Otherwise, it will stay running.
     
    'MyOutlook.Quit
    Set MyOutlook = Nothing
     
    MailList.Close
    Set MailList = Nothing
    db.Close
    Set db = Nothing
     
    End Sub
    Any ideas?

  5. #5
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    instead of using ".add", why don't you just concat all of the values in your table together using a comma and then put that string right into the "to" property? Like this:
    Code:
    do until .eof
    
    string = string & ","
    
    loop
    
    mymail.to = string
    oh, and by the way, after you open the recordset, you have to do this:
    Code:
    maillist.movelast
    maillist.movefirst
    make sure to add that code, otherwise you'll get one email address!

  6. #6
    cnstarz is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Oct 2010
    Posts
    31
    Quote Originally Posted by ajetrumpet View Post
    instead of using ".add", why don't you just concat all of the values in your table together using a comma and then put that string right into the "to" property? Like this:
    Code:
    do until .eof
     
    string = string & ","
     
    loop
     
    mymail.to = string
    oh, and by the way, after you open the recordset, you have to do this:
    Code:
    maillist.movelast
    maillist.movefirst
    make sure to add that code, otherwise you'll get one email address!
    hmm, your first example is kind of above my head. i'm not familiar with VBA coding -- i've just been copying and pasting stuff. could you explain it a little more?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How can I send an email from access???
    By Asma in forum Programming
    Replies: 2
    Last Post: 12-07-2011, 07:49 AM
  2. send email reminder
    By azaz in forum Forms
    Replies: 1
    Last Post: 12-26-2010, 08:35 AM
  3. send email from a form
    By maxbre in forum Programming
    Replies: 4
    Last Post: 11-12-2010, 01:43 AM
  4. Exporting Outlook email addresses
    By noidea in forum Import/Export Data
    Replies: 0
    Last Post: 08-01-2009, 01:48 PM
  5. Combining two Email Addresses
    By Frodo in forum Access
    Replies: 0
    Last Post: 09-16-2007, 07:07 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums