importing Outlook Inbox

Davers · Oct 17, 2003

Good Morning everyone. A while ago I found a neat bit o' code that will import your Outlook inbox to excel. Pretty cool. By trial and error, I managed to add a field or two but I think I'm stuck. I have an Outlook Template that gets sent to me from all our departments, sort of like an incident report. I need to copy and paste the fields to a spread sheet. Does anyone know how to import the fields of an Outlook Form??? Here is the code...remember..it's not mine, and I don't recall who to give credit to...sorry....

Code:

Sub ListAllItemsInInbox()
Dim OLF As Outlook.MAPIFolder, CurrUser As String
Dim EmailItemCount As Integer, i As Integer, EmailCount As Integer
    Application.ScreenUpdating = False
    Workbooks.Add ' create a new workbook
    ' add headings
    Cells(1, 1).Formula = "Subject"
    Cells(1, 2).Formula = "Recieved"
    Cells(1, 3).Formula = "Attachments"
    Cells(1, 4).Formula = "Read"
    Cells(1, 5).Formula = "Sender"
    Cells(1, 6).Formula = "Body"
    
    With Range("A1:F1").Font
        .Bold = True
        .Size = 14
    End With
    Application.Calculation = xlCalculationManual
    Set OLF = GetObject("", "Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(olFolderInbox) ' innboksen
    EmailItemCount = OLF.Items.Count
    i = 0: EmailCount = 0
    ' read e-mail information
    While i < EmailItemCount
        i = i + 1
        If i Mod 50 = 0 Then Application.StatusBar = "Reading e-mail messages " & Format(i / EmailItemCount, "0%") & "..."
        With OLF.Items(i)
            EmailCount = EmailCount + 1
            Cells(EmailCount + 1, 1).Formula = .Subject
            Cells(EmailCount + 1, 2).Formula = Format(.ReceivedTime, "dd.mm.yyyy hh:mm")
            Cells(EmailCount + 1, 3).Formula = .Attachments.Count
            Cells(EmailCount + 1, 4).Formula = Not .UnRead
            Cells(EmailCount + 1, 5).Formula = .SenderName
            Cells(EmailCount + 1, 6).Formula = .Body
        End With
    Wend
    Application.Calculation = xlCalculationAutomatic
    Set OLF = Nothing
    Columns("A:F").AutoFit
    Range("A2").Select
    ActiveWindow.FreezePanes = True
    ActiveWorkbook.Saved = True
    Application.StatusBar = False
End Sub

Thanks for the help..

Dave M.

JRSqualid · Oct 17, 2003

Hi Davers

I think of been doing pretty much the same sort of thing and got some code from here it been quite excellent and thanks tall all that have helped me recently. . to get the values from the template i used the following

Cells(i, 5) = Val(MItem.UserProperties("Response time"))

(Val is to make sure the numbers come in numeric format.)

The UserProperties specifies the filed in the custom template.

Hope that what you needed

Cheers

jon

Davers · Oct 17, 2003

Thanks for your help Jon, I appreciate it. I can't wait to see if I can get this to work...it will save me a couple of minutes of time a day...

Thanks again and have a great weekend!

Dave M.

Davers · Oct 17, 2003

Hi Jon,

I can't seem to figure out how to incorporate that into my code...would you be able to post your example??

Thanks,

Dave M.

JRSqualid · Oct 17, 2003

Dave

Hi igot this most of thiscode from the board from Ivan Moala (thanks ivan)

Code:

Sub Getinboxdetails()

Dim OutApp As Object    'Outlook.Application
Dim NmSpace As Object   'Outlook.NameSpace
Dim Inbox As Object     'Outlook.MAPIFolder
Dim MItem As Object     'Outlook.MailItem
Dim i As Long
Dim x As Integer
Dim Feedb As String
Set OutApp = CreateObject("Outlook.Application")
Set NmSpace = OutApp.GetNamespace("MAPI")
Set Inbox = NmSpace.GetDefaultFolder(6)
i = 1
'// In case of UNDELIVERABLES
On Error Resume Next
For Each MItem In Inbox.Items
  DoEvents
  '// Note Only looking for Items within THIS MONTH!
  If Month(MItem.ReceivedTime) = Month(Now) Then
       If Err Then Err.Clear: GoTo N
       i = i + 1
       Cells(i, 1) = MItem.SenderName
       Cells(i, 2) = MItem.Subject
       Cells(i, 3) = MItem.UserProperties("Call Number")  'Custom form Field 
       Cells(i, 4) = MItem.UserProperties("Engineer") 'Custom form Field 
       Cells(i, 5) = Val(MItem.UserProperties("Response time")) 
        'Custom form Field 
       Cells(i, 6) = Val(MItem.UserProperties("Courtesy/helpfulness")) 
        'Custom form Field 
       Cells(i, 7) = Val(MItem.UserProperties("awarness"))
          'Custom form Field 
       Cells(i, 8) = Val(MItem.UserProperties("communication"))
           'Custom form Field 
       Cells(i, 9) = Val(MItem.UserProperties("overall"))
            'Custom form Field 
       Cells(i, 10) = MItem.ReceivedTime
       Cells(i, 11) = MItem.UserProperties("feedback")
              'Custom form Field 

        End If
N:  Next MItem
Set MItem = Nothing
Set Inbox = Nothing
Set NmSpace = Nothing
Set OutApp = Nothing
Set Attach = Nothing
End Sub

Hope this is ok if they are not custom fields look at mailitems in the object browser of outlook VBE

Not long been doing this my self so but the above work on my outlook OFT i have been using

Cheers

Jon

Davers · Oct 17, 2003

I can't seem to get this to work for me. A couple of things I noticed...I've modified the code as follows, the rest is the same as the above example:

Code:

Cells(i, 1) = MItem.SenderName
Cells(i, 2) = MItem.Subject
Cells(i, 3) = MItem.UserProperties("IncidentDescription")  'Custom form Field

it imports the senders name, the subject, but nothing else, it also skips about 3 emails that I have in my inbox. If I delete this line:

Code:

Cells(i, 3) = MItem.UserProperties("IncidentDescription")  'Custom form Field

it does import all the sender names and subjects of all my emails in my in-box, including the sender name and subject of my custom form... I'm not sure if this makes a difference, but the information I need off of the form is on the second page of the form, not the first page...

Does anyone have any ideas out there as to what I'm doing wrong, or maybe a direction I could take? I've searched the Microsoft Knowledge base and they have examples that are close...but it seems nothing specific to exporting a custom forms data to excel...

Any help would be greatly greatly appreciated!

Have a good day,

Dave M.

JRSqualid · Oct 17, 2003

Dave

Were do you get the the field "IncidentDescription" from on the custom form if use the value tab on the properties control form for the field and use the field name there. As for the page 2 stuff i dont know about that im not at work now. But let me know how you get on.

Cheers

jon

Davers · Oct 17, 2003

Hey Jon, oh happy days!!!

I've figured it out...the only time you need to reference what page the TextBox is on, is if you are doing something to that TextBox, something like enabling it, or giving it a color. If you just want it's value, then you just have to use your code above Jon, but it seems I needed to add .Value after it. The name of my TextBox3 on the form is IncidentDescription, and I have another one called IncidentType, etc...here is how your code looks so I can use it....

Code:

Sub Getinboxdetails()

Dim OutApp As Object    'Outlook.Application
Dim NmSpace As Object   'Outlook.NameSpace
Dim Inbox As Object     'Outlook.MAPIFolder
Dim MItem As Object     'Outlook.MailItem
Dim i As Long
Dim x As Integer
Dim Feedb As String
Set OutApp = CreateObject("Outlook.Application")
Set NmSpace = OutApp.GetNamespace("MAPI")
Set Inbox = NmSpace.GetDefaultFolder(6)
i = 1
'// In case of UNDELIVERABLES
On Error Resume Next
For Each MItem In Inbox.Items
  DoEvents
  '// Note Only looking for Items within THIS MONTH!
  If Month(MItem.Subject) = Month(Now) Then
       'If Err Then Err.Clear: GoTo N
       i = i + 1
       Cells(i, 1) = MItem.SenderName
       Cells(i, 2) = MItem.Subject
       Cells(i, 3) = MItem.UserProperties("IncidentType").Value
       Cells(i, 4) = MItem.UserProperties("IncidentDescription").Value
  
        End If
N:  Next MItem
Set MItem = Nothing
Set Inbox = Nothing
Set NmSpace = Nothing
Set OutApp = Nothing
Set Attach = Nothing
End Sub

I commented the 'If Err Then Err.Clear: GoTo N part, that's what was causing the code to not list all of my emails, it would get to the form, find an error, and then just end. Once I commented it out, it listed all of my emails again. Then it was just a matter of finding help for UserProperties, and the example they had showed them using the .Value...and it worked!

Cool...Now I just need to figure out how to filter my .Subjects, and I'm home free!!!

Thanks for all of your help Jon! I really appreciate it!

Have a good weekend!!!

Dave M.

JRSqualid · Oct 17, 2003

Excellent news glad it got sorted it good to be able to helpout this time.

Just a reminder this routine only brings in mail for thie current month.

Davers · Oct 17, 2003

Hi again Jon, I've changed that part to look for a particular subject. I get a form serveral times a day where the subject always starts off with "INCIDENT REPORT:" and then there will be a short description after that. But the first 16 characters will always be this: "INCIDENT REPORT:" I modified the line of code to this:

Code:

If Left(MItem.Subject, 16) = "INCIDENT REPORT:" Then

and it seems to work fine!

Have a great day!!

Dave M.

importing Outlook Inbox

Davers

Well-known Member

JRSqualid

New Member

Davers

Well-known Member

Davers

Well-known Member

JRSqualid

New Member

Davers

Well-known Member

JRSqualid

New Member

Davers

Well-known Member

JRSqualid

New Member

Davers

Well-known Member

Similar threads

Share this page

importing Outlook Inbox

Well-known Member

New Member

Well-known Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock