Merge Outlook spreadsheet attachments

merge

At OutlookCode someone asks if it's possible to append a bunch of Excel spreadsheets attached to emails in a folder. In the end, we want all the data attached to each email to reside in one spreadsheet.

Assume you have an existing spreadsheet and you want to append incoming spreadsheets to it. Here's one way to do it.

To grab data from a closed workbook, I use the procedure found here: Import data from a closed workbook. Hey, why reinvent the wheel? :|

This procedure is what we're going to use to read the data from each attachment and write it into the rollup workbook. I won't reprint it here, visit the above link to get the function. For my purpose I converted it to late bound.

The procedure

The procedure itself is very short. We locate the folder where the emails are stored, and set a reference to it. After creating an instance of Excel, we save each email's attachment and use the GetDataFromClosedWorkbook function to read the workbook and append the data to the bottom of the rollup workbook.

Sub AppendSpreadsheets()

Dim folder As Outlook.MAPIFolder
Dim folderItems As Outlook.Items
Dim obj As Object
Dim msg As Outlook.MailItem
Dim msgAttachment As Outlook.Attachment
Dim xlApp As Object ' Excel.Application
Dim fileN As String
Dim targetFile As Object ' Excel.Workbook
Dim targetRange As Object ' Excel.Range

Const xlUp As Long = -4162

  ' get destination folder
  Set folder = _
    GetNS(Outlook.Application).GetDefaultFolder(olFolderInbox).Folders("Dump Folder")
  Set folderItems = folder.Items

  ' start Excel and open target spreadsheet
  Set xlApp = GetExcelApp
  Set targetFile = xlApp.Workbooks.Open("C:\My_File.xls")
  Set targetRange = targetFile.Sheets(1).Range("A" & xlApp.Rows.Count)

  ' loop through folder
  For Each obj In folderItems
    If IsMail(obj) Then  ' it's an email
      Set msg = obj

      ' assume one .xls attachment
      Set msgAttachment = msg.Attachments.Item(1)
      ' save attachment
      fileN = Environ("temp") & "\" & msgAttachment.fileName
      msgAttachment.SaveAsFile fileN

      ' write new data to target file
      ' assumes existing file has headers already
      Call GetDataFromClosedWorkbook(fileN, "A1:G100", _
    targetRange.End(xlUp).Offset(1, 0), False)

      ' save update
      targetFile.Save
      ' delete saved attachment
      Kill fileN

    End If
  Next obj

End Sub

' ancillary functions
Function GetExcelApp() As Object
  On Error Resume Next
  Set GetExcelApp = CreateObject("Excel.Application")
End Function

IsMail, GetItems and GetNS may be found on the Utility Functions page.

A lot of assumptions are made here (thanks in small part to the original poster), namely that:

  • the emails in the folder have a single .xls attachment.
  • the ranges in each attachment file are the same size (A1:G100)

If that is the case for you, then you should be able to use this code with little adjustment. But if your ranges are of different sizes, you'll need to edit the code and possibly even edit the GetDataFromClosedWorkbook function to take a Range Object instead of a string representing the range.

About JP

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »



Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

comment bubble 7 Comment(s) on Merge Outlook spreadsheet attachments:

  1. Christopher Anton writes:

    Hi. I'm trying to do something subtlely different from the every helpful examples shown on here. The problem is that I receive regular emails containing data in a standard format. These emails all have the same sender and subject. I them want some code that automatically, whenever one of these emails arrives, selects the entire body text of the email, copies it to the clipboard, opens an Excel spreadsheet and pastes it in there and then runs a macro to format the data into a database.

  2. Since you want code to run automatically whenever an email comes in, the code needs to be in Outlook.

    I start with this code and customize it as needed: http://www.jpsoftwaretech.com/outlook-vba/stock-event-code/

    In the part that says " (3) do something here" that's where you'll need to grab the body of the email, open the spreadsheet and format the data.

    To "select" the email body, just assign it to a String variable:

    Dim msgBody As String
    msgBody = Msg.Body

    To open a spreadsheet, create an instance of Excel and open the workbook using Excel's native methods, i.e.

    Dim xlApp As Object
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Workbooks.Open "your workbook path and filename here"

    To "paste" the data, just assign it to a range, i.e.

    xlApp.Workbooks(1).Worksheets(1).Range("A1").Value = msgBody

    (This is just shortcode, I would normally assign intermediate objects instead of using such long references)

    Now to parse it, that depends on the exact form of the message body.

    HTH,
    JP

  3. Christopher Anton writes:

    Thanks for this. What code would I use to only slect emails from a certain individual sender, or with certain key words in the subject?

  4. Hey man, I need your help. I tried to use your code but it's displaying the following message:
    User-defined type not defined

    In this line:
    Function GetItems(olNS As Outlook.Namespace, _
    folder As OlDefaultFolders) As Outlook.Items

    Which references should I need to use in order to avoid this alert?

    Thank you very much.

    Best regards,

    Moises G.

    • This code is meant to be used inside Outlook's VBA environment. If you run it in another program (Access, for example), you need to either

      a) convert named constants to their numeric equivalents (ex: "olMailItem" becomes "0"), or
      b) set a reference to the Outlook Object Library (Tools » References in VBA Editor), or
      c) declare the named constants using their numeric equivalents. See List of Enumerated Constants for Late Bound VBA Automation for a .bas file you could import that already contains the constants.

This article is closed to any future comments.
Peltier Tech Charting Utilities for Excel