Note: The other languages of the website are Google-translated. Back to English
Inloggen  \/ 
x
or
x
Registreer  \/ 
x

or

Hoe exporteer ik e-mails uit meerdere mappen / submappen om uit te blinken in Outlook?

Wanneer u een map exporteert met de wizard Importeren en exporteren in Outlook, ondersteunt deze de Inclusief submappen optie als u de map naar CSV-bestand exporteert. Het zal echter behoorlijk tijdrovend en vervelend zijn om elke map naar een CSV-bestand te exporteren en deze vervolgens handmatig naar een Excel-werkmap te converteren. Hier introduceert dit artikel een VBA om snel meerdere mappen en submappen gemakkelijk naar Excel-werkmappen te exporteren.

Exporteer meerdere e-mails uit meerdere mappen / submappen naar Excel met VBA

Office-tabblad - Schakel bewerken en browsen met tabbladen in Office in en maak het werk veel gemakkelijker ...
Kutools for Outlook - Brengt 100 krachtige geavanceerde functies naar Microsoft Outlook
  • Auto CC / BCC volgens regels bij het verzenden van e-mail; Automatisch doorsturen Meerdere e-mails volgens regels; Auto antwoord zonder uitwisselingsserver, en meer automatische functies ...
  • BCC-waarschuwing - toon bericht wanneer u iedereen probeert te beantwoorden als uw e-mailadres in de BCC-lijst staat; Herinner bij ontbrekende bijlagen, en meer herinneren functies ...
  • Beantwoorden (alle) met alle bijlagen in het mailgesprek; Beantwoord veel e-mails tegelijk; Begroeting automatisch toevoegen wanneer antwoord; Datum en tijd automatisch toevoegen aan onderwerp ...
  • Hulpmiddelen voor bijlagen: Automatisch loskoppelen, alles comprimeren, alles hernoemen, alles automatisch opslaan ... Quick Report, Tel geselecteerde e-mails, Dubbele e-mails en contacten verwijderen ...
  • Meer dan 100 geavanceerde functies zullen los de meeste van uw problemen op in Outlook 2010-2019 en 365. Volledige gratis proefperiode van 60 dagen.

pijl blauw rechts bel Exporteer meerdere e-mails uit meerdere mappen / submappen naar Excel met VBA

Volg onderstaande stappen om e-mails uit meerdere mappen of submappen te exporteren naar Excel-werkmappen met VBA in Outlook.

1. druk op anders + F11 -toetsen om het venster Microsoft Visual Basic for Applications te openen.

2. klikken Invoegen > Moduleen plak vervolgens onder VBA-code in het nieuwe modulevenster.

VBA: exporteer e-mails uit meerdere mappen en submappen naar Excel

Const MACRO_NAME = "Export Outlook Folders to Excel"

Sub ExportMain()
ExportToExcel "destination_folder_path\A.xlsx", "your_email_accouny\folder\subfolder_1"
ExportToExcel "destination_folder_path\B.xlsx", "your_email_accouny\folder\subfolder_2"
MsgBox "Process complete.", vbInformation + vbOKOnly, MACRO_NAME
End Sub
Sub ExportToExcel(strFilename As String, strFolderPath As String)
Dim      olkMsg As Object
Dim olkFld As Object
Dim excApp As Object
Dim excWkb As Object
Dim excWks As Object
Dim intRow As Integer
Dim intVersion As Integer

If strFilename <> "" Then
If strFolderPath <> "" Then
Set olkFld = OpenOutlookFolder(strFolderPath)
If TypeName(olkFld) <> "Nothing" Then
intVersion = GetOutlookVersion()
Set excApp = CreateObject("Excel.Application")
Set excWkb = excApp.Workbooks.Add()
Set excWks = excWkb.ActiveSheet
'Write Excel Column Headers
With excWks
.Cells(1, 1) = "Subject"
.Cells(1, 2) = "Received"
.Cells(1, 3) = "Sender"
End With
intRow = 2
For Each olkMsg In olkFld.Items
'Only export messages, not receipts or appointment requests, etc.
If olkMsg.Class = olMail Then
'Add a row for each field in the message you want to export
excWks.Cells(intRow, 1) = olkMsg.Subject
excWks.Cells(intRow, 2) = olkMsg.ReceivedTime
excWks.Cells(intRow, 3) = GetSMTPAddress(olkMsg, intVersion)
intRow = intRow + 1
End If
Next
Set olkMsg = Nothing
excWkb.SaveAs strFilename
excWkb.Close
Else
MsgBox "The folder '" & strFolderPath & "' does not exist in Outlook.", vbCritical + vbOKOnly, MACRO_NAME
End If
Else
MsgBox "The folder path was empty.", vbCritical + vbOKOnly, MACRO_NAME
End If
Else
MsgBox "The filename was empty.", vbCritical + vbOKOnly, MACRO_NAME
End If

