[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Help with VBScript email wild card attachment

Posted on 2011-10-21
12
Medium Priority
?
1,512 Views
Last Modified: 2012-05-12
I have automated excel reports that generate each day and are placed in a specific folder on my network.  I have a VBScript that I use to email these reports automatically. The files names are the names of the users who get the report with the exception there is a random number as part of the file name.  So the file name changes partially each day.  How can I add a wildcard script to attach the file based only on the user name in the file name?  My current script is only looking for a specific file name.  Here is my script.

Set objMessage = CreateObject("CDO.Message")

HTMLMessage = "Reports Attached"

objMessage.Subject = "XXX Reports"
'objMessage.Sender = "John Doe"
objMessage.From = "Doe"
objMessage.To = "email@business.com"
objMessage.HTMLBody = HTMLMessage
objMessage.AddAttachment "c:\temp\1234johndoe@business.com.xls"
objMessage.Send
0
Comment
Question by:filbunk
  • 6
  • 4
  • 2
12 Comments
 
LVL 59

Expert Comment

by:Bill Prew
ID: 37008706
Will there be multiple files for the same user, with different random numbers?  If so, how will you know which one needs to be mailed?

~bp
0
 

Author Comment

by:filbunk
ID: 37008746
Some users will have multiple files but most will only have one file.  Each user is the same every day.  The file is named something like this "12345johndoe@business.com.xls.  The next day the file for this user is named "64758johndoe@business.com.xls.  Once the files have been distributed each day, I clear out the directory and it is empty for the next day's reports.  Does this make sense?
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 37008766
Assuming there is only one file then try adding a funtion for example add the function below then use:


objMessage.AddAttachment findfile("c:\temp", "johndoe@business.com.xls")
instead of
objMessage.AddAttachment "c:\temp\1234johndoe@business.com.xls"

Chris
Function findfile(strFolder, strFileName)
Dim fso
Dim fil

    Set fso = CreateObject("scripting.filesystemobject")
    For Each fil In fso.getfolder(strFolder).Files
        If LCase(fil.Name) Like "*" & strFileName Then
            findfile = fil.Path
        End If
    Next
End Function

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 37008771
Forgot a line to improve efficiency!

Chris
Function findfile(strFolder, strFileName)
Dim fso
Dim fil

    Set fso = CreateObject("scripting.filesystemobject")
    For Each fil In fso.getfolder(strFolder).Files
        If LCase(fil.Name) Like "*" & strFileName Then
            findfile = fil.Path
            Exit For
        End If
    Next
End Function

Open in new window

0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 37008838
Tweaking it a bit to allow for multiple similarly named files means:

Note same name so the call is the same but now it returns the file with the 'highest name rather than the first file found.

note 125johndoe@business.com.xls
>
1233johndoe@business.com.xls"

but
1234johndoe@business.com.xls"
>
1233johndoe@business.com.xls"

Chris
Function findfile(strFolder, strFileName)
Dim fso
Dim fil

    Set fso = CreateObject("scripting.filesystemobject")
    For Each fil In fso.getfolder(strFolder).Files
        If LCase(fil.Name) Like "*" & strFileName Then
            If fil.Path > findfile Then _
                findfile = fil.Path
        End If
    Next
End Function

Open in new window

0
 
LVL 59

Expert Comment

by:Bill Prew
ID: 37008840
A risk I see with Chris's approach is false substring matches.  It may not be an issue in your environment today, but be aware that if you had usernames of:

bsmith@business.com
absmith@business.com

then when searching for "bsmith@business.com" either would be a valid match, so you could pick up the wrong file.

If the random number if always left justified and the same number of positions then you might consider removing it before the test, rather than the wildcard approach, as in:

Function findfile(strFolder, strFileName)
Dim fso, fil
    Set fso = CreateObject("scripting.filesystemobject")
    For Each fil In fso.getfolder(strFolder).Files
        If LCase(Mid(fil.Name, 5)) = LCase(strFileName) Then
            findfile = fil.Path
            Exit For
        End If
    Next
End Function

Open in new window

~bp
0
 

Author Comment

by:filbunk
ID: 37008888
I have very few users and the full user name is always in the file name so I don't think there will be an issue in my environment for picking up the wrong file.  You both have helped me tremendously.  I am not a VB scripter, merely just an admin trying to make things more efficient for my network.  Can you guide me as to what I need to fill in for the above examples?  In other words, where in these scripts do I insert my network, file or user information?
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 37008909
The following is improved to allow for the risk Bill identified and to cater for multiple files at the time i.e. it's morre resilient.  It is called as I said above by:

using the line
objMessage.AddAttachment findfile("c:\temp", "johndoe@business.com.xls")
instead of
objMessage.AddAttachment "c:\temp\1234johndoe@business.com.xls"

Function findfile(strFolder, strFileName)
Dim fso
Dim fil
Dim ver

    Set fso = CreateObject("scripting.filesystemobject")
    For Each fil In fso.getfolder(strFolder).Files
        ver = Replace(fil.Name, strFileName, "", 1, , 1)
        If LCase(Mid(fil.Name, 5)) = LCase(strFileName) And IsNumeric(ver) Then
            If fil.Path > findfile Then _
                findfile = fil.Path
        End If
    Next
End Function

Open in new window

0
 

Author Comment

by:filbunk
ID: 37008987
Ok...I compiled the script but got the following error:  <18,9> Microsoft VBScript runtime error: Type mismatch.  Here is the script.  Any suggestions?




Set objMessage = CreateObject("CDO.Message")

HTMLMessage = "Reports Attached"

objMessage.Subject = " Reports"
'objMessage.Sender = "xxxx xxxxr"
objMessage.From = "Hxxx"
objMessage.To = "john.doer@business.com"
objMessage.HTMLBody = HTMLMessage
objMessage.AddAttachment findfile("c:\temp","xxxx.xxxx@business.com.xls")
Function findfile(strFolder, strFileName)
Dim fso
Dim fil
Dim ver

    Set fso = CreateObject("scripting.filesystemobject")
    For Each fil In fso.getfolder(strFolder).Files
        ver = Replace(fil.Name, strFileName, "", 1, , 1)
        If LCase(Mid(fil.Name, 5)) = LCase(strFileName) And IsNumeric(ver) Then
            If fil.Path > findfile Then _
                findfile = fil.Path
        End If
    Next
End Function
objMailItem.Send
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 37009057
Apologies it errors out when the string is not found ... see below for that correction:

Chris
Function findfile(strFolder, strFileName)
Dim fso
Dim fil
Dim ver

    Set fso = CreateObject("scripting.filesystemobject")
    On Error Resume next
    For Each fil In fso.getfolder(strFolder).Files
        ver = Replace(fil.Name, strFileName, "", 1, , 1)
        If LCase(Mid(fil.Name, 5)) = LCase(strFileName) And IsNumeric(ver) Then
            If fil.Path > findfile Then _
                findfile = fil.Path
        End If
    Next
End Function

Open in new window

0
 

Author Comment

by:filbunk
ID: 37009146
Well the script worked and I received the email but the attachment was not the file.  The attachment was an empty .dat file. ???
0
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 2000 total points
ID: 37010292
Added a msgbox to temporarily see what is being returned. ... assuming the file name is correct then let it continue and what happens?

Chris
Set objMessage = CreateObject("CDO.Message")

HTMLMessage = "Reports Attached"

objMessage.Subject = " Reports"
'objMessage.Sender = "xxxx xxxxr"
objMessage.From = "Hxxx"
objMessage.To = "john.doer@business.com"
objMessage.HTMLBody = HTMLMessage
strFile = cstr(findfile("c:\temp","johndoe@business.com.xls"))
MsgBox strfile
objMessage.AddAttachment strfile
objMessage.Send
Function findfile(strFolder, strFileName)
Dim fso
Dim fil
Dim ver

    Set fso = CreateObject("scripting.filesystemobject")
	On Error Resume next
    For Each fil In fso.getfolder(strFolder).Files
        ver = Replace(fil.Name, strFileName, "", 1, , 1)
        If LCase(Mid(fil.Name, 5)) = LCase(strFileName) And IsNumeric(ver) Then
            If fil.Path > findfile Then _
                findfile = fil.Path
        End If
    Next
End Function

Open in new window

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Screencast - Getting to Know the Pipeline

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question