Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Email Address Merge (2000)

    Can anyone help me with this? I have a query in Access that gives me a list of email addresses. I would like the output of this query to be inserted into the BCC field of a new outlook email along with a message in the body.
    Easy Access

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

    Re: Email Address Merge (2000)

    See <post#=158114>post 158114</post#> for code that shows how to loop through the records of a table or query and create an e-mail. To add the names to the BCC box instead of to the To box, add a line to change the recipient type:

    Do While Not rstNames.EOF
    myItem.Recipients.Add(rstNames!EMailAddress).Type = olBCC
    rstNames.MoveNext
    Loop

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email Address Merge (2000)

    Here is a function to create a mail with multiple BCC recipients. The first name in the list will go in the TO field and the following in the BCC.
    Each time you run it, it ask permission to access outlook and I don't know how to stop this.<pre>Sub SendMail()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim myOLApp As New Outlook.Application
    Dim myOLItem As Outlook.MailItem

    Set db = CurrentDb
    Set rst = db.OpenRecordset("Table1") '<--- put here the nam of your query
    rst.MoveFirst
    Set myOLItem = myOLApp.CreateItem(olMailItem)
    myOLItem.To = rst!email '<--- replace email with the name of your field
    myOLItem.Subject = "Test AutoFill BCC"
    myOLItem.Body = "This is a test mail"
    rst.MoveNext
    Do While Not rst.EOF
    myOLItem.BCC = myOLItem.BCC & rst!email & " , " '<--- replace email with the name of your field
    rst.MoveNext
    Loop
    myOLItem.Display
    Set myOLItem = Nothing
    Set rst = Nothing
    Set db = Nothing
    End Sub</pre>

    This need a reference to be set to the Microsoft DAO object Library and to the Outlook Library
    Francois

  4. #4
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Email Address Merge (2000)

    I have inserted this code and set the references in my library to DAO 3.6 Library and Outlook 10.0 Object Library; however, it does not do anything.

    Private Sub cmdPreview_Click()

    On Error GoTo Err_Handling

    If IsNull([lstSubMenu]) Then
    MsgBox "You must first select a 'Category' then a report in the 'Sub Menu' List.", , "Blank Sub Menu"
    Exit Sub
    End If

    Select Case lstSubMenu

    Case 1
    DoCmd.OpenForm "frmClientDataAdd"
    Case 2
    DoCmd.OpenForm "frmClientData"
    Case 3
    DoCmd.OpenForm "frmScheduled"
    Case 4
    DoCmd.OpenForm "frmCityNames"
    Case 5
    DoCmd.OpenReport "rptInfluenzaAccountSheet", acViewPreview
    Case 6
    DoCmd.OpenReport "rptScheduledClients", acViewPreview
    Case 7
    DoCmd.OpenReport "rptScheduledClientAll", acViewPreview
    Case 8
    DoCmd.OpenReport "rptRegistrationReport", acViewPreview
    Case 9
    DoCmd.OpenReport "rptBlankAccountSheet", acViewPreview
    Case 10
    Call SendMessage("qryRegistrationEmail!strEMailAddress" , "Your registration form has been revceived. Thank you.")

    End Select

    Err_Close:
    Exit Sub

    Err_Handling:
    Resume Err_Close

    End Sub

    Public Sub SendMessage(strNameTable As String, strBody As String)

    Dim Outlook As Object
    Dim myItem As Object
    Dim blnNeedToQuit As Boolean
    Dim rstNames As Recordset

    Set rstNames = CurrentDb.OpenRecordset(strNameTable, dbOpenDynaset)

    ' Reference Outlook application object
    On Error Resume Next
    Set Outlook = GetObject(, "Outlook.Application")

    ' If Outlook isn't running, then create an instance of it
    blnNeedToQuit = False
    If Err Then
    Set Outlook = CreateObject("Outlook.Application")
    blnNeedToQuit = True
    End If
    On Error GoTo 0

    Set myItem = Outlook.CreateItem(olMailItem)

    Do While Not rstNames.EOF
    myItem.Recipients.Add(rstNames!EMailAddress).Type = olBCC
    rstNames.MoveNext
    Loop


    myItem.Subject = "Registration Form Receipt"
    myItem.Body = strBody

    On Error Resume Next
    myItem.Send
    On Error GoTo 0

    If blnNeedToQuit Then Outlook.Quit

    End Sub

    Any suggestions why this is not working?
    Easy Access

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email Address Merge (2000)

    The only thing I have to change to make you SendMessage function work with Access/Outlook 2000 and Access/Outlook 2003 is:
    Dim rstNames As DAO.Recordset

    I don't have Xp installed any more.
    Francois

  6. #6
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Email Address Merge (2000)

    Thank Francois and Hans. <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30> I went with the SendMail sub and that worked fine.
    One question: How can I get the code to insert a semicolon ([img]/forums/images/smilies/wink.gif[/img] between each address. I tried substituting the semicolon in this line but everything gets run together when the code is run:
    myOLItem.BCC = myOLItem.BCC & rst!email & " ; "
    Easy Access

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

    Re: Email Address Merge (2000)

    What do you mean by "everything gets run together"?

  8. #8
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Email Address Merge (2000)

    When I substitute a semicolon for the comma in that line of code all the email addresses that gets put in the BCC field look like this:

    Michael@msn.comsteve@yahoo.comlisa@wopr.com
    Easy Access

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

    Re: Email Address Merge (2000)

    Sorry, no idea. It works OK for me; I have tested with both Dutch and US regional settings (decimal separator, list separator etc.)

    But since the other code (with Recipients.Add) works for you, I would use that.

  10. #10
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Minneapolis, MN, Minnesota
    Posts
    162
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Email Address Merge (2000)

    I did not check the "Allow comma as address separator" in the advanced email options tab. It works fine now. Thank you very much for all your help.
    Easy Access

  11. #11
    New Lounger
    Join Date
    May 2004
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email Address Merge (2000)

    Hi everyone,

    Can I jump in here and ask if it's possible to specify that a file be attached to the e-mail being created?

    Thanks
    David

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

    Re: Email Address Merge (2000)

    Yes,

    You can add items to the Attachments collection of the MailItem object. For example:

    myItem.Attachments.Add "C:ExcelBook1.xls"

    where myItem is an Outook.MailItem object.

Posting Permissions

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