Save outlook mail into individual .txt files in to system folder

Question

I tried to save Outlook mail to a system folder in .txt format. After running the macro I am not able to see any files in the system folder.

I am not getting any result in the I:\Documents folder.

Sub SaveSelectedMailAsTxtFile()
  Const OLTXT = 0
  Dim currentExplorer As Explorer
  Dim Selection As Selection
  Dim oMail As Outlook.MailItem
  Dim obj As Object
  Dim sPath As String
  Dim dtDate As Date
  Dim sName As String

  Set currentExplorer = Application.ActiveExplorer
  Set Selection = currentExplorer.Selection

  For Each obj In Selection
    Set oMail = obj
    sName = oMail.Subject
    ReplaceCharsForFileName sName, "_"

    dtDate = oMail.ReceivedTime
    sName = Format(dtDate, "yyyymmdd", vbUseSystemDayOfWeek, _
      vbUseSystem) & Format(dtDate, "-hhnnss", _
      vbUseSystemDayOfWeek, vbUseSystem) & "-" & sName & ".txt"

    oMail.SaveAs "I:\Documents" & sName & ".txt", OLTXT
  Next

End Sub

Private Sub ReplaceCharsForFileName(sName As String, sChr As String)
  sName = Replace(sName, "/", sChr)
  sName = Replace(sName, "\", sChr)
  sName = Replace(sName, ":", sChr)
  sName = Replace(sName, "?", sChr)
  sName = Replace(sName, Chr(34), sChr)
  sName = Replace(sName, "<", sChr)
  sName = Replace(sName, ">", sChr)
  sName = Replace(sName, "|", sChr)
End Sub

"I am not getting any results" is not a problem description. Are you getting "results" anywhere else? Are you getting an error message or exception? What happens if you set a breakpoint on the `oMail.SaveAs` line and then inspect the value of `sName` before it runs? What do you see in `sName` at that point? Is it the name and location you expect? — Ken White, Jul 24 '14 at 01:08
Yes i can see the name and location that i expects.But it is not saving any data in my destination. — user3668427, Jul 24 '14 at 04:42

Matt · Accepted Answer · 2015-02-22T01:15:44.847

2

' General Declarations
Option Explicit

' Public declarations
Public Enum olSaveAsTypeEnum
  olSaveAsTxt = 0
  olSaveAsRTF = 1
  olSaveAsMsg = 3
End Enum

Sub Export_MailasMSG()
' Routine will take all selected mails and export them as .MSG files to the
' directory defined by
' Error Handling
On Error Resume Next

' Varaiable Declarations
Dim objItem As Outlook.MailItem
Dim strExportFolder As String: strExportFolder = "I:\Documents\"
Dim strExportFileName As String
Dim strExportPath As String
Dim strReceivedTime As String
Dim strSubject As String
Dim objRegex As Object

' Initiate regex search
Set objRegex = CreateObject("VBScript.RegExp")
With objRegex
.Pattern = "(\s|\\|/|<|>|\|\|\?|:)"
.Global = True
.IgnoreCase = True
End With

' Check if any objects are selected.
If Application.ActiveExplorer.Selection.Count = 0 Then
   MsgBox ("No item has been selected.")
Else
    ' Cycle all selected objects.
    For Each objItem In Application.ActiveExplorer.Selection
        ' If the currently selected item is a mail item we can proceed
        If TypeOf objItem Is Outlook.MailItem Then
            ' Format the file name
            strReceivedTime = objItem.ReceivedTime
            strSubject = objItem.Subject
            strExportFileName = Format(strReceivedTime, "yyyymmdd", vbUseSystemDayOfWeek, _
                    vbUseSystem) & Format(strReceivedTime, "-hhnnss", _
                    vbUseSystemDayOfWeek, vbUseSystem) & "-" & strSubject
            strExportFileName = objRegex.Replace(strExportFileName, "_")
            ' Export to the predefined folder.
            strExportPath = strExportFolder & strExportFileName & ".txt"
            objItem.SaveAs strExportPath, olSaveAsTxt
            MsgBox ("Email saved to: " & strExportPath)
        Else
            ' This is not an email item.
        End If
    Next 'objItem
End If



' Clear routine memory
Set objItem = Nothing
Set objRegex = Nothing

End Sub

Here is code i use for this. It will take all selected emails and export them as txt files into the folder specified by strExportFolder. It also does some validation of how many items are selected and if they are emails. I use the enum olSaveAsTypeEnum to selected between msg and txt. I usually use txt but for your case i was able to change it to txt easily since i had the enum setup. I replace the sub ReplaceCharsForFileName with a regex replace command.

You should be able to insert your date manipulation code to suit your needs.

EDIT: I have updated the code to include your method of creating timestamps. I tried this on a series of emails and i can see all the txt files just fine after selecting about 7. If this still does not work i would need to see the subject and time of some of your emails as well as the file names of the ones you "see". The above code works for me now as i believe you intended it to.

I will not be able to do much testing beyond this as i do not have your source data to play with.

edited Feb 22 '15 at 01:15

answered Jul 22 '14 at 13:46

Matt

45,022
8
78
119

I have tried the edited code also.But still the i am not getting any result in destination folder.Subject name for my mails are PRODrpt and when i select this mail and execute macro i am not seeing any results in destination.Your previous code (one without time stamp )was working for all the mail with subject line as blank.In my case i need to run macro on files with subject lines as blank only.But everytime it is overwriting the previous file.So if possible can you please include a code to rename the files eachtime instead of using timestamp. – user3668427 Jul 23 '14 at 13:55
You could chage the line assigned a value to `strExportFileName` to `strExportFileName=InputBox("Type a file name here")` – Matt Jul 23 '14 at 14:58
THis is starting to go outside the scope of the question. I know that my code is working for multiple files and renames them appropriately. The only way it would overwrite the files is if they came in your inbox at the exact same second with the exact same subject. The file names are based that exactly: the timestamp as defined by you and the subject .txt. This is working for individual exports? – Matt Jul 23 '14 at 16:15
Yes your code is perfect..while i debuging i can also see the newly created file name..example if i select an email with no subject name it will create a file in my destination as .txt.but if i select an email with subject as abc..it is not crating a file in destination..is there anything i need to do with settings – user3668427 Jul 23 '14 at 16:30
Remove the line thats says `on error ...` something is failing for you and you should be able to get more insight. – Matt Jul 23 '14 at 16:40
What us an example Subject line that is not working. I need it to be exact. Also did you try to remove the debugging line to see the point of failure, if their was one? – Matt Jul 24 '14 at 00:01
`strExportFileName` is based on the received time and subject. Even if the subject is blank the file should still have the received time. – Matt Jul 24 '14 at 00:33
Matt..if i remove the line on error...it is not even showing any error.For all the files i am getting the final message as "Email saved to I:\Documents\abc.txt".But in desination i am not able to see the file.It is not even showing any error message while debugging the code – user3668427 Jul 24 '14 at 04:47
that is odd then. If the name of the file is is listed as that it means that it did not get a value for received time from `objItem.ReceivedTime`. How did these email get in your inbox? – Matt Jul 24 '14 at 11:47
that issue is resolved by using the below code(see my answer below).Can you please help me in Replacing the third line in the text file with a string each time after saving it. – user3668427 Jul 24 '14 at 12:45
2

@user3668427 It feels like you're having a lot more than just trouble in one are of programming. I recommend taking some time and trying to do it yourself; and if you encounter a new problem, and can't figure it out on your own or through searching, ask a new question. The comments aren't a place to solve multiple problems just because you have someone's attention. – George Stocker Jul 24 '14 at 15:33

score 0 · Answer 2 · answered Jul 22 '14 at 13:20

0

"I:\Documents" & sName

Will save to

I:\Documents20140722-sName.txt

So add a directory char:

oMail.SaveAs "I:\Documents\" & sName & ".txt", OLTXT

answered Jul 22 '14 at 13:20

Alex K.

171,639
30
264
288

Alex it is not working.I am not able to see any files in destination folder – user3668427 Jul 23 '14 at 04:41

Save outlook mail into individual .txt files in to system folder

2 Answers2