By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,122 Members | 906 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,122 IT Pros & Developers. It's quick & easy.

VBA Coding to Export Fields from Emails in Outlook to Excel (Help me!!!!!)

P: 12
I have little to no experience using VBA so I found a sample of code that would allow me to select a folder in Outlook and export that data into Excel. The folder selection box pops up consistently and it opens the file I selected, so that is good. I have tried doing so many things to the code to get it to function properly, but to no avail. One attempt where I removed all of the error handling stuff allowed me to export certain folders, but I still got error messages for some of the other ones. I'm thinking it might have something to do with "Dim msg As Outlook.MailItem". Every time I try to select a folder it says "There are no mail messages to export". I have received error messages: 13, 438, and 50290. The code is posted below. Your help is greatly appreciated.

-Eric

Expand|Select|Wrap|Line Numbers
  1. Sub ExportToExcel()
  2.   On Error GoTo ErrHandler
  3.   Dim appExcel As Excel.Application
  4.   Dim wkb As Excel.Workbook
  5. Dim wks As Excel.Worksheet
  6. Dim rng As Excel.Range
  7. Dim strSheet As String
  8. Dim strPath As String
  9. Dim intRowCounter As Integer
  10. Dim intColumnCounter As Integer
  11. Dim msg As Outlook.MailItem
  12. Dim nms As Outlook.NameSpace
  13. Dim fld As Outlook.MAPIFolder
  14. Dim itm As Object
  15.     strSheet = "Copy of Archive Completed Messages.xlsx"
  16.     strPath = "C:\Users\Documents\"
  17. strSheet = strPath & strSheet
  18. Debug.Print strSheet
  19.  
  20. Set nms = Application.GetNamespace("MAPI")
  21. Set fld = nms.PickFolder
  22.  
  23. If fld Is Nothing Then
  24. MsgBox "There are no mail messages to export", vbOKOnly, "Error"
  25. Exit Sub
  26. ElseIf fld.DefaultItemType <> olMailItem Then
  27. MsgBox "There are no mail messages to export", vbOKOnly, "Error"
  28. Exit Sub
  29. ElseIf fld.Items.Count = 0 Then
  30. MsgBox "There are no mail messages to export", vbOKOnly, "Error"
  31. Exit Sub
  32. End If
  33.  
  34. Set appExcel = CreateObject("Excel.Application")
  35. appExcel.Workbooks.Open (strSheet)
  36. Set wkb = appExcel.ActiveWorkbook
  37. Set wks = wkb.Sheets(1)
  38. wks.Activate
  39. MsgBox "There are no mail messages to export", vbOKOnly, "Error"
  40. appExcel.Application.Visible = True
  41.  
  42.  
  43. For Each itm In fld.Items
  44. intColumnCounter = 1
  45. Set msg = itm
  46. intRowCounter = intRowCounter + 1
  47. Set rng = wks.Cells(intRowCounter, intColumnCounter)
  48. rng.Value = msg.To
  49. intColumnCounter = intColumnCounter + 1
  50. Set rng = wks.Cells(intRowCounter, intColumnCounter)
  51. rng.Value = msg.SenderName
  52. intColumnCounter = intColumnCounter + 1
  53. Set rng = wks.Cells(intRowCounter, intColumnCounter)
  54. rng.Value = msg.LastModificationDate
  55. intColumnCounter = intColumnCounter + 1
  56. Next itm
  57.   Set appExcel = Nothing
  58.   Set wkb = Nothing
  59. Set wks = Nothing
  60. Set rng = Nothing
  61. Set msg = Nothing
  62. Set nms = Nothing
  63. Set fld = Nothing
  64. Set itm = Nothing
  65.   Exit Sub
  66. ErrHandler:  If Err.Number = 1004 Then
  67. MsgBox strSheet & " doesn't exist", vbOKOnly, "Error"
  68. Else
  69. MsgBox Err.Number & "; Description: ", vbOKOnly, "Error"
  70. End If
  71. Set appExcel = Nothing
  72. Set wkb = Nothing
  73. Set wks = Nothing
  74. Set rng = Nothing
  75. Set msg = Nothing
  76. Set nms = Nothing
  77. Set fld = Nothing
  78. Set itm = Nothing
  79. End Sub
Jan 24 '18 #1
Share this Question
Share on Google+
3 Replies


PhilOfWalton
Expert 100+
P: 1,430
Eric, I am confused.

This has been posted on the Access Forum, and yet deals with Excel

Your Question is unclear. Do yo want to Export from Outlook and Import into Excel. or Export from Excel and Import into Outlook.

The info I gave you on a previous post just links an Outlook folder to Access so gives the illusion that you are Exporting from Outlook and Importing into Excel.
The 2 queries then put that data into a table.

Excel is not my forte, but I suspect you can do something similar, but I wouldn't be surprised if you get lots of duplicate messages.

Phil
Jan 25 '18 #2

P: 12
Oops! Sorry about that Phil. Thank you for being the only person to respond to me on these things. So, what my boss wants is the report in access, which you helped me with and works great. However, he wants me to create a code to export Outlook folders directly into Excel so that they can run additional analysis on the emails.

Eric
Jan 25 '18 #3

PhilOfWalton
Expert 100+
P: 1,430
Why Excel & not Access?

Phil
Jan 25 '18 #4

Post your reply

Sign in to post your reply or Sign up for a free account.