Solved

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

Posted on 2010-08-18
25
635 Views
Last Modified: 2012-05-10
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
Comment
Question by:matrix0511
  • 13
  • 12
25 Comments
 

Author Comment

by:matrix0511
ID: 33470879
Sample emails added with subject lines shown
sample-email-1.jpg
sample-email-2.jpg
sample-email-3.jpg
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33472223
It really will be much easier if you can just copy the subjects from the emails and paste them as text here.

Chris
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33472283
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33472915
For anyone interested ... the related question on which this is an enhancement was:

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

Chris
0
 

Author Comment

by:matrix0511
ID: 33473576
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33474257
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
 

Author Comment

by:matrix0511
ID: 33474301
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33474325
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
 

Author Comment

by:matrix0511
ID: 33474330
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
 

Author Comment

by:matrix0511
ID: 33474341
"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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33474397
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33474407
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:matrix0511
ID: 33474529
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33474626
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
 

Author Comment

by:matrix0511
ID: 33474718
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33474880
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
 

Author Comment

by:matrix0511
ID: 33475321
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33476411
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
 

Author Comment

by:matrix0511
ID: 33477114
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
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 500 total points
ID: 33477207
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
 

Author Comment

by:matrix0511
ID: 33477250
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
 

Author Comment

by:matrix0511
ID: 33477432
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
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33477798
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
 

Author Closing Comment

by:matrix0511
ID: 33478048
Outstanding work!!!!
0
 

Author Comment

by:matrix0511
ID: 33478102
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Find out how to use Active Directory data for email signature management in Microsoft Exchange and Office 365.
This article explains in simple steps how to renew expiring Exchange Server Internal Transport Certificate.
In this video we show how to create a mailbox database in Exchange 2013. We show this process by using the Exchange Admin Center. Log into Exchange Admin Center.: First we need to log into the Exchange Admin Center. Navigate to the Servers >> Data…
The video tutorial explains the basics of the Exchange server Database Availability groups. The components of this video include: 1. Automatic Failover 2. Failover Clustering 3. Active Manager

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now