Page 1 of 2 1 2 LastLast
Results 1 to 10 of 19

Thread: auto retrieve file from email

  1. #1

    auto retrieve file from email



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello and a very good day


    Currently I am using email by novell groupwise product.
    But I have some problem in how to consolidate all the relevent email by receiver.
    And at the same time, I want to automatic save all the attachment (in excel format) from that email that I receive to the specified folder at dekstop.
    Can you give me some function in vba code to solve that problem. All your kindness help, really appreciated.
    Thank you.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Hi niailmar, and welcome to the forum!

    I haven't had Novell Groupwise in my environment for several years, but I did write up this entry back when I did: http://www.excelguru.ca/content.php?...To-A-Directory

    Maybe it will help?
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3

    tagging function

    Thank you so much for helping me.


    By continue my previous task (auto retrieve file from email), now I need to tagging the received attachment file from Novell Groupwise (in xls format).


    The tagging function is to differentiate the name of attachment file from a same sender. It is because, it have some possibility to received more emel from a same sender.


    So, can you give some solution which is example coding using VBA code to solve this problem?


    Thanks again.


    Best regards,
    -Nia-

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Hi Nia,

    Just trying to understand here...

    Do you want to only extract .xls files from Groupwise and rename them when you save them?
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Yes. Actually I want to take out and copy that attachment file from Groupwise and rename (tagging) them before save it. But all this process must be done by automatic. It's means, by clicking 1 button, all these process can be done successfully.

    can you understand my explanation?

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    A couple more questions:

    1) What do you want to happen if the attachment is not a .xls file?
    • For example, what if it is a .doc (word) file?
    • How about a .xlsx (2007+ Excel file)?
    Should we just ignore them?

    2) How do you want the files named?If "Fred Bloggs" sends you a file called "MyReport.xls", would you like to see:
    • Fred Bloggs-MyReport.xls
    • MyReport-Fred Bloggs.xls
    • 2012-04-11 Fred Bloggs - MyReport.xls
    Something else?

    Just to be clear on this, I'm going to try and help, but with no enviornment to test it out I may not be able to get you all the way over the goal line. I will give an effort though.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  7. #7
    Answering your question:

    • the attachment file must be in .xls format.

    • 2012-04-11 Fred Bloggs - MyReport.xls
    Thanks for helping.

  8. #8
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    That answered the second question, but not the first.

    What about a .doc file? We cannot save that in .xls format, as it won't work. Do you want to ignore those files?
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  9. #9
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Okay, giving this a shot. If this does not work, I need to know what line is higlighted and we can start to debug from there.

    First, you MUST set a reference to the Groupware Type Library. To do this go into the Visual Basic Editor --> Tools --> References. Scroll down the list to find "Groupware Type Library" and check it. (I have no idea what version you have, so you may want to share that with me too.

    Once you've done that, insert a new module, delete any code lines that are in there, and paste in the following:
    Code:
    Option Explicit
    Private ogwApp As GroupwareTypeLibrary.Application
    Private ogwRootAcct As GroupwareTypeLibrary.Account
    
    Sub Groupwise_SaveAttachToFile()
    'Author       : Ken Puls (www.excelguru.ca)
    'Macro Purpose: Save all attachments of specified file type into a
    'user specified folder using Groupwise
    'NOTE:  Reference to Groupware Type Libary required
    'Object Model Documentation:  http://www.novell.com/developer/GWDevGuide.pdf
    
    Dim ogwFolder As Folder, _
        ogwFoundFolder As Folder, _
        i As Long, _
        sCommandOptions As String, _
        sMailPassword As String, _
        sLoginName As String, _
        sFolderToSearch As String, _
        sFileType As String, _
        sSavePath As String, _
        ogwMail As Mail
    
    'Change required variables here!
    sLoginName = "YourMailboxID"
    sFolderToSearch = "FolderToLookIn"
    sSavePath = "C:\Temp" 'do not add trailing \
    sFileType = "xls"
    
    'Set application object reference if needed
    If ogwApp Is Nothing Then 'Need to set object reference
        DoEvents
        Set ogwApp = CreateObject("NovellGroupWareSession")
        DoEvents
    End If
    
    'Create connection/login to email account
    If ogwRootAcct Is Nothing Then 'Need to log in
        'Login to root account
        If Len(sMailPassword) Then 'Password was passed, so use it
            sCommandOptions = "/pwd=" & sMailPassword
        Else 'Password was not passed
            sCommandOptions = vbNullString
        End If
        Set ogwRootAcct = ogwApp.Login(sLoginName, sCommandOptions, _
        , egwPromptIfNeeded)
        DoEvents
    End If
    
    'Search all mail items, and save any matching attachments to the
    'specified directory
    For Each ogwMail In ogwRootAcct.AllFolders.ItemByName(sFolderToSearch).Messages
        With ogwMail
            If .Attachments.Count = 0 Then
                'No attachments, so do nothing
    
            Else
                'Attachments found.  Save desired type to specified folder
                For i = 1 To .Attachments.Count
                    If Right(.Attachments(i).Filename, 3) = sFileType Then
                        .Attachments(i).Save _
                            sSavePath & "\" & _
                            Format(.CreationDate, "yyyy-mm-dd") & " " & _
                            .Sender.DisplayName & " - " & _
                            .Attachments(i).Filename
                    End If
                Next i
            End If
        End With
    Next ogwMail
    
    'Release all objects before closing
    Set ogwRootAcct = Nothing
    Set ogwApp = Nothing
    DoEvents
    End Sub
    Next, you need to update the section listed in the code as "'Change required variables here!" with your mailbox ID, the name of the Groupwise folder you want to search through and the directory you want to save the files to.

    After that, you should be able to run it. Hopefully I've picked up the right changes required to put in the date and sender name.

    Let me know how it works out.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  10. #10
    okay ken,

    Let me try it first. Then, I will show the result. I'm really appreciate your kindness help.

    Thank again,

    nia.

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •