Like operator with email attachment.

[Brando]

Hi all,
I'm trying to attach a invoice (pdf) to an email with vba and I want the code to grab the invoice regardless what else might be in the document name, such as "Invoice 1" or "Invoice approved", etc.. The invoice is always a pdf file.

This code works. It grabs the invoice as long as it is just called "Invoice":

.Attachments.Add "\\NetworkLocation\data\Admin\Environmental" & "\Invoice.pdf

This code doesn't work because it doesn't find "Invoice 1":

.Attachments.Add "\\NetworkLocation\data\Admin\Environmental" & "\" Like "Invoice*"

Any help is appreciated.

Brando

 

[June7]

Can't use wildcard, must specify exact file name. Will there be only the one invoice PDF in that folder? Loop through folder files to find one with "Invoice" in name.

    Dim strFile As String
    strFile = Dir("\\NetworkLocation\data\Admin\Environmental\*.pdf")
    Do While strFile <> ""
        If strFile LIKE "*Invoice*" Then
            .Attachments.Add "\\NetworkLocation\data\Admin\Environmental\" & strFile
            Exit Do
        End If
        strFile = Dir
    Loop

 

[Brando]

'I'm getting a Compile error on "Exit Loop": Expected: Do or For or Sub or Function or Property.
'Here is the code as it stands...


Private Sub txtEnvDocsSent_DblClick(Cancel As Integer)

On Error Resume Next
strTodaysDate = Date

Dim OApp As Object, OMail As Object, Email1 As String, Email2 As String

Set OApp = CreateObject("Outlook.Application")
Set OMail = OApp.CreateItem(0)
With OMail
.Display
End With
Email1 = "Accounts Payable (Finance)"
With OMail
.To = Email1
.Subject = "Invoice submission"

Dim strFile As String
strFile = Dir("\\filer6\data\RE_Admin\Appraisal Library\" & Year(EnvReviewComplete) & "\" & (Library) & "\Environmental" & "\*.pdf")
Do While strFile <> ""
If strFile Like "*Invoice*" Then
.Attachments.Add "\\filer6\data\RE_Admin\Appraisal Library\" & Year(EnvReviewComplete) & "\" & (Library) & "\Environmental\" & strFile
Exit Loop
End If
strFile = Dir
Loop

.Body = "INVOICE SUBMISSION" & vbNewLine & "The attached invoice is ok to pay from: " & Me!Suspense1 & vbNewLine & vbNewLine & "Please contact " & Me!Assoc1 & " with any questions." & vbNewLine & vbNewLine & "Thank you!"
'.Send
End With

Set OMail = Nothing
Set OApp = Nothing

txtEnvDocsSent = strTodaysDate

End Sub

 

[June7]

Sorry for brain fart. Should be Exit Do. Edited prior post.

This is not required, just prevents continuing to loop through files once a file meeting criteria is found.

 

[Cronk]

Or a bit simpler

strFile = Dir("\\filer6\data\RE_Admin\Appraisal Library\" & Year(EnvReviewComplete) & "\" & (Library) & "\Environmental" & "\*Invoice*.pdf") Do While strFile <> "" .Attachments.Add "\\filer6\data\RE_Admin\Appraisal Library\" & Year(EnvReviewComplete) & "\" & (Library) & "\Environmental\" & strFile Loop

PS we assume Library is a valid variable or a control in the form

 

[Brando]

Thank you June7 and Cronk! The code works perfectly with the minor fix from June7. For some reason, the simpler version from Cronk caused the attachment to be added repeatedly until the db froze. Maybe it needs an Until clause?

Anyway, I'm afraid to ask an additional question... is there a way to have this routine grab more than one invoice? Example: both "Invoice 1" and "Invoice 2"

Brando

 

[Cronk]

Sorry, I left out a line.

Should be

strFile = Dir("\\filer6\data\RE_Admin\Appraisal Library\" & Year(EnvReviewComplete) & "\" & (Library) & "\Environmental" & "\*Invoice*.pdf")
Do While strFile <> ""
   .Attachments.Add "\\filer6\data\RE_Admin\Appraisal Library\" & Year(EnvReviewComplete) & "\" & (Library) & "\Environmental\" & strFile
   strFile=Dir
Loop

 

[June7]

What criteria would be used in conditional to allow code to continue grabbing files? How should it 'know' you want Invoice 1 and Invoice 2? How many invoices pdfs are in folder? Do you want them all? Are there other files in folder?