We help IT Professionals succeed at work.

Send email lotus notes / VBA

Attached i have a file where i send emails with attachements to users.

For some reason, the stfilename1 is not picking up the array ("GB-CORP", "GB-FI", "CMB-LC", "MME") and therefore the correct file name for stfilename1, stfilename2 etc is not producing the correct filename

So these files should be picked up

Smith, John - MME (Mar-15) - Booked .pdf
Smith, John - CMB-LC (Mar-15) - Booked.xlsx

But stfilename1 = Smith, John - (Mar15)  - Managed .pdf

So no files are sent.

Can anyone see why this is?

Thanks
Burst-Tool-EE.xlsm
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016

Commented:
Hi,
the code here tests whether one of these file version exists with GB-CORP, GB-FI,  etc.
For Each stItem In Array("GB-CORP", "GB-FI", "CMB-LC", "CMB - MME")
    stFilenameTmp = str & " - " & stItem & " (" & Format(Cells(5, 15).Value, "mmm-yy") & ") - Managed .pdf"
    If Len(Dir(stpath & "\" & stFilenameTmp)) > 0 Then
        stComp = stItem
        Exit For
    End If
Next

Open in new window


pls make sure the file neame is correct ( for example should a space be between Managed and the ".pdf"

Regards

Author

Commented:
Hi Rgonzo,

The code does work for me. I have attached my file.

So i need the code to attach all files related to the names in Col K.

Managed/Booked pdfs and excel.

Currently the code does not recognise these names, and is not sending the files.

I have attached the file and the directory to wheere i am looking for the files

Thanks
Seamus
Capture.PNG
Burst-Tool-EE.xlsm
CERTIFIED EXPERT
Top Expert 2016

Commented:
pls place

    Debug.Print stpath & "\" & stfilenametmp

after
    stfilenametmp = str & " - " & stItem & " (" & Format(Cells(5, 15).Value, "mmm-yy") & ") - Managed .pdf"


to see in the immediate window  the different files it tries to find and compare them to your file

Author

Commented:
Sorry Rgonzo i was on holiday and had no access to emails.

I put the following code in and i an getting the below path for all 6 filenames.

Seeing as there is Array("GB-CORP", "GB-FI", "CMB-LC", "MME") i dont understand why the code only looks for MME?

Thanks

X:\ABC\Horis Info\Horis_Project\GBM\Mar-15\Output\Middle East\All\Smith, John - MME (Mar-15) - Managed .pdf

For Each stItem In Array("GB-CORP", "GB-FI", "CMB-LC", "MME")
    stfilenametmp = str & " - " & stItem & " (" & Format(Cells(5, 15).Value, "mmm-yy") & ") - Managed .pdf"
    If Len(Dir(stpath & "\" & stfilenametmp)) > 0 Then
        stComp = stItem
        Exit For
    End If
Next
    stfilename1 = str & " - " & stComp & " (" & Format(Cells(5, 15).Value, "mmm-yy") & ") - Managed .pdf"
    Debug.Print stpath & "\" & stfilenametmp
    stfilename2 = str & " - " & stComp & " (" & Format(Cells(5, 15).Value, "mmm-yy") & ") - Booked .pdf"
    Debug.Print stpath & "\" & stfilenametmp
    stfilename3 = str & " - " & stComp & " (" & Format(Cells(5, 15).Value, "mmm-yy") & ") - Booked Region .pdf"
    Debug.Print stpath & "\" & stfilenametmp
    stfilename4 = str & " - " & stComp & " (" & Format(Cells(5, 15).Value, "mmm-yy") & ") - Managed.xlsx"
    Debug.Print stpath & "\" & stfilenametmp
    stfilename5 = str & " - " & stComp & " (" & Format(Cells(5, 15).Value, "mmm-yy") & ") - Booked.xlsx"
    Debug.Print stpath & "\" & stfilenametmp
    stfilename6 = str & " - " & stComp & " (" & Format(Cells(5, 15).Value, "mmm-yy") & ") - Booked Region.xlsx"
    Debug.Print stpath & "\" & stfilenametmp

Open in new window

CERTIFIED EXPERT
Top Expert 2016
Commented:
On line 3 we have  

If Len(Dir(stpath & "\" & stfilenametmp)) > 0 Then

to test the current path and folder if one of these exists

Author

Commented:
Thanks!