Tự Động Hóa Báo Cáo Excel: VBA + Power Query + Email Tự Gửi Hàng Tuần

// Kết nối folder chứa file dữ liệu hàng tuần
let
    Source = Folder.Files("\\server\shared\DuLieu\"),
    FilterLatest = Table.SelectRows(Source, each 
        [Date modified] > DateTime.From(Date.AddDays(DateTime.LocalNow(), -7))),
    CombineData = Table.Combine(
        Table.AddColumn(FilterLatest, "Data", each 
            Excel.Workbook([Content]){[Item="Data"]}[Data])[Data])
in
    CombineData

let
    Source = Sql.Database("server-name", "database-name"),
    Query = Value.NativeQuery(Source, 
        "SELECT * FROM BaoCaoBanHang WHERE TuanSo = " & Text.From(Number.Round((Date.DayOfYear(DateTime.LocalNow())-1)/7)+1))
in
    Query

Sub RefreshAllData()
    ' Refresh Power Query
    ThisWorkbook.RefreshAll
    
    ' Chờ cho đến khi refresh xong
    Dim conn As WorkbookConnection
    For Each conn In ThisWorkbook.Connections
        Do While conn.OLEDBConnection.Refreshing
            DoEvents
        Loop
    Next conn
    
    ' Refresh Pivot Tables
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        Dim pt As PivotTable
        For Each pt In ws.PivotTables
            pt.RefreshTable
        Next pt
    Next ws
    
    MsgBox "Data refreshed!", vbInformation
End Sub

Sub ExportPDF()
    Dim ws As Worksheet
    Set ws = Sheets("BaoCaoTuan")
    
    ' Cập nhật tiêu đề với tuần hiện tại
    ws.Range("A1").Value = "BÁO CÁO TUẦN " & _
        Format(Date, "DD/MM/YYYY")
    
    ' Tên file PDF
    Dim fileName As String
    fileName = "BaoCao_Tuan_" & Format(Date, "YYYYMMDD") & ".pdf"
    Dim filePath As String
    filePath = ThisWorkbook.Path & "\Output\" & fileName
    
    ' Tạo folder Output nếu chưa có
    If Dir(ThisWorkbook.Path & "\Output", vbDirectory) = "" Then
        MkDir ThisWorkbook.Path & "\Output"
    End If
    
    ' Export PDF
    ws.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=filePath, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        OpenAfterPublish:=False
    
    MsgBox "Exported: " & filePath
End Sub

Sub SendReport()
    Dim olApp As Object
    Dim olMail As Object
    
    Set olApp = CreateObject("Outlook.Application")
    Set olMail = olApp.CreateItem(0) ' 0 = olMailItem
    
    With olMail
        .To = "manager@company.com"
        .CC = "team@company.com"
        .Subject = "[Auto] Báo cáo tuần " & Format(Date, "DD/MM/YYYY")
        
        .HTMLBody = "
Báo cáo tuần " & Format(Date, "DD/MM/YYYY") & " #
" & _
                    "
Kính gửi Anh/Chị,
" & _
                    "
Đính kèm báo cáo tuần. Highlights:
" & _
                    "
" & _
                    "
Doanh thu: " & Format(Sheets("Dashboard").Range("B2").Value, "#,##0") & " VNĐ
" & _
                    "
Tăng trưởng: " & Format(Sheets("Dashboard").Range("B3").Value, "0.0%") & "
" & _
                    "
Số đơn: " & Sheets("Dashboard").Range("B4").Value & "
" & _
                    "
" & _
                    "
Trân trọng,
Hệ thống báo cáo tự động
"
        
        ' Đính kèm PDF
        Dim pdfPath As String
        pdfPath = ThisWorkbook.Path & "\Output\BaoCao_Tuan_" & Format(Date, "YYYYMMDD") & ".pdf"
        If Dir(pdfPath) <> "" Then .Attachments.Add pdfPath
        
        .Send ' hoặc .Display để xem trước
    End With
    
    Set olMail = Nothing
    Set olApp = Nothing
End Sub

Sub RunWeeklyReport()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    ' Step 1: Refresh data
    Call RefreshAllData
    Application.Wait Now + TimeValue("00:00:10") ' Buffer
    
    ' Step 2: Export PDF
    Call ExportPDF
    
    ' Step 3: Send email
    Call SendReport
    
    ' Step 4: Save and close
    ThisWorkbook.Save
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
    ' Nếu chạy từ Task Scheduler, tự đóng
    If Environ("AUTO_RUN") = "1" Then Application.Quit
End Sub

' File: RunReport.vbs
Dim xlApp, xlBook
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False
xlApp.DisplayAlerts = False

' Set environment variable
Dim wshShell
Set wshShell = CreateObject("WScript.Shell")
wshShell.Environment("Process")("AUTO_RUN") = "1"

Set xlBook = xlApp.Workbooks.Open("C:\BaoCao\WeeklyReport.xlsm")
xlApp.Run "RunWeeklyReport"

' Cleanup
xlBook.Close False
xlApp.Quit
Set xlApp = Nothing

Sub RunWeeklyReportSafe()
    On Error GoTo ErrorHandler
    
    ' Log start
    LogToFile "=== Report started: " & Now() & " ==="
    
    Call RefreshAllData
    LogToFile "Data refreshed OK"
    
    Call ExportPDF
    LogToFile "PDF exported OK"
    
    Call SendReport
    LogToFile "Email sent OK"
    
    LogToFile "=== Report completed successfully ==="
    Exit Sub
    
ErrorHandler:
    LogToFile "ERROR: " & Err.Description & " at " & Err.Source
    ' Gửi email cảnh báo lỗi
    SendErrorAlert Err.Description
End Sub

Sub LogToFile(msg As String)
    Dim f As Integer: f = FreeFile
    Open ThisWorkbook.Path & "\report_log.txt" For Append As #f
    Print #f, Format(Now, "yyyy-mm-dd hh:nn:ss") & " | " & msg
    Close #f
End Sub