• 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.

How To Skip File in folder when it matchs the critieria specified [VBA] [SOLVED]

vijay.vizzu

vijay.vizzu

Member
Dear All

I want to skip a file selection, when the file name starts with QP. Below is my code, The problem is that file names which are starts with after Q, then the files not selected, and code exit, So i need a code, it need to send all files except the file name starts with QP in that folder.

[pre]
Code: 
Public Sub SendMail()
Dim fso As New FileSystemObject
Dim myFolder As Folder
Dim myFile As File
Dim OutApp As New Outlook.Application
Dim myMail As Outlook.MailItem
Dim Email As String

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

With Application
.ScreenUpdating = False
.EnableEvents = False
End With
For Each myFile In myFolder.Files
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"
Case "NIT"
Email = "NDua@yamaha-motor-india.com"
Case "PPS"
Email = "PPSingh@yamaha-motor-india.com"
Case "RAJ"
Email = "RajdevSRawat@yamaha-motor-india.com"
Case "RAK"
Email = "RakeshY@yamaha-motor-india.com"
Case Else
Exit For
End Select
Debug.Print Email

Set myMail = OutApp.CreateItem(olMailItem)
With myMail
.To = Email
.CC = "RRArora@yamaha-motor-india.com;TKuranaga@yamaha-motor-india.com"
.Subject = "Quote Pendency Summary " & UCase(Format(Now, "dd-mmm-yy"))
.Body = "Dear Sir" & vbCrLf & vbCrLf & "Please find attached Quote Pendency for your reference"
.Attachments.Add (myFile)
.Send
End With
Next

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub
[/pre]
 
How about this slight modification?

[pre]
Code: 
Public Sub SendMail()
Dim fso As New FileSystemObject
Dim myFolder As Folder
Dim myFile As File
Dim OutApp As New Outlook.Application
Dim myMail As Outlook.MailItem
Dim Email As String

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

With Application
.ScreenUpdating = False
.EnableEvents = False
End With
For Each myFile In myFolder.Files
Debug.Print myFile.Name
If Left(myFile.Name, 2) = "QP" Then GoTo skipMe 'New line
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"
Case "NIT"
Email = "NDua@yamaha-motor-india.com"
Case "PPS"
Email = "PPSingh@yamaha-motor-india.com"
Case "RAJ"
Email = "RajdevSRawat@yamaha-motor-india.com"
Case "RAK"
Email = "RakeshY@yamaha-motor-india.com"
Case Else
Exit For
End Select
Debug.Print Email

Set myMail = OutApp.CreateItem(olMailItem)
With myMail
.To = Email
.CC = "RRArora@yamaha-motor-india.com;TKuranaga@yamaha-motor-india.com"
.Subject = "Quote Pendency Summary " & UCase(Format(Now, "dd-mmm-yy"))
.Body = "Dear Sir" & vbCrLf & vbCrLf & "Please find attached Quote Pendency for your reference"
.Attachments.Add (myFile)
.Send
End With
skipMe:
Next

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub
[/pre]
 
This topic is the same as last question of this, just a week of difference:

http://chandoo.org/forums/topic/problem-in-select-case-statment


And if it isn't exactly the same you should have continued the original thread so as to don't create an unnecessary dispersion.


And if it is, then...


What you're doing "is called cross-posting and is generally frowned upon in the Blogosphere as it causes people to potentially waste our time when a question is already answered. You should also check and respond to posts and let posters know if they are heading in the write direction or not." Hui's dixit, SIC. And I agree 101%.


You're not a newbie at these forums, more than a year and a half, so you must be aware of cross posting, aren't you? If you aren't, which I strongly doubt, for i=1 to 1000:read green sticky posts: next i... and then act accordingly.


Just in case you need a little push towards them, here they are:


If you'd have read the main green sticky post at this forums main page...

http://chandoo.org/forums/topic/phd-forum-posting-rules-etiquette-pls-read-before-posting

...you should have noticed this points:


"Consider that the world is operating 24hrs a day. A late post today may well be answered by someone else overnight."


"If you and a reader have been involved in an ongoing conversation and the conversation suddenly stops, recognize that the person may have gone to bed, even though you have just arrived at work. In the worst case a reader may go on holidays and not get back to the question for a few days."


"Never title your posts as "Urgent", "Priority" "Immediate". It may be Important to you, but not for rest of the members here. These words will be moderated out."


"Cross Posting, generally it is considered poor practice to cross post, that is to post the same question on several forums in the hope of getting a response quicker.

If you do cross post, please put that in your post.

Also if you have cross posted and get an answer elsewhere, have the courtesy of posting the answer here so other readers can learn from the answer also, as well as stopping people wasting there time on your answered question."


"Say "Thanks", whenever you can. Recognize when someone has bothered to go to the trouble and time to assist you with your question for free. Often readers will spend several hours working on a solution to a problem, a line of recognition will go a long way."
 
Hi, vijay.vizzu!


BTW, I assume that the email addresses of third parties that you posted:

"HChaurasia@yamaha-motor-india.com"

"RKapoor@yamaha-motor-india.com"

"NSrivastava@yamaha-motor-india.com"

"NDua@yamaha-motor-india.com"

"PPSingh@yamaha-motor-india.com"

"RajdevSRawat@yamaha-motor-india.com"

"RakeshY@yamaha-motor-india.com"

are either all fictitious or their holders (and others at the corporation) are aware of the public post of them and of course they've authorized you to do so.


Regards!


PS: I was just wondering what if someone who read this topic thought about sending a mail to those addresses (or perhaps trying with other addresses from the same domain) including a link to it. But then I realized that it wouldn't cause any harm at all considering what stated in the previous paragraph.
 
Thank you so much Luke, Very interesting to skip the operation with Label option, i never think like you, Great


Dear Sirjb7, Initially when i started this post, i already mentioned that it is a one of the part in my project, and that time, my query is about "SELECT CASE" and now "SKIP" option, i thought why did i confuse the others, i just need to focus their skill/advice on the problem, not on the total phenomena. For that reason, i cross posted


Extremely sorry... i will follow your instructions by heart and mind


Once again thanks to all of you guys
 
No worries vijay, glad to help.

And don't mind SirJB7 too much...I think he just sounds angrier than he really is. =P
 
@SirJB7

Hi, myself!

So long...


Don't I look, sound and feel angelic, demure, diplomatic and sweet as always?


Note to self 1: remind Luke M to review his perception system and adjust the sensitiveness threshold.


Note to self 2: don't care too much about users privacy and don't warn them so much about the risks of making sensitive data publicly available... no matter even with the spam activity level and the robots that might (are!) collecting data for their spam databases.


Note so self 3: write down that new word (once again your non native english, I guess), ... angrier... for searching it in the dictionary and of course validating the result against Google Traductor :)


Regards!


PS:


Note to self 4: don't forget asking Luke M to post his team email addresses.
 
@Luke M

Hi, my friend!


You really make me thought... but not much, just a little... so I want to share my doubts with you, may I? Here it goes: don't I look, sound and feel angelic, demure, diplomatic and sweet as always?


Oh, I'm concerned too about the sensitiveness threshold of your perception system, are you sure it's fine tuned?


And last but not least, you know that there are people who collect coins, others postcards, others stamps, ... a friend of mine collect email addresses, not for any obscure and uncertain purpose, just for aiding on sublimation of his OCD, so... would you mind posting your team email addresses? Not all, just one hundred it'd be fine. Thank you on his name in advance.


Regards!
 
@SirJB7


Hello my friend. It wasn't the email addresses that caught my attention, but the growing "wall of text" that appears in many of your posts, such as this one. IMO, when a post gets too long, it stops being helpful because people tend to skim, or skip over it completely.


For the email addresses, I make no judgements in this forum, and like the atmosphere that generates. I just think a simple one-sentence statement of "be careful about posting real email addresses" would have sufficed.


From our interactions over the past years, I've grown to understand more about your wit/sarcasm, but new posters may not always pick up on it. Or, maybe you are correct, and I am just too sensitive. =P This is not meant to be nit-picky, just wanted to explain myself further (and for outside readers who may not know how we interact). Anyway, cheers mate.
 
@Luke M

Hi, dude!


Always a pleasure to interact with you :)


Regarding long posts I considered that option, but since posters to whom I dedicate those not so little texts have precisely omit reading the suggested green sticky posts (and if they didn't it's not probable that they'd do that carefully, at most they'd click on the link and when they get all the original text I bet they'll close it) I intended to don't let them avoid facing the guidelines because of laziness of clicking on links and reading certain parts, so I stamp my "extract" just in front of them... after that they won't be able to say or think "Oh, sorry, I didn't read that or I haven't noticed that part".


About my sweetness, I wouldn't be able to even think that you're referring to it as wit or sarcasm, are you? No, you aren't, I know! It might be just a typo error.


Regards!
 
You must log in or register to reply here.
Back
Top