Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jul 2001
    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

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

    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

    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

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


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



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

    Exit Sub

    '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!

    Wasting more of your valuable time...

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Edmonton, Alberta, Canada
    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
    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


Posting Permissions

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