How To Create a Macro in Outlook 2003 To Do a Save As to a Sharepoint Location

Take a look at this link as it will provide your guidelines to create a macro that will save to new file location.
http://www.rondebruin.nl/mail/folder2/saveatt.htm

Hope this helps~!

Yes!

When/how do you want the macro to run, for example if you can indicate a rule that identifiesd those rules we can do it that way, we can make a context button to do it or a button that you click to trigger the action.

If you can also identify the folder path for the save then I can code it more fully.

Chris

Thanks, but this is not quite what I'm trying to do.

When I have my email message open, I just want to be able to click a button to run a macro in Outlook to save that message as type: .MSG and default to my network drive location.

That's it. I can take it from there and save the message to the specific folder I need.

Any documentation on that??

HAve you created a button in the mail window to which we can assign a macro yet?

Chris

Hi Chris, no. As I mentioned, unfortunately, I'm not very savvy on the Outlook programming and modifications.

I'm sure creating a button is fairly easy though right??

Just to clarify what I'm trying to automate via Macro.

See screen attached screen print. It's really very simple what I'm trying to do.
8-17-2010-9-42-11-AM.jpg

The main thing is to establish if there is a directory path to sharepoint.

In the file explorer, can you navigate to teh folder where you want them saving ... if so please upload and then i'll try and walk you through the process.

Chris

Hey Chris, yes. There are two ways I have setup to access that Sharepoint location.

1.) URL: \\cth-ws01\corp\IT\Shared Documents\Project Documents

I can assess that URL either from file Explorer or web browser. See screen print below.

2.) File Explorer: I have mapped a drive (T) to the same location.  See other screen print below.

Let me know what you come up with. Thanks!
Sharepoint-location-via-URL.jpg
Network-Drive-Mapping.jpg

First off let's create a dummy button to test, the first step to which is some basic code which we will then update for the functionality required.

Insert the following sub into the VBE, on completion of which we will create a button to use.

CHris

To Create a macro:
------------------

Alt + F11 to open the macro editor

  For User Code:
     Insert | Module to insert a code module into the project
     In the project tree select the module.
     Insert the required macro(s) into the selected module, ('Module1' or similar)

Close the Visual Basic Editor.

Check Security as appropriate:
------------------------------

In the application select Tools | Macro | Security
2003 and Earlier : Select Medium
2007 : Outlook - Warnings for all Macros
     : Others - Enable a trusted location and inhibit macros otherwise so do both!
      : Disable Macros
            Office Button, (top left of the screen)
            Options
            Trust Centre
            Trust Centre Settings
            MAcro Settings
            Disable All MAcros with warnings
      : Enable Trusted Locations
            Office Button, (top left of the screen)
            Options
            Trust Centre
            Trust Centre Settings
            Trusted Locations
            Add a preferred location
            ENSURE YOUR FILE IS IN A TRUSTED LOCATION
            Re-open to ensure it is recognised if the above has been changed
            
Select OK

To run a macro:
---------------

Alt + F8
Select the macro
Select 'Run'

Sub Q_26408152()

    If Application.Explorers.count = 0 Then Exit Sub
    If Application.ActiveExplorer.Selection(1).Class = olMail Then
        MsgBox "Code to follow!"
    End If
End Sub

Select allOpen in new window

Ok. I've gotten all the way to this part. I"m stuck at the part where I'm trying to enable/disable macros.

Where do I go to find this? I don't see the "Office Button" you metioned or "Trust Centre". Is that in Word or Outlook somewhere??




Others - Enable a trusted location and inhibit macros otherwise so do both!
      : Disable Macros
            Office Button, (top left of the screen)
            Options
            Trust Centre
            Trust Centre Settings
            MAcro Settings
            Disable All MAcros with warnings
      : Enable Trusted Locations
            Office Button, (top left of the screen)
            Options
            Trust Centre
            Trust Centre Settings
            Trusted Locations
            Add a preferred location
            ENSURE YOUR FILE IS IN A TRUSTED LOCATION
            Re-open to ensure it is recognised if the above has been changed

