Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    New Lounger
    Join Date
    Aug 2003
    Location
    Atlanta area, Georgia, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Saving Outlook emails as .msg macro (2000 SR-1)

    I am new to this forum, and have little Outlook experience so please bare with me.
    My organization, in their infinite wisdom, requests the project managers (me) to save away all external and internal email communications as .MSG files to the company server. We have to maintain the subject in the .msg filename we are to save it as and prefix it (the subject filename) with the yymmdd of the email (whether sent or received). This is a lot of email (1 project that lasted 6 months has over 2300 of these).

    Is there a macro, or something that is available (or can be created) that can be executed once an email is selected to select/parse the subject, pre-fix it with the yymmdd, and then create a .msg file with that filename?

    Is this possible? Any help would be appreciated, even if it is to say to forget it..

    Thanks, Bryan

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Saving Outlook emails as .msg macro (2000 SR-1)

    This is harder than it looks. As you know, subject lines commonly contain what Windows considers to be illegal characters, such as : and / and and others. So any comprehensive solution is going to have to do something about replacing those characters with legal ones. Second, two replies sent on the same day will have the identical file name. Avoiding accidentally overwriting the first one with the second one is going to take some checking and a strategy for naming the second one for the day, or a variation on all file names that includes a unique string (such as time saved or time sent, hhmmss) in the file name.

    There is a certain advantage to saving as MSG because it is "complete." On the other hand, it is Outlook-only and could be edited after the fact. Printing to PDF might be a better archive. It all depends on the organization's objectives, I guess...

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Saving Outlook emails as .msg macro (2000 SR-1)

    Here's a code example that works around the issues I raised in my message. Annoying, that security prompt...

    Sub SaveEMailasMsg()
    ' Simplified from JohnBF's code, and then complicated some more
    Dim msg As MailItem
    Const strPath As String = "Cocuments and Settings" ' set as desired
    If Inspectors.Count > 0 Then
    ' Assume the "active" inspector has the message to be saved
    Set msg = ActiveInspector.CurrentItem
    Else
    ' Assume the first selected item is the message to be saved
    Set msg = ActiveExplorer.Selection(1)
    End If
    ' The above will fail if we try to set msg to a non-MailItem item
    ' Cleanse illegal characters from subject... :/|*?<>"
    Dim strFileName As String, intCounter As Integer
    ' Specific substititions
    strFileName = Trim(Replace(msg.Subject, ":", ";"))
    strFileName = Replace(strFileName, "<", "(")
    strFileName = Replace(strFileName, ">", ")")
    strFileName = Replace(strFileName, """", "'")
    ' Catch-all for the rest
    For intCounter = 1 To Len(strFileName)
    If InStr(1, "/|*?", Mid(strFileName, intCounter, 1)) > 0 Then
    Mid(strFileName, intCounter, 1) = "-"
    End If
    Next
    strFileName = Format(msg.SentOn, "yymmdd_") & strFileName & Format(msg.SentOn, "_hhmmss") & ".msg"
    msg.SaveAs Path:=strPath & strFileName, Type:=olMSG
    Set msg = Nothing
    End Sub

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Saving Outlook emails as .msg macro (2000 SR-1)

    <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30> Doh! Why could I not see the .SentOn Property?!
    -John ... I float in liquid gardens
    UTC -7ħDS

  5. #5
    New Lounger
    Join Date
    Aug 2003
    Location
    Atlanta area, Georgia, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving Outlook emails as .msg macro (2000 SR-1)

    <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> Wow Jefferson & JohnBF...! Kudo's to you both as this code worked well, 1st time and all I had to do was change the target directory as you stated... I have tried this with no attachments, Word, Excel and .msg attachments, and all worked fine. I attached a .zip to one and it went bye-bye however. I will avoid those.

    <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> Is there any way to make this code have all selected items go to the same directory vs. just the 1st one? I would like to select a couple hundred emails (some have attachments), and then select the macro, then Run....baddabing-baddaboom, done. Is that possible?

    Bryan

  6. #6
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Saving Outlook emails as .msg macro (2000 SR-1)

    You also can add folder navigation if you're feeling ambitious. Something along the lines of...

    <pre>Option Explicit
    'WinAPI Declarations used to help user browse through the folder tree in oftPopulate
    Private Type BrowseInfo
    hWndOwner As Long
    pIDLRoot As Long
    pszDisplayName As Long
    lpszTitle As Long
    ulFlags As Long
    lpfnCallback As Long
    lParam As Long
    iImage As Long
    End Type
    Const BIF_RETURNONLYFSDIRS = 1
    Const MAX_PATH = 260
    Private Declare Sub CoTaskMemFree Lib "ole32.dll" (ByVal hMem As Long)
    Private Declare Function lstrcat Lib "kernel32" Alias "lstrcatA" _
    (ByVal lpString1 As String, ByVal lpString2 As String) As Long
    Private Declare Function SHBrowseForFolder Lib "shell32" _
    (lpbi As BrowseInfo) As Long
    Private Declare Function SHGetPathFromIDList Lib "shell32" _
    (ByVal pidList As Long, ByVal lpBuffer As String) As Long</pre>

    at the top of the module and something like this
    <pre>Dim strPath As String
    strPath = apiChooseFolder()
    If strPath = vbNullString Then
    Exit Sub
    Else
    strPath = strPath & ""
    End If</pre>

    in your main code and a function like this somewhere else in your module:
    <pre>Private Function apiChooseFolder() As String
    Dim iNull As Integer, lpIDList As Long, lResult As Long
    Dim strPath As String, udtBI As BrowseInfo

    With udtBI
    .hWndOwner = 0&
    .lpszTitle = lstrcat("Choose folder", "")
    'Return only if the user selected a directory
    .ulFlags = BIF_RETURNONLYFSDIRS
    End With

    lpIDList = SHBrowseForFolder(udtBI)
    If lpIDList Then
    strPath = String$(MAX_PATH, 0)
    SHGetPathFromIDList lpIDList, strPath
    'free the block of memory
    CoTaskMemFree lpIDList
    iNull = InStr(strPath, vbNullChar)
    If iNull Then
    strPath = Left$(strPath, iNull - 1)
    End If
    End If

    apiChooseFolder = strPath
    End Function</pre>

    HansV posted another variations on this theme in <post#=274464>post 274464</post#>. I'm not sure where I "adopted" my code from, but it could very well have been another Lounger. Hope this helps.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Saving Outlook emails as .msg macro (2000 SR-1)

    Also see <post#=271140>post 271140</post#> by Don Ceraso for a GetFolder function needing no API declarations at all.

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Saving Outlook emails as .msg macro (2000 SR-1)

    <P ID="edit" class=small>(Edited by JohnBF on 19-Aug-03 17:54. Ensure backslash in path.)</P>Original Poster Bryan, to clarify, Hans' handy code above is to set the target folder for the saving of messages, not to address the Folders within Outlook. So you would change this line:

    Dim strPath As String ' it's no longer declared as a constant

    And add this line immediately after it:

    strPath = GetFolder(, "Cocuments and Settings") & ""

    Bingo! (Hans, wish I knew this one a couple of years back! I better start hanging around the VBA Forum more.)
    -John ... I float in liquid gardens
    UTC -7ħDS

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Related GetFolder question for Save Attachmets

    A while back Jefferson showed me how to pop the Outlook Save Attachments dialog:

    Set cbtNSAF = ActiveInspector.CommandBars.FindControl(, 3167)
    cbtNSAF.Execute

    however, this dialog doesn't return anything. I work with a lot of attachments and would love to know if there is a way to pop a similar dialog and get the path and file names used when each attachment is saved. Note that the OL Save Attachments dialog varies according to if there is only one file or if there are multiple files.

    Any ideas?
    -John ... I float in liquid gardens
    UTC -7ħDS

  10. #10
    New Lounger
    Join Date
    Aug 2003
    Location
    Atlanta area, Georgia, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving Outlook emails as .msg macro (2000 SR-1)

    HansV / All,
    Thanks for the info on the folder selection, but it looks pretty deep to me and I only have about 8 folders I would write these .msg files to, so I have just replicated the vb code to 8 different macro's. Works great, but...

    <img src=/S/confused.gif border=0 alt=confused width=15 height=20> A problem was noticed. I found that if I happen to be in the middle of drafting an email, then minimize what I am drafting to read some mail that came in (or was already there) and use one of my macro's to save away the .msg file, I find out that no matter what I have selected, OUTLOOK SAVES AWAY A .MSG OF THE EMAIL I WAS DRAFTING INSTEAD OF THE ONE I SELECTED. Have you seen that before, and if so, have you another snippet that can avoid this? Now knowing this, I will always save away the email I am writing as a draft, close it out and then do the macro, but doesn't seem like I should have to do this.

    Thanks for any info you may have,
    Bryan

  11. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Saving Outlook emails as .msg macro (2000 SR-1)

    Can you post the code you are using now, or tell us which of the above versions you are using?
    -John ... I float in liquid gardens
    UTC -7ħDS

  12. #12
    New Lounger
    Join Date
    Aug 2003
    Location
    Atlanta area, Georgia, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving Outlook emails as .msg macro (2000 SR-1)

    John,
    Here is the code that I am using...

    ++++++++++++++++++++++++++
    Sub Save_Temp_EMailasMsg()
    ' Simplified from JohnBF's code, and then complicated some more

    Dim msg As MailItem

    Const strPath As String = "C:Workc3ilexTemp Email to be Archived" ' set as desired

    If Inspectors.Count > 0 Then
    ' Assume the "active" inspector has the message to be saved
    Set msg = ActiveInspector.CurrentItem
    Else
    ' Assume the first selected item is the message to be saved
    Set msg = ActiveExplorer.Selection(1)
    End If
    ' The above will fail if we try to set msg to a non-MailItem item

    ' Cleanse illegal characters from subject... :/|*?<>"
    Dim strFileName As String, intCounter As Integer
    ' Specific substititions
    strFileName = Trim(Replace(msg.Subject, ":", ";"))
    strFileName = Replace(strFileName, "<", "(")
    strFileName = Replace(strFileName, ">", ")")
    strFileName = Replace(strFileName, """", "'")

    ' Catch-all for the rest
    For intCounter = 1 To Len(strFileName)
    If InStr(1, "/|*?", Mid(strFileName, intCounter, 1)) > 0 Then
    Mid(strFileName, intCounter, 1) = "-"
    End If
    Next

    strFileName = Format(msg.SentOn, "yymmdd_") & strFileName & Format(msg.SentOn, "_hhmmss") & ".msg"

    msg.SaveAs Path:=strPath & strFileName, Type:=olMSG

    Set msg = Nothing

    End Sub
    +++++++++++++++++++

    Thanks for the look-see,
    Bryan

  13. #13
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Saving Outlook emails as .msg macro (2000 SR-1)

    OK, then the way you are saving the messages, the assumption documented in the code isn't valid. Replace:

    If Inspectors.Count > 0 Then
    ' Assume the "active" inspector has the message to be saved
    Set msg = ActiveInspector.CurrentItem
    Else
    ' Assume the first selected item is the message to be saved
    Set msg = ActiveExplorer.Selection(1)
    End If

    with:

    If TypeName(Application.ActiveWindow) = "Explorer" Then
    ' the focus is on an Folder Item not an Open item, so get the Folder Item
    Set msg = ActiveExplorer.Selection(1)
    Else
    ' get the selected Open Item
    Set msg = ActiveInspector.CurrentItem
    End If

    and test it.
    -John ... I float in liquid gardens
    UTC -7ħDS

  14. #14
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Saving Outlook emails as .msg macro (2000 SR-1)

    You know what happens when you assume. <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30> *Sorry about that.

  15. #15
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Saving Outlook emails as .msg macro (2000 SR-1)

    Some thoughts:

    1. Your company is being shortsighted; it would be much less intrusive and much less time consuming to have all the PST's on the Lan and back them up compressed.

    2. It's easy to write the code, but the VBA code triggers an Outlook Security Guard warning, so you would be best off aquiring and using the Redemption library or maybe ADO, or having the code written in C++, all of which which is beyond my skill.

    3. With the kind of message volume you mentioned it might also be better to have code that loops through an entire folder or PST to cover all the messages. Post back if that idea is of interest.

    All that being said here is some starter code to save an individual message:

    Code redacted, see <post#=287154>post 287154</post#> attachment.
    -John ... I float in liquid gardens
    UTC -7ħDS

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •