| \n One to put a bandage on the freshly-sewn wound.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n 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.<\/p>\n 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.<\/p>\n 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.<\/p>\n Of course, as it turns out, we can’t<\/i> 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!<\/p>\n Oh, right; guess we should show you the script, shouldn’t we? Here you go:<\/p>\n Const olFolderInbox = 6
Const xlCellTypeLastCell = 11<\/pre>\nSet objFSO = CreateObject(\"Scripting.FileSystemObject\")<\/pre>\nSet objOutlook = CreateObject(\"Outlook.Application\")
Set objNamespace = objOutlook.GetNamespace(\"MAPI\")
Set objFolder = objNamespace.GetDefaultFolder(olFolderInbox)<\/pre>\nDim arrFiles()
intSize = 0<\/pre>\nSet colItems = objFolder.Items
Set colFilteredItems = colItems.Restrict(\"[Subject] = 'Test Subject'\")<\/pre>\nFor 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<\/pre>\nSet objExcel = CreateObject(\"Excel.Application\")
objExcel.Visible = True
objExcel.DisplayAlerts = False<\/pre>\nSet objWorkbook2 = objExcel.Workbooks.Add
Set objWorksheet2 = objWorkbook2.Worksheets(\"Sheet1\")<\/pre>\nFor Each strFile in arrFiles
Set objWorkbook = objExcel.Workbooks.Open(strFile)
Set objWorksheet = objWorkbook.Worksheets(\"Sheet1\")<\/pre>\n Set objRange = objWorksheet.UsedRange
objRange.Copy<\/pre>\n objWorksheet2.Activate
objworksheet2.Range(\"A1\").Activate<\/pre>\n If objWorksheet2.Cells(1,1).Value <> \"\" Then
Set objRange2 = objWorksheet2.UsedRange
objRange2.SpecialCells(xlCellTypeLastCell).Activate
intNewRow = objExcel.ActiveCell.Row + 1
strNewCell = \"A\" & intNewRow
objWorksheet2.Range(strNewCell).Activate
End If<\/pre>\n objWorksheet2.Paste
objWorkbook.Close
objFSO.DeleteFile(strFile)
Next<\/pre>\n <\/pre>\nAs 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.<\/p>\n No, hey, just kidding. (Well, actually we weren’t <\/i>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<\/b>, 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<\/b> object, bind to the MAPI namespace, and then make a connection to the Inbox folder:<\/p>\nSet objOutlook = CreateObject(\"Outlook.Application\")\nSet objNamespace = objOutlook.GetNamespace(\"MAPI\")\nSet objFolder = objNamespace.GetDefaultFolder(olFolderInbox)<\/pre>\nSo 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:<\/p>\n Dim arrFiles()\nintSize = 0<\/pre>\nNow<\/i> we’re ready to start retrieving emails; in fact, that’s what these two lines of code are for:<\/p>\nSet colItems = objFolder.Items\nSet colFilteredItems = colItems.Restrict(\"[Subject] = 'Test Subject'\")<\/pre>\nIn line 1 we’re using the Items<\/b> 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<\/i> 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<\/b> method to create a filtered collection, a collection in which all the items must have a Subject<\/b> property equal to Test Subject<\/i>.<\/p>\n That brings us to this block of code:<\/p>\n For Each objMessage In colFilteredItems\n Set colAttachments = objMessage.Attachments \n intCount = colAttachments.Count\n If intCount <> 0 Then\n For i = 1 To intCount\n strFileName = \"C:\\Test\\\" & objMessage.Attachments.Item(i).FileName\n objMessage.Attachments.Item(i).SaveAsFile strFileName\n ReDim Preserve arrFiles(intSize)\n arrFiles(intSize) = strFileName\n intSize = intSize + 1\n Next\n End If\nNext<\/pre>\nWhat 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:<\/p>\n Set colAttachments = objMessage.Attachments<\/pre>\nOnce we’ve done that we use the Count<\/b> property to determine the number of attachments in the Attachments collection, assigning that value to a variable named intCount:<\/p>\nintCount = colAttachments.Count<\/pre>\nWe then check to see if intCount is equal to 0. If intCount is<\/i> equal to 0 that means the message doesn’t have<\/i> any attachments; in that case we go back to the top of the loop and try again with the next email in the collection. <\/p>\n On the other hand, if the Count is equal to anything except<\/i> 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:<\/p>\nFor i = 1 To intCount<\/pre>\nAs 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<\/i> 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?<\/p>\n Well, OK. Clever for us<\/i>.<\/p>\n 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\\<\/b> with the value of the attachment’s FileName<\/b> property. As soon as we’ve done that we can then use the SaveAsFile<\/b> method to save the file to disk:<\/p>\nobjMessage.Attachments.Item(i).SaveAsFile strFileName<\/pre>\nWe then execute these three lines of code:<\/p>\n ReDim Preserve arrFiles(intSize)\narrFiles(intSize) = strFileName\nintSize = intSize + 1<\/pre>\nWhat we’re doing here is using the ReDim Preserve<\/b> 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.<\/p>\n 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:<\/p>\n Set objExcel = CreateObject(\"Excel.Application\")\nobjExcel.Visible = True\nobjExcel.DisplayAlerts = False\n\nSet objWorkbook2 = objExcel.Workbooks.Add\nSet objWorksheet2 = objWorkbook2.Worksheets(\"Sheet1\")<\/pre>\nAs you can see, we kick things off by creating an instance of the Excel.Application<\/b> object, then setting the Visible<\/b> property to True; that gives us a running instance of Excel that we can see onscreen. We then set the DisplayAlerts<\/b> 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<\/b>).<\/p>\n Once that’s done we use the Add<\/b> 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<\/i> get interesting.<\/p>\n Oh, sorry; we’re watching a baseball game on TV. But things get pretty interesting with our script, too.<\/p>\n How<\/i> interesting? Well, to begin with, we set up a For Each loop to loop through all the files paths in the array arrFiles:<\/p>\nFor Each strFile in arrFiles<\/pre>\nInside that loop we use these two lines of code to open the file and then bind to the first worksheet in that file:<\/p>\n Set objWorkbook = objExcel.Workbooks.Open(strFile)\nSet objWorksheet = objWorkbook.Worksheets(\"Sheet1\")<\/pre>\nAfter we’ve done that we can use the UsedRange<\/b> property to select all the data on the worksheet, then use the Copy<\/b> method to copy all that data to the Clipboard. That’s what these two lines are for:<\/p>\nSet objRange = objWorksheet.UsedRange\nobjRange.Copy<\/pre>\nWe 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:<\/p>\n objWorksheet2.Activate\nobjworksheet2.Range(\"A1\").Activate<\/pre>\nAt 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<\/b> 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<\/i>?<\/p>\n Well, the first thing we do is check to see if cell A1 is empty; if it is, then we can<\/i> just paste data into the worksheet:<\/p>\nIf objWorksheet2.Cells(1,1).Value <> \"\" Then<\/pre>\nIn 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<\/i> paste our new set of data. That’s what this block of code is for:<\/p>\nSet objRange2 = objWorksheet2.UsedRange\nobjRange2.SpecialCells(xlCellTypeLastCell).Activate\nintNewRow = objExcel.ActiveCell.Row + 1\nstrNewCell = \"A\" & intNewRow\nobjWorksheet2.Range(strNewCell).Activate<\/pre>\nWhat we’re doing here is using the UsedRange<\/b> property to create a Range<\/b> object that encompasses all the cells in the spreadsheet that contain data. Once that’s done we use the SpecialCells<\/b> 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<\/b> method to make that cell the active cell in the spreadsheet.<\/p>\n 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<\/i> other ways) is to determine the current row and then add 1 to it; that’s what this line of code does:<\/p>\nintNewRow = objExcel.ActiveCell.Row + 1<\/pre>\nWe then tack the letter A<\/i> 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:<\/p>\nstrNewCell = \"A\" & intNewRow objWorksheet2.Range(strNewCell).Activate<\/pre>\nOnce 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:<\/p>\n objFSO.DeleteFile(strFile)<\/pre>\nAnd then it’s back to the top of the loop, where we repeat the process with the next file path in arrFiles.<\/p>\n 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.<\/p>\n","protected":false},"excerpt":{"rendered":" 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 […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[212,49,3,5],"class_list":["post-55293","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-microsoft-outlook","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":" 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 […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/55293","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=55293"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/55293\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=55293"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=55293"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=55293"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
| ![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/q-for-powertip.jpg\" "\\"Hey,")
<\/p>\n![\"Spacer\"](\"https:\/\/devblogs.microsoft.com\/scripting\/wp-content\/uploads\/sites\/29\/2019\/05\/spacer.gif\")
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/a-for-powertip.jpg\" "\\"Hey,")
![\"Script](\"http:\/\/img.microsoft.com\/library\/media\/1033\/technet\/images\/scriptcenter\/ad.jpg\" "\\"Script")
<\/a><\/p>\n