Cách lấy bảng trong mail Outlook ra Excel bằng VBA

Trong bài viết này, Thanh sẽ chia sẻ tới các bạn cách trích xuất bảng trong thư của Outlook ra Excel sử dụng VBA. Đối với nội dung này, bạn cần kiến thức VBA cơ bản từ hai khóa học VBA của Thanh là VBA101 và VBA201. Chúng ta sẽ đi giải thích từng đoạn code, sau đó cuối bài sẽ có toàn bộ code được sử dụng trong ví dụ và một video cho các bạn xem và theo dõi.

Xem nhanh

Một số thiết lập trước khi bắt đầu

Phân tích vấn đề

Để nắm được vấn đề cần giải quyết, chúng ta hãy tóm tắt lại vấn đề như sau. Outlook là một chương trình đọc Mail trong bộ Office của Microsoft, vậy nên sự tương tác qua lại giữa các phần mềm Microsoft là rất tốt, như mình đã nói trong bài viết mới về cách mình sử dụng VBA. Do đó, muốn trích xuất bảng trong các email của Outlook sau đó ghi dữ liệu ra Excel thì cũng rất khả thi. Giả sử chúng ta có một Email trông như sau:

Trong hình ảnh trên, bạn có thể nhìn thấy Email của chúng ta nằm trong thư mục Inbox, trong Email có 2 bảng với nội dung khác nhau. Ở đây, các bạn có thể nhìn thấy các bảng này có cấu trúc gần giống nhau. Với ví dụ này, các bảng trong Email của bạn không nhất thiết cần có cấu trúc giống nhau.

Thiết lập VBA trong Excel

Chúng ta sẽ sử dụng Outlook Object Libray và HTML Object Library trong bài này, do đó, trong giao diện của Visual Basic for Applications, chúng ta bấm vào Menu Tools, chọn References … sau đó đánh dấu tick chọn vào 2 mục

Microsoft Outlook Object Library
Microsoft HTML Object Library

Sau khi thiết lập xong, chúng ta sẽ cần tạo 1 Module mới để chứa Code VBA. Nếu chưa biết bắt đầu với VBA như thế nào, bạn hãy tham khảo bài viết Bắt đầu với VBA của mình.

Lấy dữ liệu bảng trong thư của Outlook ra Excel

Khai báo email sẽ sử dụng

Đầu tiên, chúng ta sẽ đi khai báo email sẽ sử dụng bằng đoạn code

Const myMail As String = "<thay email của bạn vào đây>"

Khai báo các đối tượng của Outlook

' Early Binding
    Dim oApp As Outlook.Application
    Dim oMapi As Outlook.MAPIFolder
    Dim oMail As Outlook.MailItem

Khởi động Outlook


    On Error Resume Next
    Set oApp = GetObject(, "OUTLOOK.APPLICATION")
        If (oApp Is Nothing) Then Set oApp = CreateObject("OUTLOOK.APPLICATION")
    On Error GoTo 0

Lấy các mailitem trong “inbox”

Nếu bạn sử dụng thư mục khác thì hãy thay thế vào inbox


    Set oMapi = oApp.GetNamespace("MAPI").Folders(myMail).Folders("inbox")
    Set oMail = oMapi.Items(oMapi.Items.Count)

Khai báo các đối tượng HTML


    Dim html As MSHTML.HTMLDocument
    Set html = New MSHTML.HTMLDocument
    Dim htmlNodes As MSHTML.IHTMLElementCollection

Lấy ra bảng trong HTML


    With html
        .Body.innerHTML = oMail.HTMLBody
        Set htmlNodes = .getElementsByTagName("table") ' bang trong mail
    End With

Lặp qua các bảng, các ô trong bảng và lấy dữ liệu về Excel


    Dim x As Long, y As Long, i As Long, tblbStartRow As Long
    
    For i = 0 To htmlNodes.Length - 1
        tblbStartRow = (x + 1)
        Range("A" & tblbStartRow).Value = "Table " & (i + 1)
        
        For x = 0 To htmlNodes(i).Rows.Length - 1
            For y = 0 To htmlNodes(i).Rows(x).Cells.Length - 1
                Range("C1").Offset(x + tblbStartRow - 1, y).Value = htmlNodes(i).Rows(x).Cells(y).innerText
            Next y
        Next x
    Next i

Dọn dẹp các đối tượng sau khi code chạy xong


    Set oApp = Nothing
    Set oMapi = Nothing
    Set oMail = Nothing
    Set html = Nothing
    Set htmlNodes = Nothing

Toàn bộ code cho bài viết này


Option Explicit

Sub importOutlookTableToExcel()

    Const myMail As String = ""
    
    ' Early Binding
    Dim oApp As Outlook.Application
    Dim oMapi As Outlook.MAPIFolder
    Dim oMail As Outlook.MailItem
    
    On Error Resume Next
    Set oApp = GetObject(, "OUTLOOK.APPLICATION")
        If (oApp Is Nothing) Then Set oApp = CreateObject("OUTLOOK.APPLICATION")
    On Error GoTo 0
    
    Set oMapi = oApp.GetNamespace("MAPI").Folders(myMail).Folders("")
    Set oMail = oMapi.Items(oMapi.Items.Count)
    
    Dim html As MSHTML.HTMLDocument
    Set html = New MSHTML.HTMLDocument
    Dim htmlNodes As MSHTML.IHTMLElementCollection
    
    With html
        .Body.innerHTML = oMail.HTMLBody
        Set htmlNodes = .getElementsByTagName("table") ' bang trong mail
    End With
    
    Dim x As Long, y As Long, i As Long, tblbStartRow As Long
    
    For i = 0 To htmlNodes.Length - 1
        tblbStartRow = (x + 1)
        Range("A" & tblbStartRow).Value = "Table " & (i + 1)
        
        For x = 0 To htmlNodes(i).Rows.Length - 1
            For y = 0 To htmlNodes(i).Rows(x).Cells.Length - 1
                Range("C1").Offset(x + tblbStartRow - 1, y).Value = htmlNodes(i).Rows(x).Cells(y).innerText
            Next y
        Next x
    Next i
    
    Set oApp = Nothing
    Set oMapi = Nothing
    Set oMail = Nothing
    Set html = Nothing
    Set htmlNodes = Nothing

End Sub