Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2008
    Posts
    37

    Unanswered: Outlook automation

    Hi, im trying to send the same email to multiple recipients, their email addresses are got by use of a recordset. I had it working so that a separate email was sent to each one but is there a way just to send the one email to all the recipients. I tried moving the create item outside of the loop but it wont work. Here's my code:

    Set rs = db.OpenRecordset("Select * from [tblStudents] where tblStudents.[STU_TU_CODE] = " & "'" & retiring & "';")

    Do While Not rs.EOF
    If Not IsNull(rs("STU_EMAIL")) Then
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    addresses = rs("STU_EMAIL")
    With objOutlookMsg
    .Recipients.add (addresses)
    .subject = "Change of Tutor"
    .Body = strbod
    .Send
    End With
    End If

    rs.MoveNext
    Loop

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Loop through the recordset to build your To string.

    If Not IsNull(rs("STU_EMAIL")) Then
    addresses = addresses & ";" & rs("STU_EMAIL")
    end if

    rs.MoveNext
    Loop

    Created your email after the loop.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can you set the recipients to a semi-colon delimited list of addresses?
    E.g.
    Code:
    Dim someAddresses As String
    
        someAddresses = "test@test.com; test2@test.com"
        <snip>
        .Recipients.add (someAddresses)
        <snip>
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2008
    Posts
    37
    Ive tried rouges solution as below but i get an error "outlook does not recognise one or more names"

    Do While Not rs.EOF
    If Not IsNull(rs("STU_EMAIL")) Then
    addresses = addresses & ";" & rs("STU_EMAIL")
    End If

    rs.MoveNext

    Loop

    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

    With objOutlookMsg
    .Recipients.add (addresses)
    .subject = "Change of Tutor"
    .Body = strbod
    .Send
    End With

  5. #5
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Invalid addresses usually give that error. Do you check the email addresses prior to running this code? You may want to do a debug.print on addresses and see if you can figure out where the problem is.

  6. #6
    Join Date
    Jan 2008
    Posts
    37
    well for testing purposes i am using two of my own email addresses

  7. #7
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Try changing .Recipients.add (addresses) to .To = addresses

    Are the correct email addresses being returned with the debug.print?

  8. #8
    Join Date
    Jan 2008
    Posts
    37
    Changing it to .To fixed it. Thanks for all your help!

Posting Permissions

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