Here's the steps I took. I copied the code you had at the bottom to the Module in VBA. Saved it.

Then, since I use Outlook 2003 I cicked: Tools | Macro | Security and Medium.

Then did the Alt-F8 and clicked Run and I get the message below.
step-1.jpg
Alt-F8.jpg
Macro-disabled-message.jpg

That bit of text is common ... you needed the bit:

2003 and Earlier : Select Medium
the rest is 2007 specific

Try closing and re-opening outlook as well.

Chris

To add a button to the email inspector:

1. Open a new email.
2. Right click in the menu areas and select customise
3. Commands | MAcros and find Q_26408152
4. Drag the macro name to a pre-existing toolbar.

It should be permanently displayed in emails when opened.  CLose the 'new' mail and open any mail to check the button presence, now click it and you should get the basic messagebox which proves the mechanism is OK.

Chris

Ok. After closing Outlook and reopening, when I hit Alt-F8 and then click run it now gives the small box below. Is that what its supposed to do at this point?


code-to-follow.jpg

Indeed

BAsically I try to do bite sized chunks as it's easier to see where understanding 'fails'

WHat you now need is some code to try and do the copy so to refresh:

When you click this new button you want the specific email saving to  \\cth-ws01\corp\IT\Shared Documents\Project Documents

as a msg.

Assuming yes, how is the mail to be labelled, for example subject content, what about duplicates and any invalid characters?

Chris

Ok. When I click on Commands | MAcros, I don't see any macros show up in the commands box to the right.

did i miss something?
Customize.jpg

when I open MVB again this is what it defaults to. Does this look right?


MVB.jpg

Yes.   \\cth-ws01\corp\IT\Shared Documents\Project Documents is the correct path

how is the mail to be labelled? Well, I get different emails from different people. Below is an example of two different emails I may recieve. so, the subject line and "from" will be different. But the key "constant" is that I will need to be able to save as ".MSG" and save to that network path I sent you.


sample-email-1.jpg

Sample Email 2
sample-email-2.jpg

But for some reason I still can't get any macros to show in the Customize box to the right.

Are you clicking within teh mails toolbar area and are you sure there is only one copy of the sub anywhere ... expand all the folders and check modules.

Chris

See my screen prints below. I dont' know what I'm missing. I know the code is there because i can run it. It gives that message: "Code to follow". But for some reason Outlook can't find or see it when I go to select it.
Code.jpg
Code-2.jpg
step-1.jpg
step-2.jpg

NOt eit lloks as though you have the sub in more than one location .. delete it from thisOtlookSession and ensure only one of the notmal modules has the code within.

OPtion explicit requires all variables to be pre-defined ... it helps to avoid data typing issues - and is my own default.

Chris

Man, this is frustrating. I removed the second module. And I double-clicked on the "ThisOutlookSession" but the code box was blank which means it doesnt have the code in it. But i'm not able to delete "thisoutlooksession" anyway.

Is it possible it's not saving the code in the right location??

>>> Man, this is frustrating

Too right, if I Was there it would probably take but a moment!

In teh VBE check in teh tools | references menu for any that indicate missing - haven't seen it as an issue in this scenario before but i'm struggling to figure out the problem otherwise.

Chris

Chris, this is what I see below. Are there "References" that I need to look for to make sure are checked?


References.jpg

Rats! ... that looks ok.

IN thisOutlookSession add the following code and restart outlook ... saving the VB project of course at the prompt

Private Sub Application_Startup()
    msgbox "MAcros Enabled!"
end sub

DO you get the prompt on startup?

Chris

Ok Chris, we have made some progress here. I forgot to tell you I tried to create a button and add a dummy code before I started askeing for help. I had added it to my MAIN Outllok window. But I just grabbed it and clicked the x to remove. But when I right-click on the Main Oulook window (not the New Email window) and go to Customize and select macros, I see the macro.

But I need it in the new Email box. Is there a way to remove it from here and add it to the email box. When I right-click in the new Email box I don't see the macro as an option. I only see it as an option when I right-click the menu location on the main Outlook window. See below.

