• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 660
  • Last Modified:

Can I have code created in VBA to allow Outlook Email messages to save to sharepoint folders?

This question was setup for chris_bottomley to continue to assist me in creating code to save Outlook emails to a sharepoint location automatically. With functionality to save multiple emails with the same subject line with unique file names.


Sub Q_26408152()

    If Application.Inspectors.count = 0 Then Exit Sub
    If Application.ActiveInspector.CurrentItem.Class = olMail Then
        'MsgBox "Code to follow!"
        With Application.ActiveInspector.CurrentItem
            .saveas "\\cth-ws01\corp\IT\Shared Documents\Project Documents\" & FileNameCharsOnly(.Subject) & ".msg", olMsg
'            .saveas "c:\deleteme\" & FileNameCharsOnly(.Subject) & ".msg", olMsg
        End With
    End If
End Sub

Function FileNameCharsOnly(str As String) As String
Dim regEx As Object
Dim matches As Object
Dim arr() As String
Dim cnt As Integer
Dim dirColon As Boolean

    dirColon = Mid(str, 2, 1) = ":"
    Set regEx = CreateObject("vbscript.regexp")
    With regEx
        .Global = True
        .IgnoreCase = True
        .Pattern = "[^A-Za-z0-9$ %'\-_@~`\(\)\+\\,;=\[\]§-ÿ]"
    End With
    FileNameCharsOnly = regEx.Replace(str, " ")
    regEx.Pattern = " {2,}"
    FileNameCharsOnly = regEx.Replace(FileNameCharsOnly, " ")
    'If dirColon Then FileNameCharsOnly = Replace(FileNameCharsOnly, " ", ":", 1, 1)

End Function
0
matrix0511
Asked:
matrix0511
  • 13
  • 12
1 Solution
 
matrix0511Author Commented:
Sample emails added with subject lines shown
sample-email-1.jpg
sample-email-2.jpg
sample-email-3.jpg
0
 
Chris BottomleyCommented:
It really will be much easier if you can just copy the subjects from the emails and paste them as text here.

Chris
0
 
Chris BottomleyCommented:
The following assigns a suffix to each mail and hopefully therefore meets this requirement.

Chris
Sub Q_26408152()
Dim intIncrement As Integer
Dim fn As String
Dim ft As String
Dim fso As Object
    
    If Application.Inspectors.count = 0 Then Exit Sub
    If Application.ActiveInspector.CurrentItem.Class = olMail Then
        'MsgBox "Code to follow!"
        Set fso = CreateObject("scripting.filesystemobject")
        With Application.ActiveInspector.CurrentItem
            fn = "\\cth-ws01\corp\IT\Shared Documents\Project Documents\" & FileNameCharsOnly(.ConversationTopic)
            fn = "c:\deleteme\" & FileNameCharsOnly(.ConversationTopic)
            ft = ".msg"
            intIncrement = 1
            Do While fso.FileExists(fn & "_" & intIncrement & ft)
                intIncrement = intIncrement + 1
            Loop
            .saveas fn & "_" & intIncrement & ft, olMsg
        End With
    End If
End Sub

Open in new window

0
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
Chris BottomleyCommented:
For anyone interested ... the related question on which this is an enhancement was:

http://www.experts-exchange.com/Q_26408152.html

Chris
0
 
matrix0511Author Commented:
Ok. Here is one subject line I copied:

Fwd: CTH1100034 - DV & PY Package Request - Project CTH310109 (Base Machining)


Here is another:

Fwd: CTH1100051_F31121 Trigger_Package Build Request_PY
0
 
Chris BottomleyCommented:
THis is the subject of the next question to move them to sub folders, but for understanding ...

Fwd: CTH1100034 - DV & PY Package Request - Project CTH310109 (Base Machining)
is placed in the project folder in outlook "CTH1100034 ..."

Fwd: CTH1100051_F31121 Trigger_Package Build Request_PY
Similarly placed in outlook project folder "CTH1100051" ... or is it "CTH1100051_F31121"

Chris
0
 
