Excle VBA code to access outlook?

[williamhowar]

Hello,
I am writing a macro to automate some office work. We have multiple csvs that we get on mail. I am trying to create a macro that


Opens outlook -> searches in the subject line -> opens email -> opens attachment.


Would someone help me with some resources for this that would explain the logic behind it too?

[georgiboy]

Welcome to the forum williamhowar,

The below is a piece of code that I use, I run it from Excel, it loops through all emails in a specific folder of a shared mailbox. It looks at each email and then loops through the attachments of those emails, if it finds an attachment with specific words in the filename then it will save that file to a folder set in the code as 'FilePath', it will then mark that email as read.

I know it's not exactly what you are looking for but you should be able to modify it to suit your needs, to look at subject line and search for a string, open the file etc. Give us another shout if you need help.

Sub AttachmentSave()
    Dim wb As Workbook, cnt As Long, atmt As Object, omailitem As Object
    Dim olNS As Outlook.Namespace, olMailbox As Outlook.Folder, olInbox As Outlook.Folder, inFol As Outlook.Folder


    Set olNS = Outlook.GetNamespace("MAPI") ' loads all folders/ mailbox's
    Set olMailbox = olNS.Session.Folders.Item("Distribution Returns") ' shared mailbox
    Set olInbox = olMailbox.Folders.Item("Inbox") ' inbox of shared mailbox
    Set inFol = olMailbox.Folders.Item("Return Notification") ' folder to be searched
    Set wb = ThisWorkbook ' this workbook set to wb
    FilePath = wb.Path & "\Return Downloads\" ' filepath to save attachments to


    ' checks if the outlook folder is empty, if it is then exit the sub
    If inFol.Items.Count < 1 Then
        MsgBox "There are no mails to look at in the 'Return Notification' Outlook folder", vbExclamation, "Error"
        Exit Sub
    End If


    For Each omailitem In inFol.Items ' loop through the emails
        For Each atmt In omailitem.Attachments ' loop through the attachments
            If InStr(LCase(atmt.DisplayName), "Search String") Then ' checks if the words "Search String" exist in the attachment name
                cnt = cnt + 1 ' increments cnt by one (just a count)
                ' saves the attachment in a specific format with the cnt in the filename, saved with a xlsm extension
                atmt.SaveAsFile FilePath & Format(Now(), "yyyymmddhhmmss") & "-" & cnt & ".xlsm"
            End If
        Next
        omailitem.UnRead = False ' mark the email as unread
    Next
End Sub

[Gasman]

Not sure you can open the attachment (more than happy to be proved wrong), but would need to save it to file and then open that?
I have some code somewhere that runs in outlook, that saves attachments to file.

I could go looking for it, if you think that is the way you will go.?

[williamhowar]

Thanks for your answer. I got it.

fnaf 2