Link to home
Start Free TrialLog in
Avatar of jana
janaFlag for United States of America

asked on

review of VBA code not exporting completely from outlook to Excel

Hi,

We are working with a code that exports from Outlook to Excel but it seems not to export entirely.  When executed, you select the emails and then run it and it exports to excel 2010.  The problem is that it doesn't always work.  And yesterday it exports only 3 or 4 when selecting more than that.  Please review the code and advice on what we are doing wrong.



Thank you in advance.
Avatar of jana
jana
Flag of United States of America image

ASKER

(almost forgot) the code :
Sub aaaCopyToExcel()
  Dim xlApp As Object
  Dim xlWB As Object
  Dim xlSheet As Object
  Dim rCount As Long
  Dim bXStarted As Boolean
  Dim enviro As String
  Dim strPath As String
  Dim currentExplorer As Explorer
  Dim Selection As Selection
  Dim olItem As Outlook.MailItem
  Dim obj As Object
  Dim strColB, strColC, strColD, strColE, strColF As String
 'Start
  xVar = 0
 'Get Excel set up
  enviro = CStr(Environ("USERPROFILE"))
 'the path of the workbook
  strPath = enviro & "\Documents\test.xlsx"
  On Error Resume Next
  Set xlApp = GetObject(, "Excel.Application")
  If Err <> 0 Then
     Application.StatusBar = "Please wait while Excel source is opened ... "
     Set xlApp = CreateObject("Excel.Application")
     bXStarted = True
     End If
  On Error GoTo 0
 
 'Open the workbook to input the data
  Set xlWB = xlApp.Workbooks.Open(strPath)
  Set xlSheet = xlWB.Sheets("Sheet1")
 
 'Process the message record
  On Error Resume Next
 
 'Find the next empty line of the worksheet
  rCount = xlSheet.Range("B" & xlSheet.Rows.Count).End(-4162).Row
 'needed for Exchange 2016. Remove if causing blank lines.
  rCount = rCount + 1

 'Get the values from outlook
  Set currentExplorer = Application.ActiveExplorer
  Set Selection = currentExplorer.Selection
  For Each obj In Selection
      Set olItem = obj
     'Get Outlook fields
      strColB = olItem.SenderEmailAddress
      strColC = olItem.To
      strColD = olItem.Subject
      strColE = olItem.ReceivedTime
      strColF = olItem.Body
     'Write Outlook field to excel
      xlSheet.Range("B" & rCount) = strColB 'SenderEmailAddress
      xlSheet.Range("c" & rCount) = strColC 'To
      xlSheet.Range("d" & rCount) = strColD 'Subject
      xlSheet.Range("e" & rCount) = strColE 'ReceivedTime
      xlSheet.Range("f" & rCount) = strColF 'Body
      xVar = xVar + 1
      Next
  
  xlWB.Close 1
  If bXStarted Then
     xlApp.Quit
     End If
  
  Set olItem = Nothing
  Set obj = Nothing
  Set currentExplorer = Nothing
  Set xlApp = Nothing
  Set xlWB = Nothing
  Set xlSheet = Nothing
 End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Professor J
Professor J

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jana

ASKER

Thanx! Worked!
Avatar of Professor J
Professor J

you are welcome