Open an attachment from an email in Outlook

**TypicalDru** · 02-10-2012, 11:15 AM

Hi,

I have done a lot of looking around for a simple solution to this problem and I can't seem to find a clear answer anywhere.

Bear with me as I am relatively new to VBA...

Currently I receive an email everyday with an excel file attached to it. What I would like the VBA code to do is access that email (with the subject "Volume"), from my inbox, then open the attachment. The rest within excel I can do but the outlook bit is seeming pretty tricky. After I have done my bit in excel I would then like VBA to close the email (that is if it needs to be opened to obtain the attatchment ).

Thanks
Dru

**OnErrorGoto0** · 02-10-2012, 11:32 AM

Hi,
You could use something like this to save the attachment to disk


Sub SaveOLAttachment()
    Dim oAPP As Object
    Dim oNS As Object
    Dim oFdr As Object
    Dim oItem As Object
    Dim oAtt As Object
    
    Const olFolderInbox As Long = 6
    
    ' adjust as required
    Const cstrSAVE_PATH As String = "C:\Temp\"
    
    Set oAPP = CreateObject("Outlook.Application")
    Set oNS = oAPP.GetNamespace("MAPI")

    Set oFdr = oNS.GetDefaultFolder(olFolderInbox)
    Set oItem = oFdr.Items.Find("[Subject] = 'Volume'")
    If Not oItem Is Nothing Then
        If oItem.Attachments.Count > 0 Then
            Set oAtt = oItem.Attachments(1)
            oAtt.SaveAsFile cstrSAVE_PATH & oAtt.fileName
        End If
    End If
End Sub