Loop (.Attachments.Add) based on a table (1 Viewer)

[FrostByte]

Thanks to some great pointers here.

Attach all files in a folder to an email

Hi all, I've searched in vain so thank you in advance. I want to declare a variable which points to a folder and then have an email created with all files in that given directory attached. They'll will be different names each time, but I want them all attaching automatically. Kind Regards

www.access-programmers.co.uk

and

Microsoft Access tips: List files recursively

Illustrates how to list files recursively in VBA. Output can be listed to the immediate window or written to a table.

allenbrowne.com

I have managed to fudge together a routine that reads the contents of a given folders into a table. I'm only missing a loop that can read the table and attach the files based on it.

My normal code is based on an export of a report and attaches the variable file name. (as below). But in this case I think I need a loop that reads the table and creates the .Attachments.Add based on each row.

With oMail
'
'.To = "To Field"
.Subject = "Please find attached an allocation posting processed by SBL. Ref: " & Me.BatchNumberChoice
.Body = stText
.Attachments.Add fileName
.Attachments.Add fileName2
.Display '<-- comment out this line if you don't first want to view the email before sending.
'.Send '<-- uncomment this line if you want the email to auto send
End With

 

[Gasman]

No need for a table, just add to email as you get each file.

 

[FrostByte]

No need for a table, just add to email as you get each file.

The files are placed in the directory days before.

 

[Gasman]

The files are placed in the directory days before.

Yes, you have aleady said that, but why process the folder and get the filename, only to put it in a table, then to process that table to add the filename?
Now if you wanted to keep track of when you sent that file, that would be a different story, biut you have never mentioned that?

 

[FrostByte]

Yes, you have aleady said that, but why process the folder and get the filename, only to put it in a table, then to process that table to add the filename?
Now if you wanted to keep track of when you sent that file, that would be a different story, biut you have never mentioned that?

I'm clearly no expert. I've ended up down a path trying to find a solution. ;-)

I was only ever wanting to point the code to a folder and attach every file contained in it.

 

[Gasman]

I'm clearly no expert. I've ended up down a path trying to find a solution. ;-)

I was only ever wanting to point the code to a folder and attach every file contained in it.

So instead of putting the filename into a table, just add it as an attachment?

 

[FrostByte]

This is where i'm at so far....

The table with all my data is called "Fields" the full path and file name is in the field "FullPath"

I get a compile error: Method or data member not found in the while loop

Is doesn't like .Attachments.Add PKey but if I use Attachments.Add PKey it works without adding any attachments

Feels like I'm close, just need that loop of the table to work. Cheers

Private Sub Command3_Click()







Dim oApp As Object

    Dim oMail As Object

    Dim fileName As String

    Dim fileName2 As String

 

todaydate = Format(Date, "MMDDYYYY")





stText = "Please find attached XXXXXX Files." & Chr$(13) & _

    " " & Chr$(13) & _

    " " & Chr$(13) & _

    "Regards Account Team" & Chr$(13) & _

    " " & Chr$(13) & _

    " " & Chr$(13) & _

    " " & Chr$(13) & _

    " " & Chr$(13) & _

    " " & Chr$(13) & _

    " " & Chr$(13) & _

    " " & Chr$(13) & _

    ""



   'Create and show the Outlook mail item

    Set oApp = CreateObject("Outlook.Application")

    Set oMail = oApp.CreateItem(0)

 

 

 

 

 

Dim database As DAO.database

Dim table As DAO.Recordset

Dim PONum As String

Dim folder As String

Set database = CurrentDb

Set table = database.OpenRecordset("Files")

 

 

With oMail

        '

        '.To = "To Field"

        .Subject = "Please find attached XXXXXX Files: "

        .Body = stText

    

    

    

    

    

With table ' For each record in table

   Do While .EOF 'exit with loop at end of table

   Attachments = table.Fields("FullPath").Value 'get list of attachments

   PKey = table.Fields("FullPath").Value ' get record key

   Attachments.Add PKey

   .Attachments.Add PKey

   Attachments.MoveNext 'move to next attachment

   .MoveNext 'move to next record

Loop

End With

    

    

    

    

        '.Attachments.Add fileName

        '.Attachments.Add fileName2

        .Display

        '.Send

    End With





    'Application.ScreenUpdating = True

 

    Set oMail = Nothing

    Set oApp = Nothing

 

Exit Sub

 

[Gasman]

In future, please post your code within code tags using the </> icon. That keeps any indentation, which I hope you are using?
Have you looked in Similar threads for the syntax?

Firstly you need NOT .EOF as that loop will never execute.
You need to learn about walking your code, inspecting variables and setting breakpoints. See the link in my signature.

You need Option Explicit at the top of every module, you do not have this as Pkey is not declared?
In the VBA editor window, click on Tools/Option and set 'Require Variable Declaration'. That will add that to all new modules, but not existing ones. You need to do that.
As you are within the loop for the oMail object you can use .Attachments.Add

Are you storing the FULL pathname of the file, as that is what you need ot build the full pathname to be able to attach a file.

Here is some code I used, not for attachments, but for table entries into the email. There is a fair bit in there, but you should be able to see the logic.

 

[FrostByte]

ty Gasman

I have used the </>
Added NOT .EOF
Added Option Explicit and declared all variables and PKey As String
I do store the full file path and name in FullPath (C:\Temp2\Test\New Microsoft Excel Worksheet.xlsx).
I've always stepped through with excel VBA using f8 but with access its never worked (Special Keys are ticked)


The code however hates .Attachments

.Attachments = table.Fields("FullPath").Value 'get list of attachments

compile error: Method or data member not found in the while loop

Private Sub Command6_Click()
Dim oApp As Object
    Dim oMail As Object
    Dim fileName As String
    Dim fileName2 As String
    Dim stText As String
    
  
stText = "Please find attached XXXXXX Files." & Chr$(13) & _
    " " & Chr$(13) & _
    " " & Chr$(13) & _
    "Regards Account Team" & Chr$(13) & _
    " " & Chr$(13) & _
    " " & Chr$(13) & _
    " " & Chr$(13) & _
    " " & Chr$(13) & _
    " " & Chr$(13) & _
    " " & Chr$(13) & _
    " " & Chr$(13) & _
    ""
   
   'Create and show the Outlook mail item
    Set oApp = CreateObject("Outlook.Application")
    Set oMail = oApp.createitem(0)
    
    
    
    
    
Dim database As DAO.database
Dim table As DAO.Recordset
Dim PONum As String
Dim folder As String
Dim PKey As String
Dim Attachments As Integer

Set database = CurrentDb
Set table = database.OpenRecordset("Files")
    
    
With oMail
        '
        '.To = "To Field"
        .Subject = "Please find attached XXXXXX Files: "
        .body = stText
        
        
        
        
        
With table ' For each record in table
   Do While Not .EOF 'exit with loop at end of table
   Attachments = table.Fields("FileID").Value 'get IDs
   PKey = table.Fields("FullPath").Value 'get list of attachment path and filenames
   .Attachments.Add PKey
   Attachments.MoveNext 'move to next attachment
   .MoveNext 'move to next record
Loop
End With
        
        
        
        
        '.Attachments.Add fileName
        '.Attachments.Add fileName2
        .Display
        '.Send
    End With


    'Application.ScreenUpdating = True
    
    Set oMail = Nothing
    Set oApp = Nothing
    
Exit Sub



End Sub

 

[Gasman]

Upload your db, as .Attachments.Add is correct.
I would not use table as an object name, that might be a reserved name. I tend to use rst prefix eg rstEmails

I have no clue what you think you are doing with Attachments? You just add them?
Never seen a move for them.

If you are using access attachments, then I have never used them, so no experience with them, though I believe they are a special recordset like MVFs which I also never used.

 

[moke123]

I was only ever wanting to point the code to a folder and attach every file contained in it.

Here's how you would get all the files in a folder using FSO.

Sub ListMyFiles(folPath As String)


    Dim fso As Object, fil As Object, fol As Object
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fol = fso.GetFolder(folPath)


    For Each fil In fol.Files


        Debug.Print fil.Path, , , fso.GetFileName(fil.Path)
        
    Next


End Sub

 

[FrostByte]

Here's how you would get all the files in a folder using FSO.

Sub ListMyFiles(folPath As String)


    Dim fso As Object, fil As Object, fol As Object

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fol = fso.GetFolder(folPath)


    For Each fil In fol.Files


        Debug.Print fil.Path, , , fso.GetFileName(fil.Path)
    
    Next


End Sub

Hey all... thank everyone so much I finely managed to get a working model of what I wanted. This code will auto attach everything in the given directory. "Set fol = fso.GetFolder(Path2 & "\SubmissionDocs" & "\")"

Dim fso As Object, fil As Object, fol As Object
 
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fol = fso.GetFolder(Path2 & "\SubmissionDocs" & "\")
 


Set oLook = CreateObject("Outlook.Application")
Set oMail = oLook.CreateItem(0)
Set FD = Application.FileDialog(3)

With oMail
        .To = varTo
        .CC = ""
        .BCC = ""
        .Subject = stSubject
        .Body = stText


For Each fil In fol.Files

        strname = fil
        .Attachments.Add strname
     
    Next



.Display

End With

Set FD = Nothing
Set oMail = Nothing
Set oLook = Nothing

This code will do the same thing but prompt to select certain files.

strname = Path2 & "\SubmissionDocs" & "\"


Set oLook = CreateObject("Outlook.Application")
Set oMail = oLook.CreateItem(0)
Set FD = Application.FileDialog(3)

With oMail
        .To = varTo
        .CC = ""
        .BCC = ""
        .Subject = stSubject
        .Body = stText



FD.AllowMultiSelect = True
FD.Filters.Clear
FD.Filters.Add "All Files", "*.*"
FD.InitialFileName = strname


If FD.Show = True Then
For Each vrtSelectedItem In FD.SelectedItems
.Attachments.Add vrtSelectedItem
Next
End If



.Display

End With

Set FD = Nothing
Set oMail = Nothing
Set oLook = Nothing

 

[cheekybuddha]

as .Attachments.Add is correct

It's not correct in this instance - it's within a With table block, so needs the omail to qualify it.

But the code has evolved, I see.

 

[NauticalGent]

I thought this was ForestByte(Bastanu) at first because of the name. I was extremely confused because this is something he is more than capable of doing - and the avatar is different. Going to have to ensure I have at least 2 cups of coffee before I start reading AWF...!

 

[moke123]

ForerstBytes(Bastanu)

Better make it 3 cups to improve your spelling. ForestByte

 

[NauticalGent]

Better make it 3 cups to improve your spelling. ForestByte

Which one of these emojis signify the "bird"...?

 

[moke123]

Which one of these emojis signify the "bird"...?

This Bird?

 

[NauticalGent]

That's the one!