35295

Output to Excel from Outlook using VBA

Question:

I have a VBA script sitting in Outlook that attempts to grab information from email items and write it to an Excel file. I'm pretty new to VBA, so after much debugging and editing, I've managed to get something that mostly works, but I could use a little guidance. I'll explain my script and then talk about my problem.

I've included my full script at the end. Here is a quick outline of it, where I include the parts that I think might need some work.

Sub Output2Excel() Dim xlApp As Object Dim xlWkBk As Object Dim xlSheet As Object ' Setup the Excel Application Set xlApp = Application.CreateObject("Excel.Application") Set xlWkBk = xlApp.Workbooks.Open(PathName & FileName, , False) ' Open the Excel file to be updated Set xlSheet = xlWkBk.Worksheets(1) ' Loop over all the olMail items in FolderTgt, which is a MAPIFolder type RowNext = xlSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1 ' <- This line highlighted by debugger (see below) ' Write stuff to Excel like ' For xlSheet.Cells(RowNext , Col).Value = [Whatever Item I want out of FolderTgt] RowNext = RowNext + 1 ' Next ' Done with the loop, now save the file and close things down xlWkBk.Save Set xlSheet = Nothing xlWkBk.Close Set xlWkBk = Nothing xlApp.Quit Set xlApp = Nothing Debug.Print "All Done" End Sub

When I run this script, it updates my excel file correctly, producing results like:

+ - + ------- + --------------- + -------- + - + | 2 | Sender1 | SomeSubject | 04/13/17 | 0 | | 3 | Sender2 | AnotherSubject | 04/13/17 | 0 | | 4 | Sender3 | RE: SomeSubject | 04/13/17 | 0 | + - + ------- + --------------- + -------- + - +

I can even run it multiple times and it appends to the file without issue:

+ - + ------- + --------------- + -------- + - + | 2 | Sender1 | SomeSubject | 04/13/17 | 0 | | 3 | Sender2 | AnotherSubject | 04/13/17 | 0 | | 4 | Sender3 | RE: SomeSubject | 04/13/17 | 0 | | 2 | Sender1 | SomeSubject | 04/13/17 | 0 | | 3 | Sender2 | AnotherSubject | 04/13/17 | 0 | | 4 | Sender3 | RE: SomeSubject | 04/13/17 | 0 | | 2 | Sender1 | SomeSubject | 04/13/17 | 0 | | 3 | Sender2 | AnotherSubject | 04/13/17 | 0 | | 4 | Sender3 | RE: SomeSubject | 04/13/17 | 0 | + - + ------- + --------------- + -------- + - +

<strong>So up to here, everything works</strong>

<strong>Here's the problem:</strong>

I open the excel file to look at the results. I close it without any modifications. Then, I try to run the script again in VBA, and I get the following error:

Run-time error '1004': Method 'Rows' of object '_Global' failed

The debugger highlights the line

RowNext = xlSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1 ' <- This line highlighted by debugger

I'm really not sure what's causing this error since it happens only when I open the excel file to check the results. I think the VBA script might be opening and closing the file incorrectly, but the resources I've used indicate that this is the right way to do it. One solution may be to never open the file, but that's unreasonable.

Any thoughts or insights as to what may be going on here?

<hr />

More Detailed script below:

Sub Output2Excel() Dim FolderNameTgt As String Dim PathName As String Dim FileName As String Dim FolderTgt As MAPIFolder Dim xlApp As Object Dim xlWkBk As Object Dim xlSheet As Object Dim RowNext As Integer Dim InxItemCrnt As Integer Dim FolderItem As Object ' Outlook folder, computer directory, and excel file involved in the reading and writing FolderNameTgt = "MyUserId|Testing VBA" PathName = "N:\Outlook Excel VBA\" FileName = "Book1.xls" ' Locate the Folder in Outlook. I've left out some of the details here because this part works fine Call FindFolder(FolderTgt, FolderNameTgt, "|") If FolderTgt Is Nothing Then Debug.Print FolderNameTgt & " not found" Exit Sub End If ' Setup the Excel Application Set xlApp = Application.CreateObject("Excel.Application") Set xlWkBk = xlApp.Workbooks.Open(PathName & FileName, , False) Set xlSheet = xlWkBk.Worksheets(1) ' Loop over all the items in FolderTgt RowNext = xlSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1 For InxItemCrnt = 1 To FolderTgt.Items.Count ' Set and use the referenced item Set FolderItem = FolderTgt.Items.Item(InxItemCrnt) ' If the Item is of the olMail class, then extract information and write it to excel If FolderItemClass = olMail Then xlSheet.Cells(RowNext, 1).Value = RowNext xlSheet.Cells(RowNext, 2).Value = FolderItem.SenderName xlSheet.Cells(RowNext, 3).Value = FolderItem.Subject xlSheet.Cells(RowNext, 4).Value = FolderItem.ReceivedTime xlSheet.Cells(RowNext, 4).NumberFormat = "mm/dd/yy" xlSheet.Cells(RowNext, 5).Value = FolderItem.Attachments.Count RowNext = RowNext + 1 End If Next InxItemCrnt ' Done with the loop, now save the file and close things down xlWkBk.Save 'FileName:=PathName & FileName Set xlSheet = Nothing xlWkBk.Close Set xlWkBk = Nothing xlApp.Quit Set xlApp = Nothing Debug.Print "All Done" End Sub

Answer1:

<strong>Rows</strong> by itself refers to the activeSheet in Excel. You need to qualify it. Instead of

RowNext = xlSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1

use

RowNext = xlSheet.Cells(xlSheet.Rows.Count, "A").End(xlUp).Row + 1

Recommend

  • How to get the MailItems of a specific folder in Outlook
  • Outlook VBscript to forward emails in a folder
  • Search Outlook Contact using COM?
  • Pick folder routine going to Error handler - Excel VBA
  • Excel vba calculate average of a column
  • Enter a new line and copy formula from cells above
  • Change column width and row height of hidden columns and rows (remaining hidden): Excel VBA
  • Excel VBA delete entire row if both columns B and C are blank
  • OUTLOOK vba filter with like statement
  • Loop paste formula until next cell in range is empty
  • vba userform , if any of the checkboxes in the frame is true then macro should not be applied on the
  • VBA Multiple loops match conditions
  • VBA: How can I keep only the date values from a string?
  • vba paste values and keep source formatting?
  • Erase empty cells in an array
  • Delete All Rows With Errors in a column
  • cell spacing in div table
  • Using Sax parsing to edit and write XML in VB6
  • Excel's Macro-Recorder usage
  • Django: Count of Group Elements
  • Retrieving value from sql ExecuteScalar()
  • Excel - Autoshape get it's name from cell (value)
  • Sending data from AppleScript to FileMaker records
  • MySQL WHERE-condition in procedure ignored
  • vba code to select only visible cells in specific column except heading
  • Do I've to free mysql result after storing it?
  • Unanticipated behavior
  • Transpose CSV data with awk (pivot transformation)
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • How can I get HTML syntax highlighting in my editor for CakePHP?
  • How do I configure my settings file to work with unit tests?
  • IndexOutOfRangeException on multidimensional array despite using GetLength check
  • Can't mass-assign protected attributes when import data from csv file
  • Sorting a 2D array using the second column C++
  • costura.fody for a dll that references another dll
  • Binding checkboxes to object values in AngularJs
  • Observable and ngFor in Angular 2
  • UserPrincipal.Current returns apppool on IIS
  • Unable to use reactive element in my shiny app
  • java string with new operator and a literal