• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 204
  • Last Modified:

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
0
Seamus2626
Asked:
Seamus2626
  • 3
  • 3
1 Solution
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
Seamus2626Author Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now