CODEWITHSUNDEEP
vbaexceloutlook
user4585089
user4585089

Reputation:

Automatically connect to ms excel and check for unread emails using excel vba

I am doing a project based on excel-vba.

There are 2 users. User A and User B

User A has an excel workbook with a table. User B will be emailing a document regularly,to user A's outlook account.

My requirement is by using a macro to give 2 options to user A.

option 1: manually check if there is unread email from a particular user.

This is done by:

Opening outlook from within the excel workbook and then User A can manualy search through the outlook account.

I have successfully done this using the following code.

    Sub Open_Outlook()

    ' This Macro Opens Microsoft Outlook
    ' Runs an executable program

    Shell ("OUTLOOK")

    End Sub

option 2: Automatically check if there is unread email from a particular user.

This is done by:

  1. Create a Connection to Outlook.

  2. Checking if there is any unread email.

Sub ExtractFirstUnreadEmailDetails()

Dim oOutlook As Object
Dim oOlns As Object
Dim oOlInb As Object


'~~> Get Outlook instance
Set oOutlook = GetObject(, "Outlook.Application")
Set oOlns = oOutlook.GetNamespace("MAPI")
Set oOlInb = oOlns.GetDefaultFolder(olFolderInbox)

'~~> Check if there are any actual unread emails
If oOlInb.Items.Restrict("[UnRead] = True").Count = 0 Then
    MsgBox "NO Unread Email In Inbox"
    Exit Sub
End If

End Sub

I am getting an error when i run the code for the second option.

Run time error 429: Active X component cant create object.

What does this mean?

How do i change the code to get rid of the error and to run it succesfully?

Upvotes: 1

Views: 1953

Answers (1)

Dan Donoghue
Dan Donoghue

Reputation: 6206

olFolderInbox is a Outlook only constant Either define it as a constant in VBA like this:

Const olFolderInbox = 6

Or simply replace it with 6 in the Set oOlInb line

Upvotes: 0

Related Questions