Export mail from Outlook to Location on C:

[flicky_long]

Hi Guys,

I'm trying to write a Macro that can export mail with the received date today from a personal folder called WSP JOBLOG

It will then take these message(s) and export them to a folder on my C drive located here in:

C:\Documents and Settings\DentJo\My Documents\WSP_JOBLOG

The need to be saved to this location in .txt format, I have tried a few things but keep getting runtime errors, anyone done something like this?

Thanks in advance

[Hack]

Welcome to DevX

I have tried a few things but keep getting runtime errors

Post what you have tried, and specify what the runtime errors are along with what piece of code is causing them.

[flicky_long]

Heres the code that I have, this should in theory export all the messages in the folder WSP JOBLOG to the specified folder and call them the email message subject + .txt, saving them in .txt format, I was then going to modify this to give me just those for today (when I figure out how!!)

Sub ExportAll()

Dim Message As MailItem

Set myNameSpace = Application.GetNamespace("MAPI")
Set myInbox = myNameSpace.Folders("Personal Folders").Folders("WSP JOBLOG")

For Each Message In myInbox.Items
Message.SaveAs "C:\Documents and Settings\DentJo\My Documents\WSP_JOBLOG\" & Message.Subject & ".txt", olTXT
Next Message

End Sub

Can someone take a look

[flicky_long]

oh and that gives me the following runtime error:

'The Operation Failed'

Very usefuly

[flicky_long]

Are there no VB experts out there to help??!!

[Ron Weller]

The only reason this would fail is if the Path parameter is an invalid windows file name and path
First make sure the destination folder exsists
Second make sure there are no invalid characters in the subject
-you must remove them if there are.

[flicky_long]

Well the folder definitely exists, what do you mean by invalid characters?

The only other thing I can think would be that for some reason outlook doesn't have the rights to write to this destination (it's a work machine), do you think this could be a possibility.

Thanks for your help.

[Ron Weller]

If you can create a text file, manually, in the destination location from your machine then Outlook can also!
Try it, if it works then it's not a rights issue.

Windows does not like these characters in it's file and folder names:
< > " : ? / | \ *

You Can Fix it like this:

  Dim FileName As String
  'create the file name
  FileName = Message.Subject & ".txt"
  'convert invalid characters to an underscore
  FileName = Replace(FileName, "<", "_")
  FileName = Replace(FileName, ">", "_")
  FileName = Replace(FileName, "/", "_")
  FileName = Replace(FileName, "?", "_")
  FileName = Replace(FileName, "\", "_")
  FileName = Replace(FileName, "|", "_")
  FileName = Replace(FileName, ":", "_")
  FileName = Replace(FileName, "*", "_")
  'make the invalid double quote a single quote
  FileName = Replace(FileName, """", "'") 
  'Now Add the Path
  FileName = "C:\Documents and Settings\DentJo\My Documents\WSP_JOBLOG\" & FileName
  'Now save it
  Message.SaveAs FileName , olTXT

[flicky_long]

Thanks so much Ron!

Thats taken so much head banging to crack, I now have:

Dim Message As MailItem

Set myNameSpace = Application.GetNamespace("MAPI")
Set myInbox = myNameSpace.Folders("Personal Folders").Folders("WSP_JOBLOG")

For Each Message In myInbox.Items

Dim FileName As String
'create the file name
FileName = Message.Subject & ".txt"
'convert invalid characters to an underscore
FileName = Replace(FileName, "<", "_")
FileName = Replace(FileName, ">", "_")
FileName = Replace(FileName, "/", "_")
FileName = Replace(FileName, "?", "_")
FileName = Replace(FileName, "\", "_")
FileName = Replace(FileName, "|", "_")
FileName = Replace(FileName, ":", "_")
FileName = Replace(FileName, "*", "_")
'make the invalid double quote a single quote
FileName = Replace(FileName, """", "'")
'Now Add the Path
FileName = "C:\Documents and Settings\DentJo\My Documents\WSP_JOBLOG\" & FileName
'Now save it
Message.SaveAs FileName, olTXT

Next Message

All I want to do now is just tweak this code so that it only exports the job logs that are received 'today' any idea how I might go about doing this?

Thanks!

Jonny

[Ron Weller]

Sure Thing! Here is the whole routine:

Public Sub ExportTodaysLogs()
  Dim myNameSpace As NameSpace
  Dim myInbox As MAPIFolder
  Dim Message As MailItem
  Dim FileName As String
  Dim Received As Date
  
  Set myNameSpace = Application.GetNamespace("MAPI")
  Set myInbox = myNameSpace.Folders("Personal Folders").Folders("WSP_JOBLOG")
  
  For Each Message In myInbox.items
    'Remove Time Part of Date Time Value
    Received = Fix(Message.ReceivedTime)

    'If Item Was Received Today then Export It
    If Received = Date Then
      'create the file name      
      FileName = Message.Subject & ".txt"
      'convert invalid characters to an underscore
      FileName = Replace(FileName, "<", "_")
      FileName = Replace(FileName, ">", "_")
      FileName = Replace(FileName, "/", "_")
      FileName = Replace(FileName, "?", "_")
      FileName = Replace(FileName, "\", "_")
      FileName = Replace(FileName, "|", "_")
      FileName = Replace(FileName, ":", "_")
      FileName = Replace(FileName, "*", "_")
      'make the invalid double quote a single quote
      FileName = Replace(FileName, """", "'")

      'Now Add the Path
      FileName = "C:\Documents and Settings\DentJo\My Documents\WSP_JOBLOG\" & FileName

      'If this routine is run more than once in the same day
      'then some files may already exsist, so don't export those
      If Dir(FileName) = "" Then
        'File was not found so it is ok to Save it
        Message.SaveAs FileName, olTXT
      End If
    End If
  Next Message
  
  'Clean up objects
  Set myNameSpace = Nothing
  Set myInbox = Nothing
  Set Message = Nothing

End Sub

[flicky_long]

Thats a Gem, Thanks so much for your help! Really hard to find somebody who knows about outlook vb!