Attach Files with variable names to an Outlook template everyday. 1

[Xeeq]

At my job we send emails out to many different clients. Each email has it's own attachment, and each attachment has confidential information. Considering that we do all of this manually, you could guess that there are a few times a year that we accidentally attach Client1's file to Client2's email. You can see how this is a major problem. I have been assigned to make these slip-ups go away. I decided to use VBScript to do it but I am not a programmer, so I am having trouble with a few things. I have Outlook template files saved to my computer with the "To" and "Body" all ready to go. I also have a folder for each client with "attachments" that I update everyday. I already have a script that will open the templates. I now need script to attach the file to the template and to fill in the "From" section of the email. A couple of issues I have is: 1. Sometimes there are more than one file that I need to attach, so I need VBS to check for those extra files. 2. I have multiple files archived in this folder that I do NOT want to send. 3. Everyone in my office saves these files in this format (CLIENTNAME month day.pdf) The variable file name would just be a word added onto the end after "day". The date will always be yesterday's date. Any help with this would be greatly appriciated. I know there has to be a way to create variables in the script, but I don't know how to do it.

 

[Xeeq]

I see how that could be a problem.


Ok, I changed the test file name to match the format "name-month-day.pdf", and when we add a variable word to the file name we will just put a dash before the variable so it will look like this "name-month-day-var.pdf" Therefor, VBS won't care about the last word because it is undefined, and it wouldn't be part of the day.

After changing that I tried again, and of coarse, nothing. Here is the weird part.....I tweaked around on the code a bit and used that "isfilevalid" function and found that getYesterday() is not equal to "oct-24". I'm not sure why, but it is definitely not. I will continue working on it, let me know if you see something in the code that would cause this. Thanks




The only knowledge is knowing that you know nothing.

 

[Geates]

what is getYesterday returning? use a message boxs to "step" through the function.

function isFileValid(strFileName)
   isFileValid = false
   arrTokens = split(strFileName, "-")
   strMonth = arrTokens(1)
   strDay = arrTokens(2)

   [red]msgbox getYesterday() & vbNewLine & strMonth & "-" & strDay[/red]
   if (getYesterday() = strMonth & "-" & strDay) then
      [red]msgbox "The file is from yesterday"[/red]
      if (right(strFileName, 4) = ".pdf") then
         [red]msgbox "The file is valid"[/red]
         isFileValid = true
      end if
   end if
end function

-Geates

"I hope I can chill and see the change - stop the bleed inside and feel again. Cut the chain of lies you've been feeding my veins; I've got nothing to say to you!"
-Infected Mushroom

"I do not offer answers, only considerations."
- Geates's Disclaimer

 

[Xeeq]

Sorry for the pause in activity, I had a heavy load last week. Ok, so I ran the message box function through and it gives me a message box with

Oct-30
Oct-30.pdf

Then another with the "is NOT valid" message that I love so much.

The bottom file in that message box is the file that it found in the folder. The top is apparantly what it is looking for. I don't understand why it isn't working.




The only knowledge is knowing that you know nothing.

 

[Geates]

As the message box shows, [tt]getYesterday()[/tt] is returning "Oct-30" and [tt]strMonth & "-" & strDay[/tt] is returning "Oct-30.pdf". A does not equal B. This suggests that strDay = "30.pdf" You want strDay to equal "30". The issue is clear. Change either the way you name the pdfs or extract the 30.

Because strDay includes an extension (.pdf), a quick workaround would be to remove the extension

strDay = replace(strDay, ".pdf", "")

-Geates

"I hope I can chill and see the change - stop the bleed inside and feel again. Cut the chain of lies you've been feeding my veins; I've got nothing to say to you!"
-Infected Mushroom

"I do not offer answers, only considerations."
- Geates's Disclaimer

 

[Xeeq]

You were exactly right and that code made it valid, but it still says NOT valid in the first message box we were using.

I have a question (and this could possibly make things much more simple). Is there a way to tell VBS to attach anything that is "Valid" for "GetYesterday" to the message created by this code?

Dim objOutl
Set objOutl = CreateObject("Outlook.Application")
Set objMailItem = objOutl.CreateItem(olMailItem)
objMailItem.Display
strEmailAddr  = ""
objMailItem.Recipients.Add strEmailAddr 
objMailItem.Body = ""
objMailItem.Subject = ""
objMailItem.Cc =  ""
Set objMailItem = nothing
Set objOutl = nothing

