Check if there are unread emails, with an attachment name containing "Production_Plan" as part of the name ,using excel - vba

Question

I am working on a project using excel-vba and outlook.

I am working in an excel workbook. I need to be able to run a macro in order to:

Check if there are unread emails,

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

Const olFolderInbox = 6

'~~> 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"

     Else

      MsgBox "Unread Email available In Inbox"

Exit Sub

If there are unread emails ,

I need to check if there are attachments in these unread emails.

If there are attachments,
i need to check if these attachments have an attachment name which contains "Production Plan" as part of the name.

This is because this attachment is sent to me regularly.

The attachment name will be in this manner

Production Plan (day - month - year).xls

If there is such an attachment then a MsgBox should be displayed in excel saying

Msg Box "Such attachments are available"

At this point in time i know how to do part 1 and 4.

I want to know: how to do part 2 and 3?

Please guide me on how this can be done.

update: I have made a small addition, which does not work. This is in order to display a msg, if there are attachments detected, but they are not of the form "Production Plan".

Else
      If Not att.Filename Like "Production Plan*.xls" Then
            MsgBox "no production plan attachment"
    Exit Sub
End If

Tim Williams Tim Williams · Accepted Answer · 2015-02-20T06:11:55

I don't have Outlook, so untested:

EDIT - to list all attachments

Dim oOutlook As Object
Dim oOlns As Object
Dim oOlInb As Object
Dim unRead, m As Object, att As Object
Dim some As String, other As String

Const olFolderInbox = 6

'~~> 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
Set unRead = oOlInb.Items.Restrict("[UnRead] = True")

If unRead.Count = 0 Then
    MsgBox "NO Unread Email In Inbox"
Else

    some = ""
    other = ""

    For Each m In unRead
        If m.Attachments.Count > 0 Then
            For Each att In m.Attachments
                If att.Filename Like "Production Plan*.xls" Then
                    some = some & vbLf & "  -  " & att.Filename
                Else
                    other = other & vbLf & "  -  " & att.Filename
                End If
            Next att
        End If
    Next m


    If some <> "" Or other <> "" Then
        MsgBox "Production Plans:" & vbLf & _
               IIf(some <> "", some, "{none}") & _
               vbLf & vbLf & "Other files:" & vbLf & _
               IIf(other <> "", other, "{none}"), _
               vbExclamation, "Unread mails with attachments!"

    End If


End If

You may find this mammoth answer from Siddharth Rout useful: Download attachment from Outlook and Open in Excel

Check if there are unread emails, with an attachment name containing "Production_Plan" as part of the name ,using excel - vba

1 Answers