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
Seamus2626Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
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
0
Seamus2626Author 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
0
Rgonzo1971Commented:
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
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Seamus2626Author 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

0
Rgonzo1971Commented:
On line 3 we have  

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

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Seamus2626Author Commented:
Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Lotus IBM

From novice to tech pro — start learning today.