• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Problem in Select Case Statment [SOLVED]

vijay.vizzu

vijay.vizzu

Member
Hi All

I am trying to write a VBA code which can select emailid based upon filename and then send mails to the concerned person. So i am going to use Select Case Statment, but it can't work. I am unable to solve the problem as i am new to VBA. So please help to resolve my problem. I have tried a lot but can't able to resolve

[pre]
Code: 
Sub Createmail()
Dim OutApp As Object
Dim MailItem As Object
Dim Fso As Object
Dim myFolder As Object
Dim myFilename As Object
Dim EmailID As String

Set Fso = CreateObject("scripting.filesystemobject")
Set myFolder = Fso.getfolder("E:project")
For Each myFilename In myFolder.Files
Select Case myFilename
Case InStr(Len(myFolder), myFilename, "Man", vbBinaryCompare) > 0
EmailID = "h@ymail.com"
Case InStr(Len(myFolder), myFilename, "Ven", vbBinaryCompare) > 0
EmailID = "v@ymail.com"
Case Else
MsgBox "Not Found"
End Select
Next

Set OutApp = CreateObject("outlook.application")
Set MailItem = OutApp.createitem(0)

With MailItem
.to = EmailID
.Subject = "This is test"
.body = "Please find attachment"
.display
End With

Set OutApp = Nothing
Set MailItem = Nothing
End Sub
[/pre]
 
Vijay - an explanation of the actual problem would help. Are you getting an error? If so, which one. If not, what is or is not going according to plan?


To quote one of the ninjas (can't remember which one)...if you need help with a problem then you've got to give till it hurts.
 
Hi Vijay ,


You can understand the Select Case statement from here :


http://www.techonthenet.com/excel/formulas/case.php


The conditions used in each Case segment have to relate to the expression used in the Select Case line ; this is not being fulfilled in your example , since the INSTR function is not directly related to myFilename , but to a comparison of whether the substring "Man" and "Ven" occur in myFilename.


One way to get what you want done is to replace the Select Case statement with the following IF statement :

[pre]
Code: 
If InStr(Len(myFolder), myFilename, "Man", vbBinaryCompare) > 0 Then
EmailID = "h@ymail.com"
ElseIf InStr(Len(myFolder), myFilename, "Ven", vbBinaryCompare) > 0 Then
EmailID = "v@ymail.com"
Else
MsgBox "Not Found"
End If
[/pre]
Narayan
 
Dear All


Can you please rectify, what is the problem in below code


Sub SendMail()

Dim fso As New FileSystemObject

Dim myFolder As Folder

Dim myFile As File

Dim OutApp As Outlook.Application

Dim Attach As Variant

Dim Email As String


Set myFolder = fso.GetFolder("W:Quote Pendency")


On Error Resume Next

For Each myFile In myFolder.Files

Debug.Print myFile.Name

Select Case myFile.Name

Case Left(myFile.Name, 3) = "HAR"

Email = "HChaurasia@yamaha-motor-india.com"

Case Left(myFile.Name, 3) = "KAP"

Email = "RKapoor@yamaha-motor-india.com"

Case Left(myFile.Name, 3) = "NIK"

Email = "NSrivastava@yamaha-motor-india.com"

Case Left(myFile.Name, 3) = "NIT"

Email = "NDua@yamaha-motor-india.com"

Case Left(myFile.Name, 3) = "PPS"

Email = "PPSingh@yamaha-motor-india.com"

Case Left(myFile.Name, 3) = "RAJ"

Email = "RajdevSRawat@yamaha-motor-india.com"

Case Else

Email = "RakeshY@yamaha-motor-india.com"

End Select

Debug.Print Email

Next

End Sub
 
Hi, vijay.vizzu!

Could you post the 1st 20 or 30 lines that you got printed in the Immediate window?

Ooops...! You're missing a dot in the Select Case statement. For avoiding this type of errors you should use Option Explicit and compile the code before testing.

Regards!
 
Dear Sirjb7


Thank you so much, Please find below filenames which are printed by Debug.Pring myfile.name

HARENDRA27-Jun-13-100638.xlsx

KAPOOR27-Jun-13-100638.xlsx

NIKHIL27-Jun-13-100639.xlsx

NITIN27-Jun-13-100639.xlsx

PPS27-Jun-13-100640.xlsx

RAJDEV27-Jun-13-100640.xlsx

RAKESH27-Jun-13-100641.xlsx


And i have edited the post, which i forget to put (.)
 
Hi, vijay.vizzu!

The line requesting printed values was written before I realized your missing dot, so if it works fine for you the output isn't necessary.

Despite of this the posted data doesn't includes the 2nd Debug.Print statement (Email), so if it doesn't work I couldn't make any further analysis as it's missing the output data.

Regards!
 
Hi,


Shouldn't your
Code: 
Select Case statement be like this?

[pre][code]Debug.Print myFile.Name

Select Case Left(myFile.Name, 3)
Case "HAR"
email = "HChaurasia@yamaha-motor-india.com"
Case "KAP"
email = "RKapoor@yamaha-motor-india.com"
Case "NIK"
email = "NSrivastava@yamaha-motor-india.com"

'etc... etc...
[/pre]
Incidentally, what's the reason for the On Error Resume Next[/code] statement in your code?
 
Dear Colin Legg,


Thanks for your suggest, it works but still i am in confusion, why the previous method like myfile.name not works.


On error resume next i put, becoz my project is in under preparation


thanks

vijay
 
Hi,


The original way you wrote it would be interpreted as follows.


Let's suppose the file name is HARENDRA27-Jun-13-100638.xlsx.


First this line of code:

Code: 
Select Case myFile.Name


Is read as

[code]Select Case "HARENDRA27-Jun-13-100638.xlsx"


This file name is the test expression which the others are going to be compared against.


Then your next line of code is:

[code]Case Left(myFile.Name, 3) = "HAR"


The [code]Left(myFile.Name, 3) = "HAR" statement will return the value [code]True. This [code]True value will be tested for equality against the test expression. In other words, that line becomes:

[code]Case True


This means it asks the question:

does "HARENDRA27-Jun-13-100638.xlsx"
equal True[/code]? The answer is False[/code], so then next Case[/code] expression will be evaluated and so on.


Let's just recap what those two lines of code equates to because it might make it a little clearer:

[pre]Select Case "HARENDRA27-Jun-13-100638.xlsx"
Case True[/code][/pre]
I'm not sure if I've explained that too well, so here's an 'pseudo Excel formula' equivalent:

=IF(myFile.Name=(Left(myFile.Name, 3)="HAR"),email,Next Case Statement)[/code]


Re. the On Error Resume Next[/code]. In my opinion I think you should remove it from your code. In this case it seems that it is just hiding potential problems from you during your development cycle.


Hope that helps...

Colin
 
Cross posted at: http://chandoo.org/forums/topic/how-to-skip-file-selection-in-folder-when-it-matchs-the-critieria-specified-vba
 
You must log in or register to reply here.
Back
Top