Link to home
Start Free TrialLog in
Avatar of Theva
ThevaFlag for Malaysia

asked on

Copy file from email attachment into folder

Hi Experts,

I would like to request Experts help to write a script Copy and Paste an e-mail attachment (.xls) into folder “D:\Test” if the Excel workbook title stars with “DA WORK _Live & Repeat”(the title could carries other additional words or date after "Live & Repeat).

I hope Experts will help me to create this feature.  


Avatar of David Lee
David Lee
Flag of United States of America image

Avatar of Theva

ASKER

Hi BlueDevilFan,

I'm totally lost looking at the solution, have many solution and I'm not sure where to put the document name, file type and the designated folder. Really needs your help.
Avatar of Theva

ASKER

Hi BlueDevilFan,

Need your help. I've attached the code. I'm not sure whether this is the right way to save the attachment. Hope you can assist.
Public Declare Function GetProfileString Lib "kernel32" Alias "GetProfileStringA" _
        (ByVal lpAppName As String, ByVal lpKeyName As String, _
        ByVal lpDefault As String, ByVal lpReturnedString As String, _
        ByVal nSize As Long) As Long

Private Declare Function ShellExecute Lib "shell32.dll" _
  Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
  ByVal lpFile As String, ByVal lpParameters As String, _
  ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Private Sub olkFolder_ItemAdd(ByVal Item As Object)
    'Edit the condition on the next line'
      If InStr(1, Item.Attachments, "DA WORK _Live & Repeat") Then
        'Edit the folder path on the next line'
        Item.SaveAs "\\bc04\FileShare\RServices\Generic Document\"
    End If
End Sub

Function RemoveIllegalCharacters(strValue As String) As String
    RemoveIllegalCharacters = strValue
    RemoveIllegalCharacters = Replace(RemoveIllegalCharacters, "<", "")
    RemoveIllegalCharacters = Replace(RemoveIllegalCharacters, ">", "")
    RemoveIllegalCharacters = Replace(RemoveIllegalCharacters, ":", "")
    RemoveIllegalCharacters = Replace(RemoveIllegalCharacters, Chr(34), "'")
    RemoveIllegalCharacters = Replace(RemoveIllegalCharacters, "/", "")
    RemoveIllegalCharacters = Replace(RemoveIllegalCharacters, "\", "")
    RemoveIllegalCharacters = Replace(RemoveIllegalCharacters, "|", "")
    RemoveIllegalCharacters = Replace(RemoveIllegalCharacters, "?", "")
    RemoveIllegalCharacters = Replace(RemoveIllegalCharacters, "*", "")
End Function


Private Sub olkMailItem_Open(Cancel As Boolean)
    If olkMailItem.Subject = "" Then olkMailItem.Attachments = "DA WORK _Live & Repeat"
End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of David Lee
David Lee
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Theva

ASKER

Hi BlueDevilFan,

>3.  Create a rule that fires for all messages with attachments.
>4.  Set the rule's action to "run a script" and select the script below as the one to run.

Not sure how to execute these 2 items.
You don't know how to create a rule?  
Avatar of Theva

ASKER

Hi,

Sound embarrassing but that's true.  
Ok.

1.  Click Tools > Rules and Alerts
2.  Click New Rule
3.  Select "Check messages when they arrive"
4.  Click Next
5.  Place a check in the box next to "which has an attachment"
6.  Click Next
7.  Place a check in the box next to "run a script"
8.  Click the blue underlined "a script" in the box labeled "Step 2"
9.  Select the script "SaveMyFile" from the list of scripts available
10. Click Finish
Avatar of Theva

ASKER

Hi,

Have tested. After message receive, error message pop-up shows "Compile error: sub and function not define" at this line : MessageAndAttachmentProcessor

Is that possible the "SaveMyFile" only executed once user open the e-mail?

Please assist
Did you add in the code from that article?
Avatar of Theva

ASKER

Hi BlueDevilFan,

Have too many code there and I'm not sure which is the right one. I'm sorry if this create too much trouble.
Avatar of Theva

ASKER

Hi,

are you referring to this code:
Public Declare Function GetProfileString Lib "kernel32" Alias "GetProfileStringA" _
        (ByVal lpAppName As String, ByVal lpKeyName As String, _
        ByVal lpDefault As String, ByVal lpReturnedString As String, _
        ByVal nSize As Long) As Long

Private Declare Function ShellExecute Lib "shell32.dll" _
  Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
  ByVal lpFile As String, ByVal lpParameters As String, _
  ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Open in new window

That's part of it.  There's a lot more below that.  
Avatar of Theva

ASKER

Hi,

Can I copy the whole script and run the "Sub SaveMyFile" script?
All of the rest of the code from that article has to be present in Outlook for things to work.  
Avatar of Theva

ASKER

Hi,

I copied the whole script and tested, however it shows error at this line as "User define type not defined" at this line:

Sub MessageAndAttachmentProcessor(Item As Outlook.MailItem, _
    Optional bolPrintMsg As Boolean, _
    Optional bolSaveMsg As Boolean, _
    Optional bolPrintAtt As Boolean, _
    Optional bolSaveAtt As Boolean, _
    Optional bolInsertLink As Boolean, _
    Optional strAttFileTypes As String, _
    Optional strFolderPath As String, _
    Optional varMsgFormat As OlSaveAsType, _
    Optional strPrinter As String)
What version of Outlook are you using?
Avatar of Theva

ASKER

Hi,

I'm using 2007.
Change this line

objFSO As FileSystemObject

to

objFSO As Object

Avatar of Theva

ASKER

Hi,

Cool, its working no, Thanks. Can we use the same solution for 2003? Is that possible the attachment only copied to the folder after user has opened the mail?
Avatar of Theva

ASKER

Hi BlueDevilFan,

Also notice the attachment copied as well .jpg file in in the folder. This file is actually at the email body (company logo). How to avoid this?  
"Can we use the same solution for 2003?"
It should work, but I've not tested the code in Outlook 2003.

"Is that possible the attachment only copied to the folder after user has opened the mail?"
Not with this code and not easily.

"Also notice the attachment copied as well .jpg file in in the folder."
Don't know how that's possible.  I'm not doubting you, but the code is designed to only save the file types specified.  The only file type we asked it to save is xls.
Avatar of Theva

ASKER

Hi,

Thanks for the superb code.
You're welcome.