Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to create macro to copy an email to specific public folder

Posted on 2007-11-24
12
Medium Priority
?
855 Views
Last Modified: 2012-06-27
Hi,
I need to give users a toolbar button to copy the current email to a specific public folder (outlook & exchange 2003). I see you can't record a macro in outlook, and although I have done a little bit of very basic coding in access, can't see what the best way of acheiving this in outlook. Could probably automate the selection of 'Copy to Folder' from the toolbar, but the user would then still have to manaully select the correct public folder.
As it will be the same destination folder everytime, what's the best way of doing this (and possibly displaying a 'Sucessful' message so they don't click it more than once!
Many thanks,
Andrfew
0
Comment
Question by:AspenTechnologies
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
12 Comments
 
LVL 6

Expert Comment

by:kamleshgwalani
ID: 20342439
They can pull and drop with one click to the PF
you need to create PF and have them author i.e. read, write or perhaps full rights to even create new post directly in the Public Folder.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 20342803
It is possible to copy an email via a macro to be run off your button.  Is your standard destination folder a standard outlook folder or a sub folder of your own making and can you identify the folder and it's path in order to create some code?

Chris
0
 

Author Comment

by:AspenTechnologies
ID: 20343107
Hi, thanks for both of your replies.
Chris, the destination is a first level public folder (called Filing - Derby), and they do have the necessary permissions to write to it.
Kamles, I know they can manually send it, but you'd first have to change the view to 'Folders' and scroll down past the other mailboxes etc, but I want to do this in code from a button, not manually, as they have to do it for 50% of the emails they receive to their personal inbox.
Thanks,
Andrew
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 800 total points
ID: 20343177
Hi ... please see the following which will copy the current item, may need a bit of tweaking depending on where your button is.  The code in mailitem_Copy is what is required and the two functions are called as appropriate.  Could be all encompassed in a single routine but I prefer to separate out functionality for ease of re-use.  Assuming your button is on the email to be copied then this will work as is ... I think ... probably ... maybe .... aHHHH just try it :)

Chris
Sub MailItem_Copy()
Dim olApp As Outlook.Application
Dim olItem As Object
Dim copy_folder As Outlook.MAPIFolder
 
    Set copy_folder = Find_Folder("personal folders/inbox/Filing - Derby")
    Set olItem = ActiveInspector.CurrentItem
    If olItem.Class = olMail Then
        If Not copy_folder Is Nothing Then
            Call Copy2Folder(olItem, copy_folder)
        End If
    End If
 
End Sub
 
