Open attachment of email

[freshtomm]

Hi,

is it possible to open Excel workbook which is attached to recieved email in Outlook, copy data from that workbook and paste them into another workbook by VBA?

Thanks for any advice.

T.

 

[Deepak]

Yes possible but direct opening from outlook by code seems not possible. First it will save it to drive then open >copy paste. All are by code itself.

 

[freshtomm]

And would you share that code please? I didnt find it here.

 

[Deepak]

Soon.

 

[Monty]

Hello

Sub Freshtomm()
    Dim olFolder As Outlook.MAPIFolder
    Dim att As Outlook.Attachment
    Dim strFilePath As String
    Dim fsSaveFolder As String

    fsSaveFolder = "C:\test\" 'Change your folder path

    strFilePath = "C:\temp\"

    Set olFolder = Application.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)

    For Each msg In olFolder.Items
        While msg.Attachments.Count > 0
            bflag = False
            If Right$(msg.Attachments(1).Filename, 3) = "msg" Then
                bflag = True
                msg.Attachments(1).SaveAsFile strFilePath & strTmpMsg
                Set msg2 = Application.CreateItemFromTemplate(strFilePath & strTmpMsg)
            End If
            sSavePathFS = fsSaveFolder & msg2.Attachments(1).Filename


    End If
End Sub

.

Try this...Hope you like it.

 

[freshtomm]

Hello

Sub Freshtomm()
    Dim olFolder As Outlook.MAPIFolder
    Dim att As Outlook.Attachment
    Dim strFilePath As String
    Dim fsSaveFolder As String

    fsSaveFolder = "C:\test\" 'Change your folder path

    strFilePath = "C:\temp\"

    Set olFolder = Application.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)

    For Each msg In olFolder.Items
        While msg.Attachments.Count > 0
            bflag = False
            If Right$(msg.Attachments(1).Filename, 3) = "msg" Then
                bflag = True
                msg.Attachments(1).SaveAsFile strFilePath & strTmpMsg
                Set msg2 = Application.CreateItemFromTemplate(strFilePath & strTmpMsg)
            End If
            sSavePathFS = fsSaveFolder & msg2.Attachments(1).Filename


    End If
End Sub

.

Try this...Hope you like it.

Thank you very much, it works really well. And what if i wanted to save attachments only from e-mails recieved that day? Is it possible?

T.

 

[Monty]

Hello Freshtomm.
You are welcome!!!!!!
Can u give me more clarification on Second Question...To help u..

Monty

 

[freshtomm]

I tried to run that macro at my work, and it says "Compile error: User-defined type not defined" with marked first line of the code. Do you know why is that?

To my second question - I am getting emails with attachmens every day, and i want to save attachments only from emails received today. Is it possible?

Thanks.

T.

 

[Deepak]

I tried to run that macro at my work, and it says "Compile error: User-defined type not defined" with marked first line of the code. Do you know why is that?

To my second question - I am getting emails with attachmens every day, and i want to save attachments only from emails received today. Is it possible?

Thanks.

T.

For the error - you need to add a ref of outlook object library by below...
Vba>tools>reference >outlook12.0/15.0 or what u have.

https://msdn.microsoft.com/en-us/library/office/ff865816.aspx