Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2002

    Unanswered: Multiple E-Mail Attachments

    I run multiple reports that ultimately create folders with clients names (about 50) and then dumps the individual reports (about 20) into each directory. I am trying to then generate e-mails that will step through each client folder and attach all the files in there to the newly created e-mail. The code I have written grabs the files and attaches them, but I need it to move to the next client folder. I tried recreating the code for the folders but it's not working. Any help would be appreciated.

    Dim stName As String
    Dim stDir As String
    stName = Dir(stDir & "\*.*", vbDirectory)

    Do While stName <> ""
    If stName <> "." And stName <> ".." Then
    If (GetAttr(stDir & "\" & stName) And vbDirectory) = vbDirectory Then
    If err.Number = 5 Then err.Clear

    Dim strName As String
    Dim strDir As String
    strDir = "C:\Axiom Reports\Month-End\"
    strName = Dir(strDir & rsReports!CompanyName & "\*.*")

    Do While strName <> ""
    If (GetAttr(strDir & strName) And vbDirectory) <> vbDirectory Then
    If err.Number = 5 Then err.Clear
    If strName <> "." Or strName <> ".." Then
    .AttachmentAdd strDir & rsReports!CompanyName & "\" & strName
    End If
    End If
    strName = Dir
    End If
    End If
    stName = Dir

  2. #2
    Join Date
    Dec 2003

    Create a method

    I think your best soution would be to create some subroutine to accept a clients name

    On the form or in a new module

    Public Sub SendClientReports(ClientName As String, ClientEmail as string)
    On Error GoTo ErrHandler

    Const STARTPATH = "C:\Axiom Reports\Month-End\"
    Dim stName As String
    Dim EmailAttachments As Collection

    stName = Dir(stDir & "\*.*", vbDirectory)

    stName = Dir(STARTPATH & ClientName & "\*.*")
    Do While stName <> ""
    On Error Resume Next
    If (GetAttr(stDir & stName) And vbDirectory) <> vbDirectory Then
    'File Already open?
    If Err.Number = 5 Then Err.Clear
    If stName <> "." Or stName <> ".." Then
    .attachmentAdd stName
    End If
    End If
    'Get Next entry
    stName = Dir

    'Send email her to the client based on the variable passed in and include all attachments
    Exit Sub

    Select Case Err.Number
    Case 5
    Case Else
    MsgBox "Error " & PROC_NAME & vbCrLf & vbCrLf & Err.Number & ": " & Err.Description, vbCritical, "Error: " & MODULE_NAME
    End Select

    End Sub

    Then with this you can store the information about your clients in a recordset

    on the button to execute this information this is DAO so you will need to make sure this is added in your library

    dim db as database: dim rs as recordset
    set db = currentdb(): set rs = db.openrecordset("SELECT ClientName, Email FROM tblClientINFO)
    while not rs.eof
    sendclientreports(rs.fields(0).value, rs.fields(1).value)

    I dont think I can get more specific not knowing what email client you are using or your table structure etc. But this should get you headed in the right direction.

    One last thing. Make sure you annotate where you borrow code from. A lot of the code you referenced is strikingly familiar to what is on MVP Access Based on the authors comments.



Posting Permissions

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