Set olkMsg = Nothing
Set olkFld = Nothing
Set excWks = Nothing
Set excWkb = Nothing
Set excApp = Nothing
End Sub

Public Function OpenOutlookFolder(strFolderPath As String) As Outlook.MAPIFolder
Dim arrFolders As Variant
Dim varFolder As Variant
Dim bolBeyondRoot As Boolean

On Error Resume Next
If strFolderPath = "" Then
Set OpenOutlookFolder = Nothing
Else
Do While Left(strFolderPath, 1) = "\"
strFolderPath = Right(strFolderPath, Len(strFolderPath) - 1)
Loop
arrFolders = Split(strFolderPath, "\")
For Each varFolder In arrFolders
Select Case bolBeyondRoot
Case False
Set OpenOutlookFolder = Outlook.Session.Folders(varFolder)
bolBeyondRoot = True
Case True
Set OpenOutlookFolder = OpenOutlookFolder.Folders(varFolder)
End Select
If Err.Number <> 0 Then
Set OpenOutlookFolder = Nothing
Exit For
End If
Next
End If
On Error GoTo 0
End Function

Function GetSMTPAddress(Item As Outlook.MailItem, intOutlookVersion As Integer) As String
Dim olkSnd As Outlook.AddressEntry
Dim olkEnt As Object

On Error Resume Next
Select Case intOutlookVersion
Case Is < 14
If Item.SenderEmailType = "EX" Then
GetSMTPAddress = SMTPEX(Item)
Else
GetSMTPAddress = Item.SenderEmailAddress
End If
Case Else
Set olkSnd = Item.Sender
If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
Set olkEnt = olkSnd.GetExchangeUser
GetSMTPAddress = olkEnt.PrimarySmtpAddress
Else
GetSMTPAddress = Item.SenderEmailAddress
End If
End Select
On Error GoTo 0
Set olkPrp = Nothing
Set olkSnd = Nothing
Set olkEnt = Nothing
End Function

Function GetOutlookVersion() As Integer
Dim arrVer As Variant
arrVer = Split(Outlook.Version, ".")
GetOutlookVersion = arrVer(0)
End Function

Function SMTPEX(olkMsg As Outlook.MailItem) As String
Dim olkPA As Outlook.propertyAccessor
On Error Resume Next
Set olkPA = olkMsg.propertyAccessor
SMTPEX = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x5D01001E")
On Error GoTo 0
Set olkPA = Nothing
End Function

3. Pas de bovenstaande VBA-code naar behoefte aan.

(1) Vervangen bestemming_map_pad in bovenstaande code met het mappad van de doelmap waarin u de geëxporteerde werkmappen opslaat, zoals C: \ Users \ DT168 \ Documents \ TEST.
(2) Vervang your_email_accouny \ folder \ submap_1 en je_email_accouny \ folder \ submap_2 in bovenstaande code door de mappaden van submappen in Outlook, zoals Kelly @extendoffice.com \ Inbox \ A als Kelly @extendoffice.com \ Inbox \ B

4. druk de F5 toets of klik op de lopen knop om deze VBA uit te voeren. En klik vervolgens op het OK knop in het pop-upvenster Outlook-mappen exporteren naar Excel. Zie screenshot:

En nu worden e-mails van alle opgegeven submappen of mappen in bovenstaande VBA-code geëxporteerd en opgeslagen in Excel-werkmappen.


pijl blauw rechts belGerelateerde artikelen


Kutools voor Outlook - Brengt 100 geavanceerde functies naar Outlook en maakt het werk veel gemakkelijker!

  • Auto CC / BCC volgens regels bij het verzenden van e-mail; Automatisch doorsturen Meerdere e-mails op maat; Auto antwoord zonder uitwisselingsserver, en meer automatische functies ...
  • BCC-waarschuwing - toon bericht wanneer u alle probeert te beantwoorden als uw e-mailadres in de BCC-lijst staat; Herinner bij ontbrekende bijlagen, en meer herinneren functies ...
  • Beantwoorden (alle) met alle bijlagen in het e-mailgesprek; Beantwoord veel e-mails in seconden; Begroeting automatisch toevoegen wanneer antwoord; Datum toevoegen aan onderwerp ...
  • Hulpmiddelen voor bijlagen: beheer alle bijlagen in alle e-mails, Automatisch loskoppelen, Alles comprimeren, Alles hernoemen, Alles opslaan ... Snel rapport, Tel geselecteerde e-mails...
  • Krachtige ongewenste e-mails op maat; Verwijder dubbele e-mails en contacten... Stel u in staat om slimmer, sneller en beter te doen in Outlook.
shot kutools outlook kutools tabblad 1180x121
shot kutools vooruitzichten kutools plus tabblad 1180x121
 
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    saliu2512 · 18 days ago
    I run this macro but keep getting compile error:

    User=defined type not defined

    On line 62 " Public Function OpenOutlookFolder(strFolderPath As String) As Outlook.MAPIFolder "

    I have already specified the path as follows:

    ExportToExcel "C:\Users\kudus\Documents\MailExportTest\f1\A.xlsx", "myname@mydomain.com\Inbox\Black Hat Webcast"
    ExportToExcel "C:\Users\\Documekudus\Documents\MailExportTest\f2\B.xlsx", "myname@mydomain.com\Inbox\CPD\Kaplan Training"

    I'm using Outlook 2016 in case that's needed
    • To post as a guest, your comment is unpublished.
      SALIU MAAMA · 16 days ago
      I fixed it. From the visual basic window, go to Tools Reference - and the box for "Microsoft Outlook 16.0 Object Library"


  • To post as a guest, your comment is unpublished.
    JG Tiger · 11 months ago
    Hi,
    I just ran this Macro which works fine.
    I understand that in the expressions
    excWks.Cells(intRow, 1) = olkMsg.Subject
    excWks.Cells(intRow, 2) = olkMsg.ReceivedTime
    excWks.Cells(intRow, 3) = GetSMTPAddress(olkMsg, intVersion)

    the olkMsg.* and GetSMTPAddress(olkMsg, intVersion) extract stuff from Outlook.

    What is the argument to use to get the Address the mail was sent to?

    When Using the Export Wizard of Outlook, it is possible to export this address, so I assume it would be possible to do it through this Macro (with some modification).
    Can somebody help?

    Regards
  • To post as a guest, your comment is unpublished.
    danesteatite@gmail.com · 1 years ago
    Hi, Hopefully someone can help me out here, I have virtually no knowledge of VB but have managed to get this script working for me so far.

    However I have around 1500 folders and subfolders under my inbox in total and I would really like a simple script to export all of the email address that I have sent to with the subject line and date on separate columns in Excel.

    I have searched for days, and tried many different sites but cannot get any code to work other than this one.


    Is what I am asking for even possible? If so is there anyone out there kind and clever enough to help me out whit the script I need?
    I presume it has something to do with this part:


    Sub ExportMain()
    ExportToExcel "destination_folder_path\A.xlsx", "your_email_accouny\folder\subfolder_1"
    ExportToExcel "destination_folder_path\B.xlsx", "your_email_accouny\folder\subfolder_2"
    MsgBox "Process complete.", vbInformation + vbOKOnly, MACRO_NAME
    End Sub


    Thanks in advanced
  • To post as a guest, your comment is unpublished.
    msroumi@gmail.com · 3 years ago
    hello dear, every thing working well many thanks but the body is not exported, how can i export email body too, the excel file has just (Subject, Received, and Sender), if you can update me with it will solve a huge matter in my business many thanks again
    • To post as a guest, your comment is unpublished.
      John · 2 years ago
      In the ExporttoExcel sub you can add the body

      'Write Excel Column Headers
      With excWks
      .Cells(1, 1) = "Subject"
      .Cells(1, 2) = "Received"
      .Cells(1, 3) = "Sender"
      .Cells(1, 4) = "Body"
      End With
      intRow = 2
      For Each olkMsg In olkFld.Items
      'Only export messages, not receipts or appointment requests, etc.
      If olkMsg.Class = olMail Then
      'Add a row for each field in the message you want to export
      excWks.Cells(intRow, 1) = olkMsg.Subject
      excWks.Cells(intRow, 2) = olkMsg.ReceivedTime
      excWks.Cells(intRow, 3) = GetSMTPAddress(olkMsg, intVersion)
      excWks.Cells(intRow, 4) = olkMsg.Body
      intRow = intRow + 1
    • To post as a guest, your comment is unpublished.
      kellytte · 2 years ago
      Hi Montaser,
      The VBA script runs based on Outlook’s Export feature which doesn’t support exporting message content when bulk exporting emails from a mail folder. Therefore, this VBA script cannot export message content too.
  • To post as a guest, your comment is unpublished.
    ClickMonster · 3 years ago
    How do I get this to automatically recurse into subfolders?