Tip: Iba pang mga wika ay isinaling-Google. Maaari mong bisitahin ang English bersyon ng link na ito.
mag-log in
x
or
x
x
Magrehistro
x

or

Paano i-export ang mga email mula sa maramihang mga folder / subfolder upang magaling sa Outlook?

Kapag nag-export ng isang folder gamit ang wizard ng Import at Export sa Outlook, hindi ito sinusuportahan ang Isama ang Mga Subfolder opsyon kung i-export mo ang folder sa CSV file. Gayunpaman, magkakaroon ito ng matagal na oras at nakakapagod upang i-export ang bawat folder sa CSV file at pagkatapos ay i-convert ito sa manwal na Excel manwal. Dito, ang artikulong ito ay magpapakilala ng isang VBA upang mabilis na i-export ang maramihang mga folder at mga subfolder sa Excel workbook nang madali.

I-export ang maramihang mga email mula sa maramihang mga folder / subfolder sa Excel sa VBA

Kutools para sa Outlook: 100 + Bagong Advanced na Mga Tool para sa Outlook.
Tab ng Tanggapan: Paganahin ang Pag-edit ng Tab at Pag-browse sa Opisina, Tulad ng Chrome, Firefox, IE 8 / 9 / 10.
Classic Menu: Dalhin ang mga Old Menu at Toolbars Bumalik sa Office 2007, 2010, 2013, 2016 at 2019.

arrow blue right bubble I-export ang maramihang mga email mula sa maramihang mga folder / subfolder sa Excel sa VBA

Mangyaring sundin ang mga hakbang sa ibaba upang i-export ang mga email mula sa maramihang mga folder o subfolder sa Excel workbook na may VBA sa Outlook.

1. Pindutin ang Alt + F11 key upang buksan ang window ng Microsoft Visual Basic para sa Mga Application.

2. click Isingit > Module, at pagkatapos ay i-paste sa ibaba ang VBA code sa bagong window ng Module.

VBA: I-export ang mga email mula sa maramihang mga folder at subfolder sa 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. Mangyaring ayusin ang itaas na VBA code ayon sa kailangan mo.

(1) Palitan destination_folder_path sa itaas na code na may folder na landas ng folder ng patutunguhan ay mong i-save ang mga naka-export na workbook sa, tulad ng C: \ Users \ DT168 \ Documents \ TEST.
(2) Palitan ang your_email_accouny \ folder \ subfolder_1 at your_email_accouny \ folder \ subfolder_2 sa itaas na code sa mga path ng folder ng mga subfolder sa Outlook, tulad ng Kelly@extendoffice.com \ Inbox \ A at Kelly@extendoffice.com \ Inbox \ B

4. pindutin ang F5 susi o i-click ang Tumakbo pindutan upang patakbuhin ang VBA na ito. At pagkatapos ay i-click ang OK button sa popping out Export Outlook Folder sa Excel dialog box. Tingnan ang screenshot:

At ngayon ang mga email mula sa lahat ng tinukoy na mga subfolder o folder sa itaas ng VBA code ay nai-export at na-save sa mga workbook sa Excel.


arrow blue right bubbleKaugnay na Artikulo



Kutools para sa Outlook

Higit sa 100 Advanced na Mga Function para sa Outlook 2010, 2013, 2016, 2019 at 365

screen shot kutools para sa pananaw maliit na maliit para sa banner 201812

Higit pang mga Tampok | Libreng pag-download | Tanging $ 39.00 para sa daan-daang mga pag-andar

Say something here...
symbols left.
You are guest ( Sign Up? )
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.
    Montaser Abu Roumi · 9 months 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 · 12 days 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 months 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 · 1 years ago
    How do I get this to automatically recurse into subfolders?