Hey, Scripting Guy! We recently implemented a scheme whereby Team Leads or Contract Sponsors may request server access for their Team members by submitting an Excel spreadsheet. To this end, I proposed a script that will open Outlook, look for items in the Inbox that contain a particular Subject line (we will pick the wording later) and, for each item in the collection, open the attached spreadsheet, copy the data to another spreadsheet, and then close the attachment. I have successfully completed all the steps except opening the attachment. Can you help me with that?
Hey, FS. You know what’s the great thing about having kids? Oh, shoot; we were hoping you’d know the answer to that one. However, we have to admit that if you do have kids you’ll never have a dull moment. For example, the other night the Scripting Guy who writes this column was sitting around the house when the phone rang:
“Hey, Dad. Do you want to take me to the hospital?”
“Uh, yeah, I guess.”
As it turned out, the Scripting Son and several of his friends were racing mountain bikes, and the Scripting Son took a corner a bit faster than he probably should have. Somehow – and nobody is totally sure how – when he did so his foot came off the pedal and the pedal, perhaps in an act of vengeance, carved a nice big gash in the back of his leg. The boys taped the leg up (by fortuitous coincidence the Scripting Son had just taken a first aid course a couple days ago), but when they checked the wound a half hour later and blood came gushing out they decided that maybe he should go have someone else – like, say a doctor – take a look at it.
And so the Scripting Son and the Scripting Dad got to have a nice father-son outing to Evergreen Hospital in Kirkland. (True, a baseball game or even a trip to the zoo might have been better. But, hey a father-son outing is a father-son outing, right?) They then spent an hour and half in the emergency room, while 5 different people looked at the gash:
One to do an initial diagnosis and numb the wound.
One to clean the wound.
One to come in and go, “OK, that looks clean”
One to put in a few stitches.
One to put a bandage on the freshly-sewn wound.
And that doesn’t count the three different people who collected the Scripting Son’s address and phone number and – not incidentally, the Scripting Dad’s insurance information.
But all’s well that ends well: the people at the hospital were extremely nice (all of them); the Scripting Son has a good story to tell people; and the Scripting Dad, who was supposed to do some work that evening, had the perfect excuse for not doing anything. Everything turned out perfect.
Well, almost perfect; unfortunately, the Scripting Guy who writes this column can’t use the Scripting Son’s accident to get out of working today. (Not that he didn’t try, mind you.) Which means we better see what we can do about answering today’s question.
Of course, as it turns out, we can’t answer the question, at least not as asked: we couldn’t figure out a way to programmatically open an attachment, either. But that’s OK; we found a workaround. And even though FS has already completed the other steps in his multi-step process we decided other people might be interested in that as well; therefore this script includes code that not only opens each attachment but also copies all the data from each file and pastes that data into another spreadsheet. Enjoy!
Oh, right; guess we should show you the script, shouldn’t we? Here you go:
Const olFolderInbox = 6
Const xlCellTypeLastCell = 11
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objOutlook = CreateObject("Outlook.Application")
Set objNamespace = objOutlook.GetNamespace("MAPI")
Set objFolder = objNamespace.GetDefaultFolder(olFolderInbox)
intSize = 0
Set colItems = objFolder.Items
Set colFilteredItems = colItems.Restrict("[Subject] = 'Test Subject'")
For Each objMessage In colFilteredItems
Set colAttachments = objMessage.Attachments
intCount = colAttachments.Count
If intCount <> 0 Then
For i = 1 To intCount
strFileName = "C:\Test\" & objMessage.Attachments.Item(i).FileName
ReDim Preserve arrFiles(intSize)
arrFiles(intSize) = strFileName
intSize = intSize + 1
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.DisplayAlerts = False
Set objWorkbook2 = objExcel.Workbooks.Add
Set objWorksheet2 = objWorkbook2.Worksheets("Sheet1")
For Each strFile in arrFiles
Set objWorkbook = objExcel.Workbooks.Open(strFile)
Set objWorksheet = objWorkbook.Worksheets("Sheet1")
Set objRange = objWorksheet.UsedRange
If objWorksheet2.Cells(1,1).Value <> "" Then
Set objRange2 = objWorksheet2.UsedRange
intNewRow = objExcel.ActiveCell.Row + 1
strNewCell = "A" & intNewRow
As you can see, this is a relatively long, complicated script. Which can mean only one thing: we should probably leave now, before someone asks us to explain how it works.
No, hey, just kidding. (Well, actually we weren’t kidding, but the Scripting Editor is making us explain how the script works anyway.) We start out in pretty straightforward fashion, defining a constant named olFolderInbox (which will tell the script that we want to work with the Inbox folder) and a second constant named xlCellTypeLastCell (which we’ll discuss in a little more detail later on). We next create an instance of the Scripting.FileSystemObject, an instance we’ll immediately set aside and save for future use. Finally, we use these three lines of code to create an instance of the Outlook.Application object, bind to the MAPI namespace, and then make a connection to the Inbox folder:
Set objOutlook = CreateObject("Outlook.Application") Set objNamespace = objOutlook.GetNamespace("MAPI") Set objFolder = objNamespace.GetDefaultFolder(olFolderInbox)
So are we ready to start retrieving emails? Well, just about. First we need to use these two lines of code to create a dynamic array named arrFiles and to assign the value 0 to a counter variable named intSize, a variable we’ll use to periodically resize our array:
Dim arrFiles() intSize = 0
Now we’re ready to start retrieving emails; in fact, that’s what these two lines of code are for:
Set colItems = objFolder.Items Set colFilteredItems = colItems.Restrict("[Subject] = 'Test Subject'")
In line 1 we’re using the Items property to retrieve a collection of all the emails found in the Inbox. And what are we doing in line 2? Well, as we noted earlier, we don’t want all the emails found in the Inbox, we want only those emails that have a specific Subject line. In line 2, we’re using the Restrict method to create a filtered collection, a collection in which all the items must have a Subject property equal to Test Subject.
That brings us to this block of code:
For Each objMessage In colFilteredItems Set colAttachments = objMessage.Attachments intCount = colAttachments.Count If intCount <> 0 Then For i = 1 To intCount strFileName = "C:\Test\" & objMessage.Attachments.Item(i).FileName objMessage.Attachments.Item(i).SaveAsFile strFileName ReDim Preserve arrFiles(intSize) arrFiles(intSize) = strFileName intSize = intSize + 1 Next End If Next
What we’re doing here is looping through our filtered collection. For each email in the collection we use this line of code to create an object reference to all the attachments accompanying that email:Set colAttachments = objMessage.Attachments
Once we’ve done that we use the Count property to determine the number of attachments in the Attachments collection, assigning that value to a variable named intCount:intCount = colAttachments.Count
We then check to see if intCount is equal to 0. If intCount is equal to 0 that means the message doesn’t have any attachments; in that case we go back to the top of the loop and try again with the next email in the collection.
On the other hand, if the Count is equal to anything except 0 then that means the message has at least one attachment. With that in mind, we set up a For Next loop that runs from 1 to the number of attachments accompanying the email:For i = 1 To intCount
As it turns out, there’s no way to programmatically open an email attachment. (Or, more correctly, we should say that there’s no way that we know of to programmatically open an email attachment. Such a method might very well exist; we just couldn’t figure out what it was.) Therefore, we’re going to cheat a little: instead of directly opening each attachment we’re going to save those attachments to the hard disk. We’ll then open each saved file, do our little copy-and-paste thing, and then close and delete each saved file. Clever, huh?
Well, OK. Clever for us.
At any rate, our first step to that end is to create a new file path for our first attachment; we do that by combining the string C:\Test\ with the value of the attachment’s FileName property. As soon as we’ve done that we can then use the SaveAsFile method to save the file to disk:objMessage.Attachments.Item(i).SaveAsFile strFileName
We then execute these three lines of code:ReDim Preserve arrFiles(intSize) arrFiles(intSize) = strFileName intSize = intSize + 1
What we’re doing here is using the ReDim Preserve statement to resize our array, taking care to preserve any data currently in that array. (Running ReDim without the Preserve statement would resize the array, but would also erase any data in the array.) We then add the path for our just-saved attachment to the array; in other words, arrFiles will eventually contain the complete path to each and every attachment we saved to the hard disk. And then we increment the value of intSize by 1. Why? Because the next time we resize our array we have to make sure the array can hold one item more than it can currently hold. This is an easy way of doing that.
After all the attachments have been retrieved and saved we then get Microsoft Excel up and running, something we use this block of code for:Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True objExcel.DisplayAlerts = False Set objWorkbook2 = objExcel.Workbooks.Add Set objWorksheet2 = objWorkbook2.Worksheets("Sheet1")
As you can see, we kick things off by creating an instance of the Excel.Application object, then setting the Visible property to True; that gives us a running instance of Excel that we can see onscreen. We then set the DisplayAlerts property to False. Why? Well, we’re going to repeatedly copy data from a workbook and then close that workbook; if we don’t set DisplayAlerts to False then every time we close a workbook Excel will say, “There is a large amount of Information on the Clipboard. Do you want to be able to paste this information into another program later?” Truthfully, we don’t really care; we just want to avoid having to deal with this dialog box. By setting DisplayAlerts to False we don’t see this dialog box; instead, Excel simply goes with the default choice (Yes).
Once that’s done we use the Add method to add a new workbook to our instance of Excel, then bind to the first worksheet in that workbook. It’s at that point that things really get interesting.
Oh, sorry; we’re watching a baseball game on TV. But things get pretty interesting with our script, too.
How interesting? Well, to begin with, we set up a For Each loop to loop through all the files paths in the array arrFiles:For Each strFile in arrFiles
Inside that loop we use these two lines of code to open the file and then bind to the first worksheet in that file:Set objWorkbook = objExcel.Workbooks.Open(strFile) Set objWorksheet = objWorkbook.Worksheets("Sheet1")
After we’ve done that we can use the UsedRange property to select all the data on the worksheet, then use the Copy method to copy all that data to the Clipboard. That’s what these two lines are for:Set objRange = objWorksheet.UsedRange objRange.Copy
We then use two more lines of code to activate our original worksheet (the one we’re going to paste the data into) and to make cell A1 on that sheet the active cell:objWorksheet2.Activate objworksheet2.Range("A1").Activate
At this point things get a little tricky. If we were working with only one Excel file all we’d have to do is call the Paste method and we’d be done. However, we’re most likely going to be working with multiple Excel files. Suppose we paste in the data from the first file, and this data fills up the first 12 rows in the spreadsheet. When we go to paste in data from the next file, we have to start the paste operation in row 13. But how in the world do we do that?
Well, the first thing we do is check to see if cell A1 is empty; if it is, then we can just paste data into the worksheet:If objWorksheet2.Cells(1,1).Value <> "" Then
In fact, that’s what will happen when we go to paste in data from our first file: because cell A1 will be empty we can bypass this If Then statement and simply paste in the data. From that point on, however, cell A1 will no longer be empty. Because of that, we need to figure out where we can paste our new set of data. That’s what this block of code is for:Set objRange2 = objWorksheet2.UsedRange objRange2.SpecialCells(xlCellTypeLastCell).Activate intNewRow = objExcel.ActiveCell.Row + 1 strNewCell = "A" & intNewRow objWorksheet2.Range(strNewCell).Activate
What we’re doing here is using the UsedRange property to create a Range object that encompasses all the cells in the spreadsheet that contain data. Once that’s done we use the SpecialCells method to go to the very last cell in that range; note the use of the constant xlCellTypeLastCell. And what do we do after we reach the last cell in the range? Why, we use the Activate method to make that cell the active cell in the spreadsheet.
Of course, that’s not really what we want; after all, that cell has data in it. What we want to do is drop down to the next row, and make cell A in that row the active cell. One way to do that (and there are other ways) is to determine the current row and then add 1 to it; that’s what this line of code does:intNewRow = objExcel.ActiveCell.Row + 1
We then tack the letter A onto the front of that value (e.g., A13) giving us a cell reference that we can use with the Range property. And that’s exactly what we do here; we create a new range and then make that cell (e.g., cell A13) the active cell:strNewCell = "A" & intNewRow objWorksheet2.Range(strNewCell).Activate
Once we’ve done that we can finally call the Paste method and paste in our data. We close the saved attachment file, then use this line of code to delete that file from the folder C:\Test:objFSO.DeleteFile(strFile)
And then it’s back to the top of the loop, where we repeat the process with the next file path in arrFiles.
That should do it, FS; let us know if you run into problems. Oh, and let us know if you need to go to the emergency room and get your leg stitched up: we’ve got plenty of experience doing that as well.