get email address from outlook to excel

Question

found a code below and im trying to get the email address in my outlook inbox into excel but errors in line set objfolder

Sub getemail()
Dim objFolder As MAPIFolder
Dim strEmail As String
Dim objItem As Object
Dim counter As Integer
counter = 2

Set objFolder = Application.GetNamespace("Mapi").GetDefaultFolder(olFolderInbox)
For Each objItem In objFolder.Items
    If objItem.Class = olMail And objItem.ReceivedTime >= DateAdd("yyyy", -1, Now) Then
        strEmail = objItem.SenderEmailAddress
        Cells(counter, 1).Value = strEmail
        counter = counter + 1
    End If
Next
End Sub

@TimWilliams object does not support this property or method — k1dr0ck, Feb 07 '23 at 07:43

DavidLim · Answer 1 · 2023-02-07T08:41:01.570

2

I am not sure if it got to do with late binding/early binding issue. But you can try to change the "olFolderInbox" to 6.

If you want to use the early binding, make sure your Microsoft Outlook XX.X object library is enabled in your reference.

I typically will use late binding, it will be a lot simpler and you don't have to deal with the reference library version issue. When sharing the sub routine with another colleague or friend with different Excel version

Sub Get_Name()

    Dim OLApp As Object
    Dim oNameSpace As Object
    Dim oFolder As Object
    Dim oMail As Object

    Set OLApp = CreateObject("Outlook.Application")
    Set oNameSpace = OLApp.GetNameSpace("MAPI")
    Set oFolder = oNameSpace.GetDefaultFolder(6) 'olFolderInbox: 6, Inbox folder

    For Each oMail In oFolder.items
        On Error Resume Next
        Debug.Print oMail.SenderEmailAddress
        'Do your stuff here....
        On Error GoTo 0
    Next oMail

End Sub

edited Feb 07 '23 at 08:41

answered Feb 07 '23 at 08:08

DavidLim

63
6

it errors on debug.print line 'object does not support this property or method' – k1dr0ck Feb 07 '23 at 08:36
copy the code and try again. I forgotten to change one of the variable name, previously was oRecip, I change it to oMail for your reference. – DavidLim Feb 07 '23 at 08:38
i already edited it even before but still gives an error, even already reference the outlook object library – k1dr0ck Feb 07 '23 at 08:43
1

there are some item in the inbox doesn't have any attribute in SenderEmailAddress (empty), therefore it is causing the issue. you can just put a On Error Resume Next and On Error GoTo 0 to skip items without Sender Email. – DavidLim Feb 07 '23 at 08:45
1

one of such example will be recalled email. please look at the code now. added the 2 lines inside. you can open the watch window to see which are the items without sender email. subject should give you a big hint. by the way, do remember to replace the debug.print with your cells(counter,1).value = oMail.SenderEmailAddress. learn to use the watch window in the editor will help to pull more fields. happy coding =) – DavidLim Feb 07 '23 at 08:49
you are correct there were sent emails that bounced back in the inbox, it now retrieves the email address, thanks – k1dr0ck Feb 07 '23 at 09:06
3

@k1dr0ck Rather than hiding all errors in the multiple lines of code in `'Do your stuff here....`, apply the same test indicated in the question post `If objItem.Class = olMail`. `On Error Resume Next` is best applied on zero lines of code https://stackoverflow.com/questions/31753201/vba-how-long-does-on-error-resume-next-work/31753321#31753321. – niton Feb 07 '23 at 12:53

score 2 · Answer 2 · answered Feb 07 '23 at 16:50

2

Firstly, never loop through all items in a folder - folders can contain thousands of messages, use Items.Find/FindNext or Items.Restrict.

Secondly, Inbox folder can contain items other than MailItem, such as ReportItem or MeetingItem, which do not expose the SenderEmailAddress property. Check the Class property (exposed by all OOM object) that you indeed have a MailItem object.

Finally, Application intrinsic variable points to the Excel.Application object in Excel VBA. You need to explicitly create an instance of the Outlook.Application object if unless you are running your code in Outlook VBA.

set OlApp = CreateObject("Outlook.Application")
Set objFolder = OlApp.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
set restrictedItems = objFolder.Items.Restrict("[ReceivedTime] >= '02-07-2023' ")
For Each objItem In restrictedItems
    If objItem.Class = olMail Then
        strEmail = objItem.SenderEmailAddress
        Cells(counter, 1).Value = strEmail
        counter = counter + 1
    End If
Next

answered Feb 07 '23 at 16:50

Dmitry Streblechenko

62,942
4
53
78

tried it but it says object variable not set on the [if objitem.class=olmail] line -- Dim OLApp As Object Dim objFolder As Object Dim objItem As Object Dim restrictedItems As Object Dim olFolderInbox As Object Dim olMail As Object Dim strEmail As String Dim counter As Integer counter = 2 ..... – k1dr0ck Feb 08 '23 at 02:48
... Set OLApp = CreateObject("Outlook.Application") Set objFolder = OLApp.GetNamespace("MAPI").GetDefaultFolder(6) Set restrictedItems = objFolder.Items.Restrict("[ReceivedTime] >= '02-07-2023' ") For Each objItem In restrictedItems If objItem.Class = olMail Then strEmail = objItem.SenderEmailAddress Cells(counter, 1).Value = strEmail counter = counter + 1 End If MsgBox "Email address copied" Next – k1dr0ck Feb 08 '23 at 02:48
Did you add Outlook to the project references? Try to replace olMail with 43. Do not declare olMail as a variable - it is a constant (=43) – Dmitry Streblechenko Feb 08 '23 at 06:15
already had outlook added to the project preference just changed the olMail to 43 and it now works, thanks – k1dr0ck Feb 09 '23 at 09:45

score 0 · Accepted Answer · answered Feb 09 '23 at 09:47

by @Dmitry make sure to add outlook as project preference

Sub getemail()
Dim OLApp As Object
Dim objFolder As Object
Dim objItem As Object
Dim restrictedItems As Object
Dim olFolderInbox As Object
Dim strEmail As String
Dim counter As Integer
counter = 2

Set OLApp = CreateObject("Outlook.Application")
Set objFolder = OLApp.GetNamespace("MAPI").GetDefaultFolder(6)
Set restrictedItems = objFolder.Items.Restrict("[ReceivedTime] >= '02-07-2023' ")
For Each objItem In restrictedItems
If objItem.Class = 43 Then
strEmail = objItem.SenderEmailAddress
Cells(counter, 1).Value = strEmail
counter = counter + 1
End If
MsgBox "Email address copied"
Next
End Sub

get email address from outlook to excel

3 Answers3