VBA code to get emails from outlook to an excell file

Oweri

New Member
Joined
Jun 11, 2020
Messages
5
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hello

I have a code below to import my inbox to excel, but i am getting the following error
Private Sub Application_Startup()
Dim Test
'Const FilePath As String = "file:///C:\Users\DjOweriman\Documents\My%20WCEN%20Email_Vault.xlsm"
Dim outlookApp As Outlook.Application
Dim objectNS As Outlook.NameSpace
Set outlookApp = Outlook.Application
Set objectNS = outlookApp.GetNamespace("MAPI")
Set inboxItems = objectNS.Folders("owen@wcen.org.uk-Outlook").Folders("Inbox").Items

Can anyone help?
Thanks
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

dougmarkham

Board Regular
Joined
Jul 19, 2016
Messages
231
Office Version
  1. 365
Platform
  1. Windows
Hi Oweri,

I'm not an expert by any means in Outlook VBA; however, I made a few models recently to enable me to control folders etc., from excel by calling excel sheets.
During that project, I came across the below code.
I wouldn't be able to help you much if it didn't work (as I'm pretty new to it myself), but it's a start: you can try the following...

VBA Code:
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(xlSheet.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

The script came from the following source: Output to Excel from Outlook using VBA

Kind regards,

Doug.
 

Oweri

New Member
Joined
Jun 11, 2020
Messages
5
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi Oweri,

I'm not an expert by any means in Outlook VBA; however, I made a few models recently to enable me to control folders etc., from excel by calling excel sheets.
During that project, I came across the below code.
I wouldn't be able to help you much if it didn't work (as I'm pretty new to it myself), but it's a start: you can try the following...

VBA Code:
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(xlSheet.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

The script came from the following source: Output to Excel from Outlook using VBA

Kind regards,

Doug.

Hi Doug,

Thanks for giving me a start. I very new to this as well but I will try.

I think I have an issue in setting up the inbox location as I still getting this error
' Locate the Folder in Outlook. I've left out some of the details here because this part works fine
Call FindFolder(FolderTgt, FolderNameTgt, "Inbox")
 

Oweri

New Member
Joined
Jun 11, 2020
Messages
5
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi Doug,

Thanks for giving me a start. I very new to this as well but I will try.

I think I have an issue in setting up the inbox location as I still getting this error
' Locate the Folder in Outlook. I've left out some of the details here because this part works fine
Call FindFolder(FolderTgt, FolderNameTgt, "Inbox")

Hi Doug

I think I have nailed the problem, no error on startup now changed the inbox name. I am now trying to test it

Thanks
 

Oweri

New Member
Joined
Jun 11, 2020
Messages
5
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi Doug

I think I have nailed the problem, no error on startup now changed the inbox name. I am now trying to test it

Thanks

Hi Doug

I am still getting an error pointing

' Locate the Folder in Outlook. I've left out some of the details here because this part works fine
Call FindFolder(FolderTgt, FolderNameTgt, "olFolderInbox")

Any help on locating the folder?
 

dougmarkham

Board Regular
Joined
Jul 19, 2016
Messages
231
Office Version
  1. 365
Platform
  1. Windows
Hi Oweri,

I just realized it is a Sub that calls another Sub that the user who posted it failed to add to his post, so although his problem was fixed, nobody else can run it.
My apologies.
However, there are loads of outlook VBA macros to send email to excel.
An example would be: How To Import Your Outlook Emails Into Excel With VBA | How To Excel

If you google search on: "outlook vba export emails to excel", you'll get tonnes of hits.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,959
Messages
5,627,861
Members
416,278
Latest member
CellHell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top