matrix0511Author Commented:
Yes, in any of these subject lines, the focus is on the project number only. Because that is what the project folder will be named on Sharepoint sub folder.

so CTH1100034, CTH1100051, ETC.
0
 
Chris BottomleyCommented:
Okay that's set the baseline for the next cycle ... how are we on the functionality in this question to save versions of the documents to the root folder?

Chris

0
 
matrix0511Author Commented:
Chris, when you say..."placed in Outlook project folder". You are talking about the new project folder that you will create on Sharepoint right??

Or are you talking about creating a folder "in Outlook"?

I got a little confused on your question there. please clarify. thanks.
0
 
matrix0511Author Commented:
"how are we on the functionality in this question to save versions of the documents to the root folder?"

Chris, could you rephrase the question? I don't understand what your asking for. Thanks.
0
 
Chris BottomleyCommented:
Perhaps some misunderstanding so ...

1. The first question wanted the files in the sharepoint folder.  That capability was met there but it became clear that you wanted them into sub folders based on the project in the mail subject.
2. You also indicated a requirement to save the mails with the same subject as versions.
3. I requested that the first question be accepted on the grounds that the initial requirmeent was met ... and workload was definitely on the high side - not that i'm complaining.
4. I tried to suggest wording for the 'second question' to reflect he sub folders requirement however you seemed to word it for the multiple version support so that is the subject of this question.
5. The next question once the versions are suitably saved and identified, (albeit in the root of the sharepoint server) will be to implement some functionality to save them to sub folders.

Chris
0
 
Chris BottomleyCommented:
ALso there is a bug in my post from my testing scenario!

Chris
Sub Q_26408152()
Dim intIncrement As Integer
Dim fn As String
Dim ft As String
Dim fso As Object
    
    If Application.Inspectors.count = 0 Then Exit Sub
    If Application.ActiveInspector.CurrentItem.Class = olMail Then
        'MsgBox "Code to follow!"
        Set fso = CreateObject("scripting.filesystemobject")
        With Application.ActiveInspector.CurrentItem
            fn = "\\cth-ws01\corp\IT\Shared Documents\Project Documents\" & FileNameCharsOnly(.ConversationTopic)
'            fn = "c:\deleteme\" & FileNameCharsOnly(.ConversationTopic)
            ft = ".msg"
            intIncrement = 1
            Do While fso.FileExists(fn & "_" & intIncrement & ft)
                intIncrement = intIncrement + 1
            Loop
            .saveas fn & "_" & intIncrement & ft, olMsg
        End With
    End If
End Sub

Open in new window

0
 
matrix0511Author Commented:
Ok. So, at this point what specific new question do you want me to create now in Experts Exchange?

Do you want me to create a new question that asks the following:

Can you setup code to save my Outlook email messages to sub folders on my sharepoint folder location?
0
 
Chris BottomleyCommented:
At the moment nothing, we need to make sure you are getting copies saved to the root folder so that when we progress to that next stage ( and the content sounds fine when we come to it) the copies are already working and can be expected to drop into the correct sub folder

Once this bit is also working then we can move to the next round ... but only then otherwise we risk changing something in one thread and not the other.

Chris
0
 
matrix0511Author Commented:
Ok. so at this  point you want me to copy the code below into VBA in Outlook and create a button to seee if it copies the email to sharepoint?

Or are we waiting for you to finish something first?


Sub Q_26408152()
Dim intIncrement As Integer
Dim fn As String
Dim ft As String
Dim fso As Object
   
    If Application.Inspectors.count = 0 Then Exit Sub
    If Application.ActiveInspector.CurrentItem.Class = olMail Then
        'MsgBox "Code to follow!"
        Set fso = CreateObject("scripting.filesystemobject")
        With Application.ActiveInspector.CurrentItem
            fn = "\\cth-ws01\corp\IT\Shared Documents\Project Documents\" & FileNameCharsOnly(.ConversationTopic)
'            fn = "c:\deleteme\" & FileNameCharsOnly(.ConversationTopic)
            ft = ".msg"
            intIncrement = 1
            Do While fso.FileExists(fn & "_" & intIncrement & ft)
                intIncrement = intIncrement + 1
            Loop
            .saveas fn & "_" & intIncrement & ft, olMsg
        End With
    End If