Project.jpg

Now it's just sitting on my Menu bar at the top of the Main Outlook screen and I can't remove it. When I click to run it to test I get a runtime error.


Menu-Bar.jpg

Btw, after adding that test code to enable macros I saved and exited and reopened Outlook.

And Yes. It does give a box that says: Macros Enabled!

To remove it enter customise and drag it from the window.

WHen it's 'gone' retry adding it to a new mail window.

CHris

It won't let me drag it out to remove it from the box. The only thing it will let me do is grab it and drag it to the top of the menu bar where File Edit View are. And even then it never competely removes it from the Customize box.

Ok. I got the command to go away from the Customize box. I had to end up deleteing the code for it to go away.

Now I guess I should create new module to add that code to again huh?

So bazaar. I reads the code you sent me to add about enableing macros. Everytime I close and open Outlook it runs that code and enables macros. But I can never get it to show the macro avaialble to select.

crazy

Once again, I see the macro now from the main outlook window. But can't see it from new email box.

Is this a business pc and is there any chance they've done something with the policy to disable edits to the email template?

Chris

Chris, if you try to do the same thing on your Outlook, does it work for you? Are you able to see your macro from the New email box?

Ok, good news and bad news. I FINALLY got the code to show. I was trying to add it to an OPEN email message window. It has to be closed. Now when I open an email msssage I see the new code. see below.

However, when I click it I get a runtime error.

Let me know the next steps to try. Thanks for hanging in there with me!

Runtime-Error.jpg

Hang on a second. I just noticed that I dont' have access to that link at the moment (\\cth-ws01\corp\IT\Shared Documents\Project Documents\) I'm going to reboot my laptop.

Ok. After rebooting I can pullup my URL again. And when i click on the code button I added to the new email box it no longer gives a runtime error. However, it just doesn't  do anything. Nothing happens.

MOdified the Q_26408152 below, (keep the other sub)

When the code fires it will immediately stop in the routine, use the F8 key when it does to step through the code and see how it goes.

ALmost certainly it'll fall over on the line with the server path.  I say this because it works fine with the fixed path as tested.

Chris

Sub Q_26408152()

    stop
    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

Select allOpen in new window

You can also try the mapo with the following edit:

Chris

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 "T:\" & FileNameCharsOnly(.Subject) & ".msg", olMsg
'            .saveas "c:\deleteme\" & FileNameCharsOnly(.Subject) & ".msg", olMsg
        End With
    End If
End 

Select allOpen in new window

I guess there could also be an issue with name length.

Try it with an email with a miniscule subject .. if this is the issue it's easy enough to solve albeit by truncation of the subject.

Chris

Ok Chris. Good news. We are getting close. But I think I may not have been specific on the location where my emails have to go once on Sharepoint.

On the Sharepoint location there are individual folders for each email that I save. If you notice in the email below in the subject and also the actual email file that gets saved there is a project number defined. In this example it's project num: CTH1100051

DSo, ever email I get will have a project number in the Subject line. This is so it becomes very easy to tell where the file should be saved and also what project that email applies to.

So, the good news is that when I clicked on the new code button you just created it prompted me to go through each line of code. and once it finished the last thing I saw it do was copy that email to my Share point location. See below. However, right now it's just sitting on the ROOT of that sharepoint location. As you can see there are individual folders and among those is folder: CTH1100051 - XXXX

D That email file will need to be moved to that folder.

So, this brings me to a couple of questions as to how to get this to work. You tell me.

1.) would it be possible to setup code to tell Outlook to save an email message based on the project number in the subject line (ex. CTH1100051)??

2.) If this is not possible, the second option would be to set the code so that when I click on the button it does a save as with default TYPE .MSG and defaults to that sharepoint root location that we are using currently (\\cth-ws01\corp\IT\Shared Documents\Project Documents)  with the box still up and ALLOWS ME TO TAKE IT FROM THERE AND MANUALLY SAVE IT TO THE APPROPRIATE FOLDER THAT MATCHES THE PROJECT NUMBER.

Does all that make sense?

Let me know what you think?