Anything in the designated folder that is "valid" for "getyesterday" is what I want to send.




The only knowledge is knowing that you know nothing.

 

[Geates]

That is what the code currently does. Or at least those are the conditions in which I programmed it.

-Geates

"I hope I can chill and see the change - stop the bleed inside and feel again. Cut the chain of lies you've been feeding my veins; I've got nothing to say to you!"
-Infected Mushroom

"I do not offer answers, only considerations."
- Geates's Disclaimer

 

[Xeeq]

You had it set up to attach a file name to a corresponding "CDO.Message". A CDO.message isn't going to work for me. I need these emails to be sent out from my exchange server. I currently have the email creation working fine, and the email is automatically filled out with my code. I simply need to know how to reference the files that "getYesterday" finds as valid files so that I can add them as attachments. Here is the code that I will be using.

 const UNKNOWN = "Chickenless Soup"

dim arrAttachments()

set objFSO = CreateObject("Scripting.FileSystemObject")
set colFiles = objFSO.GetFolder("C:\Users\Lee\Documents").Files
set dicClients = CreateObject("Scripting.Dictionary")


function getYesterday()
select case (month(date - 1))
case 1 : strMonth = "Jan"
case 2 : strMonth = "Feb"
case 3 : strMonth = "Mar"
case 4 : strMonth = "Apr"
case 5 : strMonth = "May"
case 6 : strMonth = "Jun"
case 7 : strMonth = "Jul"
case 8 : strMonth = "Aug"
case 9 : strMonth = "Sept"
case 10 : strMonth = "Oct"
case 11 : strMonth = "Nov"
case 12 : strMonth = "Dec"
end select



getYesterday = strMonth & "-" & (day(date - 1))
end function



function isFileValid(strFileName)
isFileValid = false
arrTokens = split(strFileName, "-")
strMonth = arrTokens(1)
strDay = arrTokens(2)


if (getYesterday() = strMonth & "-" & strDay) then
if (right(strFileName, 4) = ".pdf") then
isFileValid = true
end if
end if
end function


strDay = replace(strDay, ".pdf", "")


Dim objOutl
Set objOutl = CreateObject("Outlook.Application")
Set objMailItem = objOutl.CreateItem(olMailItem)
objMailItem.Display
strEmailAddr  = "Lots of email addresses"
objMailItem.Recipients.Add strEmailAddr 
objMailItem.Body = "Stuff"
objMailItem.Subject = "Howdy"
objMailItem.Cc =  "More email addresses"
strAttachment = ****I JUST NEED TO KNOW WHAT TO PUT HERE****
objMailItem.Attachments.Add strAttachment
Set objMailItem = nothing
Set objOutl = nothing

I don't know how to reference the "getYesterday" files, but whatever it decides is from yesterday is what I want to attach. I previously didn't realize that it could be broken down to that simple, but any file with yesterday's date was sent out this morning. Today's files will be sent out tomorrow, and so on.

The rest of the code you wrote for me I saved in another file, and I am going to come back to that later. For now I have to get the basics set up.




The only knowledge is knowing that you know nothing.

 

[Geates]

the function needs to return something for it to work. All getYesterday does is return yesterday (date - 1). It is then used to see if a file contains the dat that getYesterday() returns.

function getYesterday()
   ...
   case 11 : strMonth = "Nov"
   case 12 : strMonth = "Dec"
   [red]getYesterday = strMonth & "-" & strDay[/red]
end select

-Geates

"I hope I can chill and see the change - stop the bleed inside and feel again. Cut the chain of lies you've been feeding my veins; I've got nothing to say to you!"
-Infected Mushroom

"I do not offer answers, only considerations."
- Geates's Disclaimer

 

[Xeeq]

How do I do that? I don't know how to write code to get it to return something. I would want it to return any file that has yesterdays date. I added the rest of the code that you wrote, but took out all lines that had anything to do with client name. I the told it to attach "arr attachment". Now I am getting an error that says "the server threw an exception" so now I am troubleshooting that.




The only knowledge is knowing that you know nothing.

 

[Geates]

I posted it in red above.

I don't know how to write code to get it to return something.

