AspenTechnologies
asked on
How to create macro to copy an email to specific public folder
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
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
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
Chris
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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
Chris
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
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
Improved filename char handling:
Chris
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
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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.CurrentIte m
Else
If Not ActiveExplorer.Selection Is Nothing Then
Set olItem = ActiveExplorer.Selection.I tem(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
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.CurrentIte
Else
If Not ActiveExplorer.Selection Is Nothing Then
Set olItem = ActiveExplorer.Selection.I
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
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.