Results 1 to 4 of 4

Thread: Excle VBA code to access outlook?

  1. #1

    Excle VBA code to access outlook?

    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?

  2. #2
    Administrator VBAX Master georgiboy's Avatar
    Mar 2008
    Kent, England
    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
            omailitem.UnRead = False ' mark the email as unread
    End Sub
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved
    Click here for a guide on how to upload a file with your post

    Excel 365, Version 2405, Build 17628.20102

  3. #3
    VBAX Contributor
    Nov 2020
    Swansea,South Wales,UK
    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.?

  4. #4
    Thanks for your answer. I got it.

    fnaf 2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts