Category Archives: VP Macros

Gmail Import to VantagePoint

The following provides guidance for exporting the content of a Gmail inbox to VantagePoint. To begin, start by creating a folder on your local drive and copying Gmail_Import_to_VP to this folder. Then take the following steps:

Step 1: Install Mozilla Thunderbird (https://www.mozilla.org/en-US/thunderbird/download) on your machine.

Step 2: Setup Thunderbird by providing it with a Gmail address and password.

Step 3: In Thunderbird install the Add-on ImportExportTools 3.2.1 (by selecting Tools > Add-ons, then search Extensions for the keyword ‘Export’. In the results list select ‘ImportExportTools’). After downloading this Add-on to your local disk, opening Add-ons from the Tools menu (Tools>Add-ons) and from the options button (adjacent to the search field) select “Install Add-on From File…”, then point to this Add-on. Thunderbird must be restarted for the installation to complete.

Note: Steps 1-3 only need to be performed once. After performing these all subsequent Gmail imports begin with Step 4.

Step 4: In Thunderbird click ‘Get Messages’, then right click your Inbox folder and select ImportExportTools >  Export all messages in the folder > Spreadsheet (CSV). You will be prompted for a folder to save the CVS export to. Save this file in the same folder as Gmail_Import_to_VP.xlsm.

Step 5: Open Gmail_Import_to_VP.xlsm and click the button ‘Gmail Import’ (note: macros need to be enabled in Excel for this script to run). The procedure that follows will generate a file in the same directory Gmail_Import_to_VP.xlsm named To_Import.xls. Launch VantagePoint and select ‘Import Database Table (Excel, Access, etc.)’. Next click ‘Browse’ and point to To_Import.xls.

Step 6: Once imported into VantagePoint some users find it useful to further process fielded values that are separated by commas (e.g. this can occur in the ‘TO’ field if there are multiple recipients for a given email). Some users also find it useful to create a folder titled ‘Exported Emails’ in Thunderbird and move all exported messages to this folder.

A training video using this script can be viewed at VP ” How-To”>Advanced Analytics  Importing E-mail into VantagePoint

Export MS Outlook Email and Import into VantagePoint

The following steps outline how to export email from Outlook and into VantagePoint:

Step 1: Copy the code in the text file VBA Export Code for Outlook into a VBA Module in Outlook. Steps for doing this appear below:

  1. Select the “File” tab in the Outlook ribbon and then click “Options”
  2. Click “Customize Ribbon” and select “Developer” on the right hand side of the screen (if it’s not already selected. Then click “OK”.
  3. Select the “Developer” tab in the Outlook ribbon and then click “Visual Basic” (or type Alt+F11).
  4. Click “Insert” then select “Module”.
  5. Copy the code in the attached text file (VBA Export Code for Outlook.txt) into a VBA Module that appears

Step 2: If Excel isn’t already referenced it needs to be. While in the VBA Module click Tools -> References and then select “Microsoft Excel Object Library”. Click “OK” and then close the VBA Module.

Step 3: In Outlook open the folder containing the emails you would like exported and then in the “Developer” tab click “Macros” and then select “ExportMessagesToExcel”. A pop up box will appear letting the user know when the process is complete and the number of messages that were exported (this process can require a little patience when applied to a large number of messages and/or messages with a significant number of characters).

Step 4: The following fields will be exported in Excel format for all messages in the open folder: Subject, Date, From, To, Attachment, CC, Body and Header. They will be saved to your Documents folder under the filename Outlook_Email_Export_[today’s date].xlsx, unless a file with that name already exists, in which case the user will be prompted to enter a filename.

Step 5: Launch VantagePoint, select “Import Database Table (Excel, Access, etc.)”, click “OK”, and point to the Outlook export file. Note: list items in some fields may contain multiple values delimited by semi-colons in which case the user might find it useful to right click on the field (in the Summary screen) and then select Further Processing -> Divide at -> Semicolon.