Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Posts
    52

    Unanswered: access ----> Outlook

    I'm trying to figure out a way to import all email addresses and shoot them out in one shot.


    The current set loops each email address into its own message, its become a pain.


    I've used the code obtained from microsoft.com. Can anyone give suggestions on how to create a loop that inserts all the addresses into a signle email?



    Private Sub Command14_Click()


    Dim MyDb As Database
    Dim MyRS As Recordset
    Dim strSQL As String
    Dim Address As String
    Dim lngCount As Long
    Dim lngMyRS As Long

    strSQL = "SELECT * FROM qryAttendance WHERE [type of conference]='" & basConference() & "' and [Times Attended]>=" & basAttendance() & ";"

    Debug.Print strSQL

    Set MyDb = CurrentDb
    Set MyRS = MyDb.OpenRecordset(strSQL)

    MyRS.MoveFirst

    ' Create the Outlook session.
    Set objOutlook = CreateObject("Outlook.Application")

    Do Until MyRS.EOF
    ' Create the e-mail message.
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    Address = MyRS![Email]

    With objOutlookMsg
    ' Add the To recipient(s) to the e-mail message.
    Set objOutlookRecip = .Recipients.add(Address)
    objOutlookRecip.Type = olTo

    ' Add the CC recipient(s) to the e-mail message.
    If (IsNull(Address)) Then
    Else
    Set objOutlookRecip = .Recipients.add(Address)
    objOutlookRecip.Type = olCC
    End If

    ' Set the Subject, Body, and Importance of the e-mail message.
    .Subject = "test"
    .Body = "test body"
    .Importance = olImportanceHigh 'High importance

    'Add attachments to the e-mail message.
    'If Not IsMissing(AttachmentPath) Then
    'Set objOutlookAttach = .Attachments.add(AttachmentPath)
    'End If

    ' Resolve each Recipient's name.
    For Each objOutlookRecip In .Recipients
    objOutlookRecip.Resolve
    If Not objOutlookRecip.Resolve Then
    objOutlookMsg.Display
    End If
    Next
    .Send
    End With
    MyRS.MoveNext
    Loop
    Set objOutlookMsg = Nothing
    Set objOutlook = Nothing

    End Sub

  2. #2
    Join Date
    Feb 2004
    Location
    Dorset UK
    Posts
    147
    Sorry, I'm trying to understand this:

    You have a query (qryAttendance) with a list of email names, you want every name in that query in one email in the to section ?

    ~Ken

  3. #3
    Join Date
    Feb 2004
    Location
    Dorset UK
    Posts
    147
    Attached Dbase..
    Attached Files Attached Files

  4. #4
    Join Date
    Mar 2004
    Posts
    52
    yes, that it correct


    Originally posted by Ken_Hart
    Sorry, I'm trying to understand this:

    You have a query (qryAttendance) with a list of email names, you want every name in that query in one email in the to section ?

    ~Ken

  5. #5
    Join Date
    Feb 2004
    Location
    Dorset UK
    Posts
    147
    Originally posted by ax2ron
    yes, that it correct

    Did the database work ? Or was that yes to my question

    Ken

  6. #6
    Join Date
    Mar 2004
    Posts
    52
    Ken,

    that was yes to the problem. I'm abou tto try out the code. Thanks a bunch!!! Will get back to you

    Aaron


    Originally posted by Ken_Hart
    Did the database work ? Or was that yes to my question

    Ken

  7. #7
    Join Date
    Mar 2004
    Posts
    52
    Ken,

    I ran the code and recieved the following error:

    Recordset not updateable. I'm assuming that this code is attempting to write to the form? Why is that?


    Private Sub Command14_Click()

    Dim MyDb As Database
    Dim MyRS As Recordset
    Dim strSQL As String
    Dim Address As String


    strSQL = "SELECT * FROM qryAttendance WHERE [type of conference]='" & basConference() & "' and [Times Attended]>=" & basAttendance() & ";"

    Debug.Print strSQL

    Set MyDb = CurrentDb
    Set MyRS = MyDb.OpenRecordset(strSQL)

    MyRS.MoveFirst

    Do Until MyRS.EOF
    Email = MyRS.Fields(0) & ";" & Email <-----ERROR HERE!
    MyRS.MoveNext
    Loop

    Email = Left(Email, Len(Email) - 1)

    Set MyolApp = CreateObject("Outlook.Application")
    Set MyItem = MyolApp.CreateItem(olMailItem)
    Set MyRecipient = MyItem.Recipients.add(Email)

    MyItem.Subject = "Your Title"

    Msg = Chr(13) & "Hi," & Chr(13) & Chr(13)

    MyItem.Body = "This is a test" & Chr(13) & Chr(13) & "Thanks"
    MyItem.Display

    MyRS.Close

    End Sub








    Originally posted by ax2ron
    Ken,

    that was yes to the problem. I'm abou tto try out the code. Thanks a bunch!!! Will get back to you

    Aaron

Posting Permissions

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