Open Outlook attachment from specific sender with today's date

    Hi all,


    On a daily basis i receive an email with an attachment (the email will come from one of three different people). I want to be able to open this attachment, move the data into my workbook, then run my vba code on it (This to modify it, save as a txt file and close). The attachment has a different name each day - a string of 12 different letters or numbers, but always ends ".000001.csv", if that helps.


    My code to modify etc all works fine. The part i'm struggling with is the Outlook link. I have searched far and wide but i'm not finding anything suitable (although my lacking knowledge may also be at play here). The issue generally seems to be that people want code to loop through their entire inbox, whereas i just want it to find the most recent email from said recipient, execute then stop. It goes to a general inbox and therefore it has to be specific as i do not want to open 1000+ attachments by accident!


    I only receive one email per day from this recipient, hence a "today" qualifier may be useful (although not required) to prevent it opening yesterdays/previous email.


    I have my code if required, but none is relevant to the question so i don't want to 'muddy' the answer.


    Many thanks in advance!

    Re: Open Outlook attachment from specific sender with today's date


    Hi


    Welcome to OzGrid!!


    this might help you.


    [vb]Option Explicit


    Sub kTest()

    Dim olApp As Object 'Outlook.Application
    Dim olNS As Object 'Outlook.Namespace
    Dim olItems As Object 'Outlook.Items
    Dim olItem As Object 'Outlook.MailItem
    Dim olAttach As Object 'Outlook.Attachment
    Dim Flg As Boolean

    Const olFolderInbox As Long = 6

    On Error Resume Next
    Set olApp = GetObject(, "Outlook.Application")
    Err.Clear: On Error GoTo 0

    If olApp Is Nothing Then
    Set olApp = CreateObject("Outlook.Application")
    Flg = True
    End If

    Set olNS = olApp.GetNamespace("MAPI")

    Set olItems = olNS.GetDefaultFolder(olFolderInbox).Items

    For Each olItem In olItems
    If TypeName(olItem) = "MailItem" Then
    If Int(olItem.ReceivedTime) = Date Then
    Select Case olItem.SenderEmailAddress
    Case "[email protected]", "[email protected]"
    On Error Resume Next
    Set olAttach = olItem.Attachments.Item(1)
    Err.Clear: On Error GoTo 0
    If Not olAttach Is Nothing Then
    If olAttach.Filename Like "*000001.csv" Then
    '//save the file
    olAttach.SaveAsFile Environ("temp") & Application.PathSeparator & olAttach.Filename
    '// now call you macro to process the data.

    GoTo Finish
    End If
    End If
    End Select
    End If
    End If
    Next

    Finish:

    If Flg Then olApp.Quit

    End Sub[/vb]

    Re: Open Outlook attachment from specific sender with today's date


    Hi Krishnakumar,


    First off i just want to say thanks ever so much for taking the time to reply, it always amazes me peoples kindness in taking the time to help others.


    Also, that the code works absolutely perfectly!


    Much appreciated,


    James

    Re: Open Outlook attachment from specific sender with today's date


    Hi Krishnakumar,


    Sorry to be a pest, but i have one more request..


    I have setup the code to run automatically upon opening the spreadsheet. This is fine except in the super rare instance where a different sender than the usual three, send me the email (typically this happened to me this morning for the first time in months, the second time having used the new spreadsheet :smash: ) . So, what i was thinking was maybe having a notebook/text file in the same directory as the spreadsheet, where i could list the possible email senders. This way any new senders i can add on the list, instead of having to try and access the spreadsheet without it auto-running. This would also mean my colleagues who are less able than i at vba (i know you're shocked, but they do exist) are able to update the list without searching through code.


    Many thanks,


    James


    p.s. if you can suggest a better option i'm happy to hear it!

    Re: Open Outlook attachment from specific sender with today's date


    Well, i could do that but it would still have to be a sheet in a separate workbook (Mine currently auto-runs upon open then auto-closes down). In this instance how would i reference the list of names in your code, assuming the book would be called names.xls, sheet 1, as an example?

    Re: Open Outlook attachment from specific sender with today's date


    Quote from jiminykricket;789161

    Well, i could do that but it would still have to be a sheet in a separate workbook (Mine currently auto-runs upon open then auto-closes down). In this instance how would i reference the list of names in your code, assuming the book would be called names.xls, sheet 1, as an example?


    In that case reading the text file would be better option. But if you are sure that the attachment always ends with 000001.csv then you can ignore the sender's email address in the code.

    Re: Open Outlook attachment from specific sender with today's date


    Actually yes that's a good point. Seeing as it checks for the filename anyway the senders name is relatively redundant other than to slightly speed it up. I'll just modify the code to exclude the sender as suggested.


    Many thanks,


    James

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login