While it is for everyone with an issue pertinate to VBS, a certain level of understanding is assumed from those that post in this forum. It is clear that you do not have such understanding. I strongly suggest you get a programmer at your company to address this issue.

I added the rest of the code that you wrote, but took out all lines that had anything to do with client name

How can you expect it to work if you indiscriminately removed lines? "If you want to destory my sweater, pull this thread as I walk away"

I the told it to attach "arr attachment". Now I am getting an error that says "the server threw an exception" so now I am troubleshooting that.

Makes sense, there is no "arr attachment" is not a variable. It is an array. It contains several values in which they must be accessed using their index.

-Geates

"I hope I can chill and see the change - stop the bleed inside and feel again. Cut the chain of lies you've been feeding my veins; I've got nothing to say to you!"
-Infected Mushroom

"I do not offer answers, only considerations."
- Geates's Disclaimer

 

[Xeeq]

I have appreciated your help thus far, but obviously you are getting frustrated. First of all, I have told you more than once during this thread that I am no programmer. I also stated that I first started working with VBscript ONE WEEK before I posted this thread, and that I researched arrays but did not have the basic knowledge to understand it. I am simply a person that is computer savvy, and I am doing my best to understand all of this.

I understand if you are fed up with this thread and want to just walk away from it, no problem. No hard feelings, and thank you for your help.

If not, then i feel like I need to re-iterate what I need. My original post no longer applies, because you have shown me a very simply way to find only the things I need to attach simply by looking at the date in the file name.

The code currently looks into the folder where my attachments are. Then, looks at the file names to determine which ones contain yesterday's date. Then, it opens up an email message from outlook and fills in the recipients, carbon copy, Subject, and body. Now I just simply need it to recall all files from the original folder that contained yesterday's date, and then attach them to the email. That is the only thing I have left to do. I have been sitting at my desk all day each day trying to learn how to write some simple code, because I do not want to have to rely on you or the next guy. This is not your responsibility, but you are the best I've got because I cannot learn how to write code over night. If you want to help me, it will be appreciated. If not, then thank you for your help so far.




The only knowledge is knowing that you know nothing.

 

[Geates]

I'm not getting frustrated. I've learned that frustration gets nobody anywhere. However, I offen struggle with explanations - the simplest things can be quite arduous in my case. I recognized that concept iteration was not favorable to me so I recommended that you employ someone who can address your issue locally.

The code I originally posted will discover all attachments from yesterday that pertain to a client and attach them to an email. I'll repost my code with additional comments to make it more clear.

'*********************************************************************
' VARIABLE DEFINITION
'*********************************************************************

const UNKNOWN = "Chickenless Soup"

dim arrAttachments()
dim strAttachmentsFolder
dim strFrom
dim strSubject
dim strMessage

set objFSO = CreateObject("Scripting.FileSystemObject")
set dicAttachments = CreateObject("Scripting.Dictionary")

strAttachmentsFolder = "C:\Temp"
strFrom = "me@domain.com"
strSubject = "Daily Attachments"
strMessage = "Here are your daily attachments"

'*********************************************************************
' FUNCTIONS
'*********************************************************************

'determin yesterday
function getYesterday()
	'get the numeric representation of yesterdays month
	intMonth = month(date - 1)
	
	'set the string appreviation of the numeric month
	select case (intMonth)
		case 1 : strMonth = "Jan"      
		case 2 : strMonth = "Feb"      
		case 3 : strMonth = "Mar"      
		case 4 : strMonth = "Apr"       
		case 5 : strMonth = "May"      
		case 6 : strMonth = "Jun"      
		case 7 : strMonth = "Jul"      
		case 8 : strMonth = "Aug"      
		case 9 : strMonth = "Sept"      
		case 10 : strMonth = "Oct"      
		case 11 : strMonth = "Nov"      
		case 12 : strMonth = "Dec"   
	end select   
	
	'return strMonth_yesterday (eg Nov_2)
	getYesterdaay = strMonth & "_" & day(date - 1)
end function

'determine if strFileName is from yesterday
function isFileValid(strFileName)   
	'set the default validity to false
	isFileValid = false   
	
	'Assuming the files have the nomeclature of Client_month_day_var.pdf
	'split strFileName into pieces by "_"
	arrPieces = split(strFileName, "_")   
	
	'if there 4 peices (0-3) then continue validating
	if (ubound(arrPieces) = 3) then
		strMonth = arrPieces(1)   
		strDay = arrPieces(2)      
	
		'set strYesterday to whatever the function getYesterday() returns.
		strYesterday = getYesterday()
		
		if (strYesterday = strMonth & "_" & strDay) then      
			'check to see if the file has a pdf extention.  If so, set isFileValid to true
			if (right(strFileName, 4) = ".pdf") then isFileValid = true     
		end if
	end if
end function

'*********************************************************************
' main
'*********************************************************************

'get a collection of file in the strAttachmentsFolder
set colFiles = objFSO.GetFolder(strAttachmentsFolder).Files

'run through each file in the collection
for each objFile in colFiles
	'check to see if the current file is a valid attachment
	if (isFileValid(objFile.Name) = true) then      
		'get the client name from the file name 
		strClient = left(objFile.Name, instr(objFile.Name, "_"))     
		'depending on the value of strClient, set strTo (a recipient)
		select case lcase(strClient)         
			case "bob" : strTo = "bob@organics.com"         
			case "joe" : strTo = "boss@capital.com"         
			case "janet" : strTo = "janet@office.gov"         
			case else : strTo = UNKNOWN      
		end select      
		
		if (strTo <> UNKNOWN) then         
			'add client (new dictionary key) if they aren't already in the dictionary of attachments
			if NOT (dicAttachments.Exists(strClient)) then
				dicAttachments.Add strClient, array(strTo)
			end if
			
			'get the array of attachments
			arrAttachments = dicAttachments.Item(strClient)         
			
			'because we are adding a new attachment to the array, we need to make sure there is room for it.
			intIndex = ubound(arrAttachments) + 1         
			
			'redimension arrAttachment to the new size while preserving its contents
			redim preserve arrAttachment(intIndex)         
			
			'add the attachment file to the new index
			arrAttachment(intIndex) = objFile.Path
			
			'put the array back in the dictionary or attachments for the client.
			dicAttachments.Item strClient, arrAttachments      
		end if
	end if
next

'get all the clients (keys) of the attachments dictionary and store them in an array
arrClients = dicAttachments.Keys

'run through the arrClients array
for i = 0 to ubound(arrClients)   
	'set strClient to the index (i) of arrClients
	strClient = arrClients(i)   
	
	'get the array if attachment for this client
	arrAttachments = dicAttachments.Item(strClient)      
	
	'create an email object
	set objEmail = CreateObect("cdo.message")   
	
	'set certain email properties
	objEmail.To = arrAttachments(0)   
	objEmail.From = strFrom   
	objEmail.Subject = strSubject   
	objEmail.Message = strMessage  
	objEmail.Configuration.Fields.Item ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusing")[/URL] = 2
	objEmail.Configuration.Fields.Item ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserver")[/URL] = "nmhexevs01.nmh.nmrhs.net"
	objEmail.Configuration.Fields.Item ("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserverport")[/URL] = 25
	objEmail.Configuration.Fields.Update
	
	'add each file in arrAttachments to the email
	for j = 1 to ubound(arrAttachments)      
		objEmail.AddAttachment(arrAttachments(j))   
	next   
	
	'send email
	objEmail.Send
next

msgbox "done"

A great VBS resource (at least in my opinion)

http://raqxtr.sstar.com/caspdoc/html/vbscript_language_reference.htm

-Geates

"I hope I can chill and see the change - stop the bleed inside and feel again. Cut the chain of lies you've been feeding my veins; I've got nothing to say to you!"
-Infected Mushroom

"I do not offer answers, only considerations."
- Geates's Disclaimer

 

[Xeeq]

It works!! I owe it all to you, Geates. I had to changed two things about the code to get it work the way I needed it to. 1. I had to make it create the email object first. 2. I told it to attach any valid file as it ran through the files in the folder, as oppose to after it was finished running through. That was the only way I could get around the "The server threw an exception" error. I couldn't have done it without you. Thanks

 

[Geates]

Glad to hear it.

-Geates

"I hope I can chill and see the change - stop the bleed inside and feel again. Cut the chain of lies you've been feeding my veins; I've got nothing to say to you!"
-Infected Mushroom

"I do not offer answers, only considerations."
- Geates's Disclaimer