Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jul 2001
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access and Outlook Mail - Bad coding? (Access 97 - SR2)

    Dear Access Guru's:
    It has been awihle since I have posted here, but I am once again having some troubles. I have a database that utilizes Microsoft Outlook to mail to a selected number of mail recipients. In this database I have a code that attaches selected files to be attachments in the email being sent out (to lets say 100 addresses).
    When testing this database, the atached files are only recieved by the FIRST recipient on the list. Everyone else gets the message, but none of the attached files. Here is the code in the module.

    ****
    Sub SendOutlookMail()
    On Error GoTo Errhandler
    Dim DB As Database
    Dim DB2 As Database
    Dim RS As Recordset
    Dim RS2 As Recordset
    Set DB = CurrentDb()
    Set DB2 = CurrentDb()
    Set RS = DB.OpenRecordset("qrysendtest")
    Set RS2 = DB2.OpenRecordset("tblDocument")
    Dim aCounter As Integer
    Dim PersonSendTo As String
    Dim aFileName As String

    Do While Not RS.EOF
    RS.Edit

    PersonSendTo = RS![Email]
    RS![DateSend] = Now()
    RS.Update

    Dim appOutLook As Outlook.Application
    Dim MailOutLook As Outlook.MailItem
    Set appOutLook = CreateObject("Outlook.Application")
    Set MailOutLook = appOutLook.CreateItem(olMailItem)

    With MailOutLook
    .To = PersonSendTo
    .Subject = SubjectLine
    .HTMLBody = SendMsg & "

    "

    Do While Not RS2.EOF
    RS2.Edit

    aFileName = RS2! <img src=/S/disk.gif border=0 alt=disk width=15 height=15> 'This is actually the word "[ F i l e ]" w/out the spaces. Auto'd to a pic
    RS2.Update

    .Attachments.Add aFileName, olByValue, 1, "File" & aCounter
    aCounter = aCounter + 1

    RS2.MoveNext

    Loop
    '.Attachments.Add aFileName, olByValue, 1, "File" & aCounter
    .Send
    End With

    RS.MoveNext

    Loop

    Set RS = Nothing
    Set RS2 = Nothing
    Set DB = Nothing

    Exit_sub:
    Exit Sub

    Errhandler:
    'GoTo Exit_sub
    MsgBox Err.Description

    End Sub
    ****

    Maybe I have placed something in the wrong spot? Please help, I'm lost. Thanks a ton!

    -Happen609
    Wasting more of your valuable time...

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access and Outlook Mail - Bad coding? (Access 97 - SR2)

    Stick an RS2.Movefirst immediately after your .Send . You looped through all the RS2 records for the first recipient, but RS2 is still sitting at EOF for all the other recipients.

  3. #3
    New Lounger
    Join Date
    Jul 2001
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access and Outlook Mail - Bad coding? (Access 97 - SR2)

    Yeah.. I managed to play with it enough to figure it out yesterday. I do apreciate the reply, however. All I did was move the set db2 and set rs2 in front of the attacment loop .. and then I set them to nothing after the attachment loop ... but they were both included in the mailing loop. This way it reset the attachments to the top of the list after every mailing. Brilliant!! .. ;-) thanks

    -Happen609

Posting Permissions

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