Public Function Find_Folder(str_folder As String) As Outlook.MAPIFolder
Dim ol_app As Outlook.Application
Dim OL_namespace As Outlook.NameSpace
Dim OL_Folders As Outlook.Folders
Dim Required_Folder As Outlook.MAPIFolder
Dim arr_folders() As String
Dim nest_count As Integer
 
    On Error Resume Next
    str_folder = Replace(str_folder, "/", "\")
    arr_folders() = Split(str_folder, "\")
    Set ol_app = CreateObject("outlook.application")
    Set OL_namespace = ol_app.GetNamespace("MAPI")
    Set Required_Folder = OL_namespace.Folders.Item(arr_folders(0))
    If Not Required_Folder Is Nothing Then
        For nest_count = 1 To UBound(arr_folders)
            Set OL_Folders = Required_Folder.Folders
            Set Required_Folder = Nothing
            Set Required_Folder = OL_Folders.Item(arr_folders(nest_count))
            If Required_Folder Is Nothing Then
                Set Required_Folder = OL_Folders.Add(arr_folders(nest_count))
                Set Required_Folder = OL_Folders.Item(arr_folders(nest_count))
            End If
        Next
    End If
    Set Find_Folder = Required_Folder
    Set ol_app = Nothing
    Set OL_namespace = Nothing
    Set OL_Folders = Nothing
    Set Required_Folder = Nothing
End Function
 
 
Sub Copy2Folder(objItem As Object, obj_folder As Outlook.MAPIFolder)
Dim objApp As Outlook.Application
Dim objNewItem As Object
 
    Set objApp = CreateObject("outlook.application")
    Set objNewItem = objItem.Copy
    objNewItem.UnRead = False
    objNewItem.Subject = objNewItem.Subject & " - Sideways copy, (" & Date & ")."
    objNewItem.Move obj_folder
    Set objApp = Nothing
    Set objNewItem = Nothing
 
End Sub

Open in new window

0
 

Author Comment

by:AspenTechnologies
ID: 20344112
Hi Chris,
Thanks very much for your coding, I'll try it first thing monday. Just one thing, is the Find_Folder variable the folder we're copying to or from. Only the destination is a public folder called Derby - Filing, not a subfolder of the users mailbox (exchange mailbox, not pst file either)?
Cheers,
Andrew
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 20344967
Ah okay, I didn't realise that.  I'll have to think a bit more but i'm sure its possible just that I have not done it that way yet.

Chris
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 20346113
Okay, greatly simplified, a solution is attached here, hopefully it is clear enough but please ask for any clarification, note I am using message subject for the file title, hence the relace of invalid chars during my test BUT may need extending to include other chars, or could be more maturely done with some more work.

Chris
Sub MailItem_Copy()
Dim olItem As Object
Dim dos_folder As String
Dim mailItemType As String
Dim mailSubject As String
 
    dos_folder = "c:\Derby - Filing"
    dos_folder = "c:\"
    Set olItem = ActiveInspector.CurrentItem
    If TypeName(olItem) = "MailItem" Then
        mailSubject = Replace(olItem.Subject, "?", "")
        mailSubject = Replace(mailSubject, ",", "")
        mailSubject = Replace(mailSubject, "/", "")
        If olItem.BodyFormat = olFormatHTML Then
            olItem.SaveAs dos_folder & mailSubject & ".htm", olHTML
        Else
            olItem.SaveAs dos_folder & mailSubject & ".msg", olMSG
        End If
    End If
 
End Sub

Open in new window

0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 20346328
Improved filename char handling:

Chris
Sub MailItem_Copy()
Dim olItem As Object
Dim dos_folder As String
Dim mailItemType As String
Dim mailSubject As String
Dim lenString As Integer
 
    dos_folder = "c:\Derby - Filing"
    Set olItem = ActiveInspector.CurrentItem
    If TypeName(olItem) = "MailItem" Then
        For lenString = 1 To Len(olItem.Subject)
            If Mid(olItem.Subject, lenString, 1) Like "[!\/:*?<>|]" And Mid(olItem.Subject, lenString, 1) <> Chr(34) Then mailSubject = mailSubject & Mid(olItem.Subject, lenString, 1)
        Next
        Do While Right(mailSubject, 1) = "."
            mailSubject = Left(mailSubject, Len(mailSubject) - 1)
        Loop
        If olItem.BodyFormat = olFormatHTML Then
            olItem.SaveAs dos_folder & mailSubject & ".htm", olHTML
        Else
            olItem.SaveAs dos_folder & mailSubject & ".msg", olMSG
        End If
    End If
 
End Sub

Open in new window

0
 

Author Comment

by:AspenTechnologies
ID: 20348937
Morning Chris,
Thanks for your work so far. Unfortunately, when I said public folder I think you misunderstood me. I mean an Exchange Server Public Folder, similar to an individuals mailbox (ie like a personal folder) but available to all users.
I'll try your earlier example for saving to another foldewr in a personal folder first, but if you could modify it, that would be great.
Thanks,
Andrew
0
 
LVL 59

Assisted Solution

by:Chris Bottomley
Chris Bottomley earned 800 total points
ID: 20359088
I have tried the earlier post substituting the personal folder with "public folders/all public folders/diddly"  It works as expected so that previous works for what I understand you to require

Chris
0
 

Author Comment

by:AspenTechnologies
ID: 20359462
Hi Chris,
Whilst waiting for your deliberations, I tempoarily created a personal folder to test with your initial code. Unfortunately, I got and error in line 83 I think.
However, I've just retried it with the correct public folder in the code, and despite getting a run-time error 91: object variable or with block variable not set, realised that this is because i was clicking a macro buton in the main outlook window (all be it with the email highlighted).
When I open the email and then click the macro within that opened email, it works perfectly!
As such I'm happy to award the points, but it would be great if you could write it in such a  way that it worked from the main window for the item highlighted? Even better if it displayed a successful message box (but let me have a go at that msyself)
Thanks very much,
Andrew
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 20360147
Andrew

You could try the following replacement for the main routine:

Sub MailItem_Copy()
Dim olApp As Outlook.Application
Dim olItem As Object
Dim copy_folder As Outlook.MAPIFolder
 
    Set copy_folder = Find_Folder("personal folders/inbox/Filing - Derby")
    If Not ActiveInspector Is Nothing Then
        Set olItem = ActiveInspector.CurrentItem
    Else
        If Not ActiveExplorer.Selection Is Nothing Then
            Set olItem = ActiveExplorer.Selection.Item(1)
        Else
            Set olItem = Nothing
        End If
    End If
    If Not olItem Is Nothing Then
        If olItem.Class = olMail Then
            If Not copy_folder Is Nothing Then
                Call Copy2Folder(olItem, copy_folder)
            End If
        End If
    End If
End Sub

Chris
0

Featured Post

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article lists the top 5 free OST to PST Converter Tools. These tools save a lot of time for users when they want to convert OST to PST after their exchange server is no longer available or some other critical issue with exchange server or impor…
If you troubleshoot Outlook for clients, you may want to know a bit more about the OST file before doing your next job. IMAP can cause a lot of drama if removed in the accounts without backing up.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …

598 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