Auto download of Excel files in emails

  • Thread starter Thread starter Chrisso
  • Start date Start date
C

Chrisso

Hi - sorry for posting in this group as sure this is not quite the
right group but only related. However I cannot find a better group and
have always had helpful replies in this group.

I have an Excel reporter that gets it's raw data from another
application in the form of another Excel file. My reporter imports the
data and presents charts etc.

At the moment users have provide the raw data Excel file but this is
haphazard and unreliable due to the fact that humans are lazy and take
holidays!

The application that produces the raw data Excel file can now be
configured to send the raw data Excel file via email but for full
automation I need this file on the network and not in an email inbox.

My company uses MS Exchange Server (not sure what version) so my
question is this: Is it possible to set up MS Exchange Server to
monitor an email account and when emails arrive automatically download
any attachments to a network location?

Thanks in advance for any pointers or help,
Chrisso
 
Hi - sorry for posting in this group as sure this is not quite the
right group but only related. However I cannot find a better group and
have always had helpful replies in this group.

I have an Excel reporter that gets it's raw data from another
application in the form of another Excel file. My reporter imports the
data and presents charts etc.

At the moment users have provide the raw data Excel file but this is
haphazard and unreliable due to the fact that humans are lazy and take
holidays!

The application that produces the raw data Excel file can now be
configured to send the raw data Excel file via email but for full
automation I need this file on the network and not in an email inbox.

My company uses MS Exchange Server (not sure what version) so my
question is this: Is it possible to set up MS Exchange Server to
monitor an email account and when emails arrive automatically download
any attachments to a network location?

Thanks in advance for any pointers or help,
Chrisso
Click to expand...
Hi

I would check to see if the program that is exporting this raw data can
save it via a Delimited .csv/.txt file format saving it directly to your
network drive location where you can access/import it automatically there.

HTH
Mick.
 
Set up rules alert on emails that contain this so that anytime this email gets flagged it goes into the new folder. This script will work in VBA on Outlook:

Sub SaveAttachmentsToFolder()
' This Outlook macro checks a named subfolder in the Outlook Inbox
' (here the "SecurityandCriticalPatches" folder) for messages with attached
' files of a specific type (here file with an "xlsx" extension)
' and saves them to disk. Saved files are timestamped.
' After attached files have been saved to the disk, items in named subfolder
' in the Outlook Inbox (here the "SecurityandCriticalPatches" folder)
' are moved into another named subfolder in the Outlook Inbox
' (here the "Processed" folder) so they are not processed again.
' NOTE: make sure the specified subfolder and save folder exist
' before running the macro. This code requires a reference to be set
' to the Microsoft Outlook 12.0 Object Model
On Error Resume Next
' Declare variables
Dim appOl As New Outlook.Application
Dim ns As Outlook.NameSpace
Dim Inbox As Outlook.MAPIFolder
Dim SubFolder As Outlook.MAPIFolder
Dim Destination As Outlook.MAPIFolder
Dim Item As Object
Dim Atmt As Outlook.Attachment
Dim FileName As String
Dim i As Integer
Dim varResponse As Variant
Set ns = appOl.GetNamespace("MAPI")
Set Inbox = ns.GetDefaultFolder(olFolderInbox)
Set SubFolder = Inbox.Folders("SecurityandCriticalPatches") ' Enter correct subfolder name.
Set Destination = Inbox.Folders("Processed") ' Enter correct subfolder name.
i = 0
' Check each message for attachments
For Each Item In SubFolder.Items
For Each Atmt In Item.Attachments
' Check filename of each attachment and save if it has "xlsx" extension
If Right(Atmt.FileName, 3) = "pdf" Then
' This path must exist! Change folder name as necessary.
FileName = "filepath" & _
Format(Item.CreationTime, "mmddyy_hhnn_") & Atmt.FileName
Atmt.SaveAsFile FileName
i = i + 1
End If
Next Atmt
Next Item
' Move messages into "Processed" folder
For Each Item In SubFolder.Items
SubFolder.Items.Item(i).Move Destination
If SubFolder.Items.Count >= 1 Then
Call SaveAttachmentsToFolder
Else
Exit Sub
End If

Next Item
' Clear memory
SaveAttachmentsToFolder_exit:
Set Atmt = Nothing
Set Item = Nothing
Set ns = Nothing
Set appOl = Nothing
Exit Sub

End Sub



>>>

If you want Outlook to Automatically do this on start up or new email. Paste this into Microsoft Office Outlook Objects> This Outlook Session:

Option Explicit

Private Sub Application_NewMail()

Call SaveAttachmentsToFolder

End Sub

Private Sub Application_Startup()

Call SaveAttachmentsToFolder

End Sub
 
Typo in:

' Check filename of each attachment and save if it has "xlsx" extension
If Right(Atmt.FileName, 3) = "pdf" Then

should be:


' Check filename of each attachment and save if it has "xlsx" extension
If Right(Atmt.FileName, 4) = "xlsx" Then
 
Last edited:
Thanks Mick

Hi

I would check to see if the program that is exporting this raw data can
save it via a Delimited .csv/.txt file format saving it directly to your
network drive location where you can access/import it automatically there.

HTH
Mick.
Click to expand...

Thanks Mick but that avenue is a dead end. We can only get in an email.

Chrisso
 
Back
Top