S
Simon
Hi, I have a seperate folders in outlook that receives 3 emails a day,
all with the same name but containing different data. I have a macro
that looks through the folder, copies the emails as a .txt file and
then places them in another folder. This all works, the only thing is,
it saves the email 3 times using the subject as a file name and so only
leaves me with one file on my (K) drive.
Is there a simple way to save the emails a different name each, though
they have to be consistent as I use a macro in excel daily to take data
out of them, so don't want them named by date of receipt or anything
like that. Here is the code.....thanks....
Public Sub LoopMailFolder()
On Error GoTo ERR_HANDLER
Dim o2Fld As Outlook.MAPIFolder
Dim O2ArcFld As Outlook.MAPIFolder
Dim Obj As Object
Dim Atmt As Attachment
Dim i As Integer
Dim Filename As String
Dim Item As Object
Set o2Fld = GetFolder("Mailbox - Stewart, Simon\Inbox\Ian")
Set O2ArcFld = GetFolder("Mailbox - Stewart,
Simon\Inbox\Ian\IanArchive")
For Each Obj In o2Fld.Items
For Each Atmt In Obj.Attachments
Filename = "K:\Fiapps\supportteam\Performance\" & Atmt.Filename
Next Atmt
Next Obj
For Each Obj In o2Fld.Items
If ExportMailToTxt(Obj) Then
End If
Obj.Move O2ArcFld
Next
For Each Obj In o2Fld.Items
If ExportMailToTxt(Obj) Then
End If
Obj.Move O2ArcFld
Next
For Each Obj In o2Fld.Items
If ExportMailToTxt(Obj) Then
End If
Obj.Move O2ArcFld
Next
For Each Obj In o2Fld.Items
If ExportMailToTxt(Obj) Then
End If
Obj.Move O2ArcFld
Next
For Each Obj In o2Fld.Items
If ExportMailToTxt(Obj) Then
End If
Obj.Move O2ArcFld
Next
'For Each obj In oFld.Items
'obj.Move OArcFld
'Next
'For Each obj In oFld.Items
'obj.Move OArcFld
'Next
'For Each obj In oFld.Items
'obj.Move OArcFld
'Next
Set o2Fld = Nothing
Set O2ArcFld = Nothing
Set Obj = Nothing
Set Atmt = Nothing
Exit Sub
ERR_HANDLER:
MsgBox Err.Description, vbExclamation
End Sub
Public Function GetFolder(strFolderPath As String) As MAPIFolder
' folder path needs to be something like
' "Public Folders\All Public Folders\Company\Sales"
Dim objApp As Outlook.Application
Dim objNS As Outlook.NameSpace
Dim colFolders As Outlook.Folders
Dim objFolder As Outlook.MAPIFolder
Dim arrFolders() As String
Dim i As Long
On Error Resume Next
strFolderPath = Replace(strFolderPath, "/", "\")
arrFolders() = Split(strFolderPath, "\")
Set objApp = CreateObject("Outlook.Application")
Set objNS = objApp.GetNamespace("MAPI")
Set objFolder = objNS.Folders.Item(arrFolders(0))
If Not objFolder Is Nothing Then
For i = 1 To UBound(arrFolders)
Set colFolders = objFolder.Folders
Set objFolder = Nothing
Set objFolder = colFolders.Item(arrFolders(i))
If objFolder Is Nothing Then
Exit For
End If
Next
End If
Set GetFolder = objFolder
Set colFolders = Nothing
Set objNS = Nothing
Set objApp = Nothing
End Function
Public Function ExportMailToTxt(oMail As Outlook.MailItem) As Boolean
' On Error Resume Next
Dim sPath As String: sPath = "K:\Fiapps\supportteam\Performance\"
Dim sName As String
Dim sFile As String
sName = oMail.Subject
sName = sName & ".txt"
oMail.SaveAs sPath & sName, olTXT
ExportMailToTxt = (Err.Number = 0)
End Function
all with the same name but containing different data. I have a macro
that looks through the folder, copies the emails as a .txt file and
then places them in another folder. This all works, the only thing is,
it saves the email 3 times using the subject as a file name and so only
leaves me with one file on my (K) drive.
Is there a simple way to save the emails a different name each, though
they have to be consistent as I use a macro in excel daily to take data
out of them, so don't want them named by date of receipt or anything
like that. Here is the code.....thanks....
Public Sub LoopMailFolder()
On Error GoTo ERR_HANDLER
Dim o2Fld As Outlook.MAPIFolder
Dim O2ArcFld As Outlook.MAPIFolder
Dim Obj As Object
Dim Atmt As Attachment
Dim i As Integer
Dim Filename As String
Dim Item As Object
Set o2Fld = GetFolder("Mailbox - Stewart, Simon\Inbox\Ian")
Set O2ArcFld = GetFolder("Mailbox - Stewart,
Simon\Inbox\Ian\IanArchive")
For Each Obj In o2Fld.Items
For Each Atmt In Obj.Attachments
Filename = "K:\Fiapps\supportteam\Performance\" & Atmt.Filename
Next Atmt
Next Obj
For Each Obj In o2Fld.Items
If ExportMailToTxt(Obj) Then
End If
Obj.Move O2ArcFld
Next
For Each Obj In o2Fld.Items
If ExportMailToTxt(Obj) Then
End If
Obj.Move O2ArcFld
Next
For Each Obj In o2Fld.Items
If ExportMailToTxt(Obj) Then
End If
Obj.Move O2ArcFld
Next
For Each Obj In o2Fld.Items
If ExportMailToTxt(Obj) Then
End If
Obj.Move O2ArcFld
Next
For Each Obj In o2Fld.Items
If ExportMailToTxt(Obj) Then
End If
Obj.Move O2ArcFld
Next
'For Each obj In oFld.Items
'obj.Move OArcFld
'Next
'For Each obj In oFld.Items
'obj.Move OArcFld
'Next
'For Each obj In oFld.Items
'obj.Move OArcFld
'Next
Set o2Fld = Nothing
Set O2ArcFld = Nothing
Set Obj = Nothing
Set Atmt = Nothing
Exit Sub
ERR_HANDLER:
MsgBox Err.Description, vbExclamation
End Sub
Public Function GetFolder(strFolderPath As String) As MAPIFolder
' folder path needs to be something like
' "Public Folders\All Public Folders\Company\Sales"
Dim objApp As Outlook.Application
Dim objNS As Outlook.NameSpace
Dim colFolders As Outlook.Folders
Dim objFolder As Outlook.MAPIFolder
Dim arrFolders() As String
Dim i As Long
On Error Resume Next
strFolderPath = Replace(strFolderPath, "/", "\")
arrFolders() = Split(strFolderPath, "\")
Set objApp = CreateObject("Outlook.Application")
Set objNS = objApp.GetNamespace("MAPI")
Set objFolder = objNS.Folders.Item(arrFolders(0))
If Not objFolder Is Nothing Then
For i = 1 To UBound(arrFolders)
Set colFolders = objFolder.Folders
Set objFolder = Nothing
Set objFolder = colFolders.Item(arrFolders(i))
If objFolder Is Nothing Then
Exit For
End If
Next
End If
Set GetFolder = objFolder
Set colFolders = Nothing
Set objNS = Nothing
Set objApp = Nothing
End Function
Public Function ExportMailToTxt(oMail As Outlook.MailItem) As Boolean
' On Error Resume Next
Dim sPath As String: sPath = "K:\Fiapps\supportteam\Performance\"
Dim sName As String
Dim sFile As String
sName = oMail.Subject
sName = sName & ".txt"
oMail.SaveAs sPath & sName, olTXT
ExportMailToTxt = (Err.Number = 0)
End Function