End Sub
0
 
Chris BottomleyCommented:
Sort of ...

It's the same code as before but with the extensions required for this question, (hopefully).

Therefore replace the existing sub of the same name with this one and test it by checking for multiple copies in the sharepoint root folder.

Chris
0
 
matrix0511Author Commented:
Ok. But keep in mind Chris when I talk about "multiple copies" Im speaking of a scenario where I receive email #1 with subject: RE: CTH1200096 - Test Email - Aug 2010  on 8/19/2010. I click the button to save that email to sharepoint sub folder.

For this example let's say the code saved it as: RE: CTH1200096 - Test Email - Aug 2010.MSG  In a folder it created called: CTH1200096

Then a week later I receive an email with the same exact subject line but has different data (attachments) in the email. I click on the button to save the email to sharepoint in that sub folder it created a week before (CTH1200096). But since it already has the original email file saved in that sub folder, it recognizes that somehow (I guess your working on this) and saves the email with a unique extension.

So let's say it saves it with the file name ext:

RE: CTH1200096 - Test Email - Aug 2010 1.MSG

Is that all your understanding as well? That clicking that button will only save ONE COPY. But when the time comes to save another copy from another email with the same subject line as my example above it will save it with unique file name.

Please let me know if that is your understanding.

Thanks.


0
 
Chris BottomleyCommented:
Probably the same understanding because one scenario you didn't mention is if you repeat the process on the same email ... in this case yet another numbered file will be produced.

If you test the suppied change you should be able to see that.

Chris
0
 
matrix0511Author Commented:
Chris, I went and added the new code above to the module in Outlook and added the macro button to my email. restarted outlook and opened the email and clicked on the button but got a compile error. see below.
Compile-Error.jpg
0
 
Chris BottomleyCommented:
Did you delete the function filenamecharsonly as previously supplied?  If so reinsert it into the code module.

Chris
Function FileNameCharsOnly(str As String) As String
Dim regEx As Object
Dim matches As Object
Dim arr() As String
Dim cnt As Integer
Dim dirColon As Boolean
    
    dirColon = Mid(str, 2, 1) = ":"
    Set regEx = CreateObject("vbscript.regexp")
    With regEx
        .Global = True
        .IgnoreCase = True
        .Pattern = "[^A-Za-z0-9$ %'\-_@~`\(\)\+\\,;=\[\]§-ÿ]"
    End With
    FileNameCharsOnly = regEx.Replace(str, " ")
    regEx.Pattern = " {2,}"
    FileNameCharsOnly = regEx.Replace(FileNameCharsOnly, " ")
    'If dirColon Then FileNameCharsOnly = Replace(FileNameCharsOnly, " ", ":", 1, 1)

End Function

Open in new window

0
 
matrix0511Author Commented:
I'm confused now. Am I inserting two separate set's of code into the same module??

I have only been adding code to that one module. Not sure what you mean by "filenamecharsonly"
0
 
matrix0511Author Commented:
Ok, I just went back and added that function to the bottom of the code and saved it.

this time when I click the button it worked!! It actually saved a copy to the root of the sharepoint with an extention of "1". I pulled up a different email with same exact subject line and clicked the button and it saved a unique copy with a "2" extention. That is perfect! Just what I want.

Great job so far! So, what's the next step to get the code to create new sub folder and also save to existing sub folders?
Sharepoint-Save.jpg
0
 
Chris BottomleyCommented:
Next step is to close this question and open a new question using the related question button that will then appear.  This time of course it will be to save the emails to sub folders off the root as defined by the project number in the mail subject

Chris
0
 
matrix0511Author Commented:
Outstanding work!!!!
0
 
matrix0511Author Commented:
Ok. I have closed this question with high marks for you! :-)

I have also created a new question.

Title: Can you create code in Outlook to click a button to save email MSG to a network sub folder based on subject line?

ID: 26415611
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 13
  • 12
Tackle projects and never again get stuck behind a technical roadblock.
Join Now