0

I've listed code where it searches and attaches documents to email. The name value is listed in column A24, Everything works just fine except the below folder path does not look into subfolders; it only picks up and attaches the files if it's listed in the main folder.

I tried putting '& "\"' in the formula for path, but still it didn't work. Can anyone suggest any alternate way to script into folders and subfoders?

My current VBA code is below:

Sub AttachandSendEmail()
Dim obMail As Outlook.MailItem
Dim irow As Integer
Dim dpath As String
Dim pfile As String

'file path
**dpath = "C:\Users\document_location_folder"**

'looping through all the files and sending an mail

irow = 24

Do While Cells(irow, 1) <> Empty


'pikcing up file name from column C
 pfile = Dir(dpath & "\*" & Cells(irow, 1) & "*")


'checking for file exist in a folder and if its a pdf file

If pfile <> "" And Right(pfile, 1) = "pdf" Then

Set obMail = Outlook.CreateItem(olMailItem)
With obMail
.To = "email@comapny.com"
.Subject = "O/S Blanace"
.BodyFormat = olFormatPlain
.Body = "Please see attached files"

Do While Cells(irow, 1) <> Empty
    'pikcing up file name from column C
    pfile = Dir(dpath & "\*" & Cells(irow, 1) & "*")
    'checking for file exist in a folder and if its a pdf file

    If pfile <> "" And Right(pfile, 1) = "pdf" Then
        .Attachments.Add (dpath & "\" & pfile)
    End If

    'go to next file listed on the C column
    irow = irow + 1
Loop

.Send
End With

End Sub
0m3r
  • 12,286
  • 15
  • 35
  • 71
PraSon
  • 29
  • 2
  • 11
  • You need to [iterate through all subfolders](https://stackoverflow.com/questions/22645347/loop-through-all-subfolders-using-vba#22645439) – cybernetic.nomad Feb 20 '18 at 04:58
  • @cybernetic.nomad. Thank you for your comment but that did not work at all. It didn't do anything there was no output. – PraSon Feb 20 '18 at 05:28
  • 1
    Shouldn't your `Right` function have **3** as its *Length As Long* argument? Otherwise it's always going to evaluate to `FALSE` as `Right(pfile,1)` would equal **p** (I'm assuming, based on your code), in which case it would be a useless test. – TotsieMae Feb 20 '18 at 05:47
  • @TotsieMae can you elaborate the False as Right(pfile, 1)? Because, currently this works just fine as it attaches the documents from the listed folder path in Dpath. I just need to work it in subfolder too – PraSon Feb 20 '18 at 06:21
  • 1
    The way the `Right` function is currently written, it is only looking at the very last letter from **pfile**, which means it could never equal **pdf** (1 letter in the function versus 3 letters in "pdf"), and would therefore be `FALSE`. As your logical statement uses **And**, both `pfile <> ""` *and* `Right(pile, 1) = "pdf"` must evaluate to `TRUE`, or else files from `(dpath & "\" & pfile)` won't ever attach to the e-mail. Given that `Right(pile, 1) = "pdf"` won't ever be true in this code, you won't ever get those specific files to attach. – TotsieMae Feb 20 '18 at 06:55
  • Read more about the `Right` function [here](https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/right-function). Also, what happens when you step through the code using `F8`? Does it get tripped up somewhere? Where? – TotsieMae Feb 20 '18 at 06:58
  • @TotsieMae I've chnaged it to Right (pfile, 3) = 'pdf' and still wont pickup the file from subfolder. It does picks it from mastar folder listed the path in dpath. but not the folder within that folder. – PraSon Feb 22 '18 at 00:37
  • @PraSon, there are many issues with your code. When I put it - as written - into my VBE and then compile, I'm returned several errors. For example, you have an `If` statement with no `End If`as well as a `Do While` with no closing `Loop`. Each of your `If` statements needs its own `End If` and each `Do While` needs its own `Loop`. I'm not sure how you were able to get this to run correctly as it's currently written, but if you can provide further clarification, we may be able to better assist you. – TotsieMae Feb 22 '18 at 16:42

0 Answers0