Thanks dude. We are very close to completing this!

Sharepoint-Location.jpg

Also, I often have to save an email with the same subject line with more than one copy. So in that case it would save the first copy. but if someone sent me another email later in the day with the same subject line and I click on that button it woudl not save it because it would still see the same exact subject.

so I will need the ability to save it manually to give the file a unique file extention. Like just adding a "1", "2", "3", depending on how many different emails I get with the same subject.

Make sense?

Save to specific folder ... ought to viable:

1. Can you supply a range of subjects and their  subjects.
2. Specifically identify the sub folder by name
3. Will the folder definitely pre-exist

Depending on the complexity I may ask for you to raise a new question as a premium member that shouldn't be an issue but if you insist we'll continue in this thread so it's not a threat just a request.

Chris

Adding an affix to the email name can also be automated ... with yet more code and a small change in the design.

Chris

Hey Chris, since option 1 may not be possible becuase the project number folders are not predetermined. I create a new project folder upon request of a new project. so the numbers will change constantly. New ones that is.

so, could we just focus on option 2 below? Sounds like that option might be easier. What do you think? Ilike having some control anyway.

2.) If this is not possible, the second option would be to set the code so that when I click on the button it does a save as with default TYPE .MSG and defaults to that sharepoint root location that we are using currently (\\cth-ws01\corp\IT\Shared Documents\Project Documents)  with the box still up and ALLOWS ME TO TAKE IT FROM THERE AND MANUALLY SAVE IT TO THE APPROPRIATE FOLDER THAT MATCHES THE PROJECT NUMBER.

The ideal code setup would be for the code to do a save based on the project number in the subject to the associated project folder name on Sharepoint. And also be smart enough to recognize if a file already exist with that name and assign another copy with a different file name.

That would be a perfect solution. But that sounds like it would not be possible or just too much trouble.

if so, we can just focus on option two I mentioned above.

And for any emails that didn't have a project number in the subject line (some don't) I could use the code you setup for option 2. so, ideally I would have two buttons. One for subject line project numbers and one for emails without project numbers that would give me the ability to finish saving the email manually once it defaulted to the sharepoint location with type .MSG.

Again, that would be ideal. :-)

I am hoping with a sample of headings that I can realise a filter to extract the project numbers.

Chris

But would you be able to account for new future project number folders? And also the ability to save multiple files with the same subject? That's probably the trickiest part of it.

It all increases the complexity but now that I know of the functional requirement ... yes to all.

I would however propose a sequence of (2?) additional questions adding the extra functionality firstly a related question to save to a sub folder.

Seem fair?

Chris

Whatever works for you to accomplish it.

Just let me know what you need me to do or provide you as we go along. thanks.

Chris, if you are able to get this to work, would there be anyway of including code to verify or let me know somehow that the file (s) got saved to sharepoint folder? I would hate to be moving along with this and assuming it has been saving all these emails for like a week and find out that it has not been.

would be nice if there was some sort of verification or notificaiton of the saved email. Just a thought.

1. If you accept this question on the grounds that it is saving the mail as required then raise a related question to place the copy in a sub folder of the sharepoint folder.

As part of supply a sample of subjects containing projects and specify the actual project folder name for each.

Verification is questionable, but for example perhaps we could count the items before the code runs and the number afterwards and check for an increment.

It will be flawed as for example if the PST closes without a save, (hangs up for example) then the save may not not be retained.

Chris

SOrry overl;apping questions, we're not talking an outlook folder so via a pre and post count it should be fine.

Chris

Chris, I just realized what you are proposing regarding the saving of the emails. Unfortunately my boss wants to have all the emails reside in the root of each associated project folder. See below for example. having subfolders would make it harder to scan for files, having to drill down to see everything. We have auditors that will need to access these folders and need to make it simple as possible for them to scan them.

so, given that fact, if you don't think it will be possible without having to do sub folders we can always look at that option 2 I mentioned.

Just let me know.
Sharepoint-2.jpg

Now you are confusing me, the root folder is:

\\cth-ws01\corp\IT\Shared Documents\Project Documents
your piccy shows CTH10036 etc as a sub folder of the root into which you are posting the documents so in what way do I misunderstand?

Chris

Ok. I see what your saying. If that is what you mean by "sub folder" then that is fine.

But initially when you said sub folders I thought you meant when the code goes to save additional email files of the same subject name you would have it create sub folders within that specific sub folder(cth1100036).

What I'm saying is I do need it to save to sub folders off the root, but once it saves to CTH1100036 for example, all files need to be in the root of that specific sub folder.

Did I  confuse you more?

I'm saying that I agree with you. nothing has changed as far as needing to save to a sub folder off the root of \\cth-ws01\corp\IT\Shared Documents\Project Documents

Just don't have the code create sub folders within sub folders.

so don't have a folder underneath CTH1100036. Just have all files in the root of that CTH1100036 sub folder.

Agreed and therefore as I see all will be well.

Out of interest in my concepual modification the 'new' folders (where it doesn't exist yet will be named as the project number and you will need add the descriptive portion some time later.

Chris

Ok. So are you saying I would need to create that new folder "before" i have the email up to save? I don't think code can be setup to create the folder for me based on project number can it?

>>> Ok. So are you saying I would need to create that new folder "before" i have the email up to save?

NO!

The folders in your snapshot appear as something like:
     CTH1100036 diddly squat and a bit more text besides

As far as I interpret, the project number in the emails will appear as somthing like:
    CTH1100036 something different maybe or maybe a repeat of an earlier one

Therefore whilst I can create a new folder when required, it would in this case be named:

    CTH1100036

At some point later on you might like to add the descriptive text to change the name to:
     CTH1100036 diddly squat and a bit more text besides

Chris

Assuming you have the sense of the direction then you need to accept post http:Q_26408152.html#33464248 at which point you will be able to raise the related question for the sub folder activity.

Chris

Ok. Yes. That is correct. After the folder is created automatically I will go back and manually rename the folder to account for the description.

See below for some sample emails with the project number in the subject line.

So let me know if this question is suffecient below:

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

Oh, and btw, as you can see below alot of these emails will have attachments. I assume this code would include those attachments in the save.

sample-email-1.jpg
sample-email-2.jpg
sample-email-3.jpg

The code will work exactky the same as save as from the menu.

CAn you copy and paste the subjects into a post in the new question?

Chris

As for a draft wording how about ...

I have some VBA to save a mail to a sharepoint folder.  I need the mails saving to a sub folder off the root folder according to a project which is recorded in the mail subject.

Can you modify the code so it checks for an existing sub folder and creates one if missing.  If there is no project then pause the script for user input of a project folder.

Sample subjects and the matching sub folder names are as follows:

a12345 diddy ... folder name starts with a12345
re bbbbb summit else ... folder name starts with bbbbb
etc

Chris

You want me to add those last 3 sample email posts? Is that what your speaking of or did you need something else?

Chris, I'm not exactly sure how you want me to setup this question. So I just ask another question and post it and add the 3 sample email posts?

All it needs is the content of the mail subjects adding ... that's where the code will get the project from after all.

Chris

Ok. Chris, not 100% sure if I created the new question the way you wanted me to or not.

But I just created a new question. The question is:

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

Question ID: 26413323

Let me know if you need me to do anything else.  When do you think you might have the code complete?

Thanks!

I suggested two modifications based off the progression of this question and I see you are starting with the multiple copies of the same subject string.  I will address this capability there now if you will PAQ this one to get it off my open list.

I suggested you asked a related question, this is a button that appears after closing to enable you to ask a ... related question i.e. additional capability.

In this case do not do this now as you already have the new question ... the related question link makes connections between the related questions easier which is why it's a good thing.

Chris

Not sure if I understood all that you said above, got a little confusing trying to follow it. But for now what does PAQ mean?

do you want me to Accept one of your comments as a solution? Is that what your talking about?

The rest of what you said I didn't get, but I guess it's not that important right now.

thanks.

Previously asked Question

Yes i.e. http:Q_26408152.html#33464248

Agreed

Chris