Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    339
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Email Addresses in Access Database (2003)

    Hi All,

    I have an access database which contains contact details for a sporting body. The email addresses for contacts are in that database. There must be a way I can run a query and then use the email addresses in Outlook but I haven't been able to find one. Can anyone shed some light on how I would do this.

    I wasn't sure whether I should post this here or in the Access forum.

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

    Re: Email Addresses in Access Database (2003)

    I think the solution will always involve VBA code:

    You can send e-mails from Access using SendObject or using Automation (i.e. using Outlook VBA from Access).
    Conversely, you could use DAO or ADO code in Outlook to open a recordset based on an Access table or query and retrieve the e-mail addresses.

    If you provide more information about what you want to accomplish, we may be able to give more specific help. We might move this thread to the Access forum.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    339
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email Addresses in Access Database (2003)

    Thanks Hans

    Please feel free to move this to the Access board. I am brave enough to give VBA in access a go but have never done it before.

    Basically the database is pretty much a flat file database. It contains contact information along with some other supporting information.

    The main fields we would have to deal with are ones called "primaryemailaddress" and "maillist" - the maillist field is a yes no.

    Pretty much I want to send an email to anyone who has indicated yes in the maillist field. After posting the original email, I ran a query, got the email addresses then copied and pasted them into outlook (after taking them through word, converting the table to text and replacing the paragraph markers with semi colons).

    In order to protect people's privacy I put all the email addresses in the BCC field. There were about 60 addresses.

    Unfortunately a number of mail servers bounced the message - I suspect because they were thought to be spam.

    Basically all my requirement is, is to send out regular but different emails, to people who have asked for them but somehow also get around the spam problem.

    Any help you can provide would be appreciated.

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

    Re: Email Addresses in Access Database (2003)

    So you would like to send individual e-mails to each of the persons who has "Yes" in the maillist field, with the primaryemailaddress placed in the To field instead of the BCC field?

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

    Re: Email Addresses in Access Database (2003)

    I'll take a risk and suggest some code to be run from Outlook. The following code should be copied into a standard module in Outlook. Make sure to substitute the correct names and strings where necessary. You should set a reference to the Microsoft ActiveX Data Objects 2.n Library in Tools | References... You can run the code from Outlook by selecting Tools | Macro | Macro's or from the Visual Basic Editor by clicking in the code and pressing F5.

    Note 1: Outlook will ask for permission each time the code tries to send an e-mail. Install and activate the free utility ClickYes to suppress this warning.
    Note 2: You may want to test the code with some dummy internal e-mail addresses first.

    Sub SendMails()
    Const strDatabase = "C:AccessTest.mdb"
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim itm As Outlook.MailItem

    On Error GoTo ErrHandler

    ' Open connection to Access database
    With cnn
    .Provider = "Microsoft.Jet.OleDB.4.0"
    .ConnectionString = strDatabase
    .Open
    End With

    ' Open recordset
    rst.Open "SELECT * FROM [tblSomething] WHERE maillist = True", _
    cnn, adOpenForwardOnly, adLockOptimistic, adCmdText

    ' Loop through records of recordset
    Do While Not rst.EOF
    ' Create e-mail
    Set itm = CreateItem(olMailItem)
    With itm
    .Subject = "Test mailing"
    .Body = "This is a test of our mailing system."
    .Recipients.Add rst!primaryemailaddress
    .Send ' or .Display
    End With
    rst.MoveNext
    Loop

    ExitHandler:
    ' Clean up
    On Error Resume Next
    Set itm = Nothing
    rst.Close
    Set rst = Nothing
    cnn.Close
    Set cnn = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

Posting Permissions

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