VBA code to get emails from outlook to an excell file

[Oweri]

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

 

[dougmarkham]

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...

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]

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...

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]

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]

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]

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.