auto retrieve file from email

niailmar

New member
Joined
Apr 4, 2012
Messages
30
Reaction score
0
Points
0
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.
 

niailmar

New member
Joined
Apr 4, 2012
Messages
30
Reaction score
0
Points
0
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-
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
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?
 

niailmar

New member
Joined
Apr 4, 2012
Messages
30
Reaction score
0
Points
0
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?
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
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.
 

niailmar

New member
Joined
Apr 4, 2012
Messages
30
Reaction score
0
Points
0
Answering your question:

  • the attachment file must be in .xls format.

  • 2012-04-11 Fred Bloggs - MyReport.xls​

Thanks for helping.
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
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

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
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 ([URL="http://www.excelguru.ca/"]www.excelguru.ca[/URL])
'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:  [URL]http://www.novell.com/developer/GWDevGuide.pdf[/URL]

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.
 

niailmar

New member
Joined
Apr 4, 2012
Messages
30
Reaction score
0
Points
0
okay ken,

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

Thank again,

nia.
 

niailmar

New member
Joined
Apr 4, 2012
Messages
30
Reaction score
0
Points
0
auto retrieves data from the emails and save as report

Hello ken...


Thank for your helping. It was really working.


Actually, I get a task about developing a system for Human Resources department which is get the claim form from all the staff. So I need to keep all the required data and save it into the database. But, I'm so weakness in understanding about macro function.


Can you show me how to retrieves data from the emails attachment from sender and save is as a report (in excel file) using vba code.


This must be done automatically where is all the report must be stored by monthly. It means, by clicking one button (eg: summary), it can be succesfully generate the report. All the data that need to be saved such as staff name, staff id, claims subject, date of claims, receipt number and claims amount. Then, from the report, it can be summarized and viewed who the person request claims for each months (included their claims ammount and date of claims) by year-to-date. All the report will be save into database.


Hopefully you can teach me. I'm really appreciate your kindness help.

thanks again,

-nia-
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Hi Nia,

I can try, but you're going to have to explain some things for me very very clearly to be able to guide you here.

Are you actually trying to extract data from the attachment? As in, are you trying to automate the process of opening the file, getting some data, and recording that somewhere?
 

niailmar

New member
Joined
Apr 4, 2012
Messages
30
Reaction score
0
Points
0
auto retrieves data from the emails and save as report

Hello Ken,

Absolutely yes! Thank a lot understand what I need.


After extract the data, I need the automated system to process of opening the file, getting some data, recording that the data and save it as report (the report must be in excel file).


From that report, user can view the report data continuously from year-to-date and can summarized it into other part. For example, user can view who the person make a claims or what the total of amount that have been made for each month.

really appreciated :)
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Okay, so the attachements that you are opening:
1) Will they always be Excel files?
2) Are they always structured EXACTLY the same inside, or do they vary?
 

niailmar

New member
Joined
Apr 4, 2012
Messages
30
Reaction score
0
Points
0
auto retrieves data from the emails and save as report

okay ken,

it Will always be the Excel files and the attachment that will opening is always structured exactly the same inside.

thank you
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,521
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Next questions:
-Can you tell me the sheet name and cell addresses that you want copied from the attachement?
-We'll also need the full file path/name to the workbook you want to save the data to
-If you could provide me with a template of some fake information that shows how you want the log file set up, that would be handy

Just a note here... The routine will need to be run manually. We won't be able to automate this to run every time an email is received in Groupwise, as I don't believe Groupwise has a VBA engine. It just lets Excel talk to it via VBA.
 

niailmar

New member
Joined
Apr 4, 2012
Messages
30
Reaction score
0
Points
0
View attachment PCM.xls

Based on attachment form above, the details that will be save in report which are:

1) name of employee
2) PMCare membership number
3) name of patient
4) date of consulting services
5) claim amount
6) receipt number

Only this form will be attached in any email from sender to person in charge at HR.
Below is an example the report look likes to be saved.

View attachment PM Care Mac.xlsx
 
Top