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

or

Sut i allforio e-byst o sawl ffolder / is-ffolder i ragori yn Outlook?

Wrth allforio ffolder gyda'r dewin Mewnforio ac Allforio yn Outlook, nid yw'n cefnogi'r Cynhwyswch Is-ffolderi opsiwn os ydych chi'n allforio'r ffolder i ffeil CSV. Fodd bynnag, bydd yn cymryd llawer o amser ac yn ddiflas allforio pob ffolder i ffeil CSV ac yna ei drosi i lyfr gwaith Excel â llaw. Yma, bydd yr erthygl hon yn cyflwyno VBA i allforio ffolderi ac is-ffolderi lluosog yn gyflym i lyfrau gwaith Excel yn gartrefol.

Allforio e-byst lluosog o ffolderau / is-ffolderi lluosog i Excel gyda VBA

Tab Swyddfa - Galluogi Golygu a Phori Tabbed yn y Swyddfa, a Gwneud Gwaith yn Haws Orau ...
Kutools for Outlook - Yn Dod â 100 o Nodweddion Uwch Pwerus i Microsoft Outlook
  • Auto CC / BCC yn ôl rheolau wrth anfon e-bost; Auto Ymlaen E-byst Lluosog yn ôl rheolau; Ymateb Auto heb weinydd cyfnewid, a nodweddion mwy awtomatig ...
  • Rhybudd BCC - dangos neges pan geisiwch ateb popeth os yw'ch cyfeiriad post yn rhestr BCC; Atgoffwch Wrth Ymlyniadau ar Goll, a mwy o nodweddion atgoffa ...
  • Ymateb (Pawb) Gyda'r Holl Atodiadau yn y sgwrs bost; Ateb Llawer o E-byst ar unwaith; Auto Ychwanegu Cyfarchiad wrth ateb; Auto Ychwanegu Dyddiad ac Amser yn destun ...
  • Offer Ymlyniad: Auto Detach, Cywasgu Pawb, Ail-enwi Pawb, Auto Save All ... Adroddiad Cyflym, Cyfrif Postiau Dethol, Tynnwch y Post a Chysylltiadau Dyblyg ...
  • Bydd mwy na 100 o nodweddion datblygedig datrys y rhan fwyaf o'ch problemau yn Outlook 2010-2019 a 365. Nodweddion llawn treial am ddim 60 diwrnod.

swigen dde glas saeth Allforio e-byst lluosog o ffolderau / is-ffolderi lluosog i Excel gyda VBA

Dilynwch y camau isod i allforio e-byst o sawl ffolder neu is-ffolder i lyfrau gwaith Excel gyda VBA yn Outlook.

1. Gwasgwch Alt + F11 allweddi i agor ffenestr Microsoft Visual Basic for Applications.

2. Cliciwch Mewnosod > Modiwlau, ac yna pastiwch islaw cod VBA i mewn i ffenestr y Modiwl newydd.

VBA: Allforio e-byst o sawl ffolder ac is-ffolder i 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. Addaswch y cod VBA uchod yn ôl yr angen.

(1) Amnewid cyrchfan_folder_path yn y cod uchod gyda llwybr ffolder y ffolder cyrchfan byddwch yn arbed y llyfrau gwaith a allforir, fel C: \ Defnyddwyr \ DT168 \ Dogfennau \ PRAWF.
(2) Amnewid eich_email_accouny \ folder \ subfolder_1 a'ch ffolder your_email_accouny \ subfolder_2 yn y cod uchod gyda llwybrau ffolder is-ffolderi yn Outlook, fel Kelly @extendoffice.com \ Mewnflwch \ A. a Kelly @extendoffice.com \ Mewnflwch \ B.

4. Gwasgwch y F5 allwedd neu cliciwch y Run botwm i redeg y VBA hwn. Ac yna cliciwch ar y OK botwm yn y blwch popio allan Allforio Ffolderi Outlook i flwch deialog Excel. Gweler y screenshot:

Ac yn awr mae e-byst o'r holl is-ffolderi neu ffolderau penodol yn y cod VBA uchod yn cael eu hallforio a'u cadw i lyfrau gwaith Excel.


swigen dde glas saethErthyglau Perthnasol


Kutools for Outlook - Yn Dod â 100 o Nodweddion Uwch i'w Rhagweld, a Gwneud Gwaith yn Haws Osgach!

  • Auto CC / BCC yn ôl rheolau wrth anfon e-bost; Auto Ymlaen E-byst Lluosog yn ôl arfer; Ymateb Auto heb weinydd cyfnewid, a nodweddion mwy awtomatig ...
  • Rhybudd BCC - dangoswch neges pan geisiwch ateb popeth os yw'ch cyfeiriad post yn rhestr BCC; Atgoffwch Wrth Ymlyniadau ar Goll, a mwy o nodweddion atgoffa ...
  • Ymateb (Pawb) Gyda'r Holl Atodiadau yn y sgwrs bost; Ateb Llawer o E-byst mewn eiliadau; Auto Ychwanegu Cyfarchiad wrth ateb; Ychwanegu Dyddiad i'r pwnc ...
  • Offer Ymlyniad: Rheoli Pob Atodiad ym mhob Post, Datgysylltiad Auto, Cywasgu Pawb, Ail-enwi Pawb, Arbed Pawb ... Adroddiad Cyflym, Cyfrif Postiau Dethol...
  • E-byst Sothach Pwerus yn ôl arfer; Tynnwch y Post a Chysylltiadau Dyblyg... Yn eich galluogi i wneud yn ddoethach, yn gyflymach ac yn well yn Outlook.
tab kutools rhagweld kutools tab 1180x121
kutools rhagweld rhagolygon kutools ynghyd â thab 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 · 1 months 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 · 1 months 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 · 1 years 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?