Macro to attach Files

[howard]

I have a macro below to allow a user attach files in a folder C:\fixed assets

I need the code amended to attached specfic files for eg BR1 Fixed Assets.xls, Socom fixed Assets.xlsx etc

It would be appreciated if someone could kindly amend my code so as to attach specific files in C:\fixed assets

 Sub Attachfiles_AndEmail()

                     
ztext = [bodytext]
Zsubject = [subjectText]
Dim fName As String
Dim OutApp As Object
Dim OutMail As Object
Dim Strpath As String
Dim Strfile As String
Dim FD As Object
Dim vrtSelectedItem As Variant

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.createitem(0)
      Set FD = Application.FileDialog(3)
    
        With OutMail
           .To = Join(Application.Transpose(Sheets("Macro").Range("N1:N2").Value), ";")
           .CC = ""
           .BCC = ""
           .Subject = Zsubject
           .Body = ztext
              FD.AllowMultiSelect = True
                FD.Filters.Clear
                FD.Filters.Add "All Files", "*.*"
                FD.InitialFileName = "C:\Fixed Assets\"

               If FD.Show = True Then
       For Each vrtSelectedItem In FD.SelectedItems
       .Attachments.Add vrtSelectedItem
       Next
    End If
           .Display
        End With
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

 

[rollis13]

Have a try with these few changes to your macro, for the "ecc." just add other file names to the array:

Option Explicit
Sub Attachfiles_AndEmail()
    '    Dim ztext  As String
    '    Dim zsubject As String
    ztext = [bodytext]
    zsubject = [subjectText]
    Dim OutApp As Object
    Dim OutMail As Object
    Dim Strpath As String
    Dim fileArray As Variant
    Dim x      As Integer
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.createitem(0)
    Strpath = "C:\Fixed Assets\"
    fileArray = Array("BR1 Fixed Assets.xls", "Socom fixed Assets.xlsx") '<- add files as needed
    With OutMail
        .To = Join(Application.Transpose(Sheets("Macro").Range("N1:N2").Value), ";")
        .CC = ""
        .BCC = ""
        .Subject = zsubject
        .Body = ztext
        For x = LBound(fileArray) To UBound(fileArray)
            .Attachments.Add Strpath & fileArray(x)
        Next
        .Display
    End With
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

 

[howard]

thanks for the help Rollis13

I have several files for eg BRgroup Fixed asset Consolidation 2021.2021.xls, BTY Fixed asset Consolidation 2021.2022.xls etc.

i tried to use a wildcard "*Consolidation*.*" in the array, but get a run time error "filename or director name is not valid". If i insert the full file name into the array it works

Kindly advise how to make use of the wilcard using an array

 

[rollis13]

If you have a lot of files it would be easier to list them somewhere in a sheet and with a loop populate the array. But then, even without an array, the For/Next related to the function .Attachments.Add would do the job (read from the list instead of from the array).

 

[howard]

How do refer to the list on a sheet with a loop to populate the array ?

 

[rollis13]

As said, even without array, just change this part of your macro (supposing that the list of file names starts in A1 of Sheet1):

'...
.Body = ztext
Dim LastRow    As Long
LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row '<- change sheet name and range as needed
For x = 1 To LastRow
    .Attachments.Add Strpath & Sheets("Sheet1").Range("A" & x).Value
Next x
.Display
'...

and get rid of the fileArray.