[Integrated Practice]Automated Weekly Report (Excel → Word → Email)

Summary: Generate a Word weekly report from Excel data with one click and automatically email it to the team via Outlook. The whole pipeline takes about 15 minutes to get running—copy, paste, and you're done.

Level note: This is a cross-tool walkthrough that is really L3-level on the B-series scale (VBA + Outlook automation + AI API). If you have no VBA background, read B7 first; if you just want a ready-made solution, you can copy the code in Step 3 as-is and it will run.

1. The Pain Point

It's 4 PM on Friday, and three things are spread across your desk:

An Excel sheet listing 12 tasks completed this week, 4 key metrics, and 3 next-week plans;
A Word template you drafted last week, which you plan to tweak and reuse;
A recipient list in Outlook: 8 teammates plus 1 boss.

The next hour usually plays out like this: copy the 12 rows from Excel into Word (copy a row, click into the body, press Ctrl+V, fix the font, paste the next row) → swap out last week's "data from two weeks ago" → write the email, CC the 8 people, attach the report → hit Send.

That sequence repeats once a week, 4 times a month, 48 times a year. Even worse: while pasting, you accidentally drop row 7's tasks into row 6's slot; the boss reads the report and sends it back asking you to "double-check the data"—and that costs you another half hour.

This article compresses that hour down to 5 minutes: one macro click in Excel, and a Word report plus an email to the team is generated automatically. No more manual copy-paste in the middle, no more mix-ups where the wrong data lands in the wrong slot.

This pipeline threads four tools together: Excel (the data source) + AI (optional—writes the "Weekly Summary" paragraph) + Word (the report template, fields auto-filled) + Outlook (sends the email). Excel is the command center; the VBA macro lives in Excel and triggers the whole chain.

2. What You'll Build

By the end of this tutorial you'll have:

An Excel data sheet (named "WeeklyData"): a header row followed by the "raw material" for this week's report—week label, key metrics, accomplishments, next-week plan, etc.
A Word template (named WeeklyReport_Template.docx): a fixed body skeleton with placeholder fields such as {Week}, {KPIs}, {Accomplishments}, {NextWeek}, {AISummary}, which the macro replaces automatically.
A VBA macro (placed in Excel's ThisWorkbook): every week you just update the data in Excel and press F5. The macro then runs the entire chain—generate the Word document → save to a designated folder → create the email in Outlook → fill in subject, body, and recipients → attach the report → send.
(Optional) An AI call: if you have an OpenAI or DashScope (千问) API Key, AI will automatically generate a ~100-character "Weekly Summary" from your accomplishments and slot it into Word; if you have no API Key, the field stays blank and the macro still runs.

Prerequisites:

A computer with Excel + Word + Outlook installed (Office 2016 or later works; this article is written against Microsoft 365 / Office 2019).
Outlook must have a configured mail account (corporate Exchange or personal IMAP, both work) and be able to send normally.
The Excel file must be saved as .xlsm (macro-enabled workbook), otherwise the macro won't persist.
Before the first run, add the file's folder to File → Options → Trust Center → Trust Center Settings → Trusted Locations (much safer than globally enabling all macros; only on your own PC), and in Outlook's Trust Center set "Programmatic Access" to suppress the warning prompt (details in the troubleshooting section).
(Optional) Any LLM API Key. Without one, the macro still runs—you'll just skip the AI summary.

3. Step-by-Step Walkthrough

The pipeline has four steps. Steps 1 and 2 prepare the materials (Excel data + Word template); step 3 writes the VBA to bridge Excel → Word; step 4 adds Outlook to broadcast the email. Follow each step by clicking and pasting, and you'll have it running by the end.

Step 1: Prepare the Excel Data Source

The "raw material" of any mail-merge-style automation is a table. The first row holds the column headers, and every row beneath it is one report's "data." Let's keep it simple:

Step 1a (new workbook): Open Excel → create a new blank workbook → save as WeeklyReport_Automation.xlsm (note: .xlsm, not .xlsx, or the macro won't persist).

Step 1b (build the table): In the first worksheet, fill in the headers and data below. You can rename the sheet to whatever you like (e.g., "WeeklyData"), but the VBA has a constant DATA_SHEET = "WeeklyData"—if you rename the sheet, update the VBA constant too.

Column A: Week	Column B: KPIs	Column C: Accomplishments	Column D: Next Week	Column E: Recipients
2025-W42	Sales 1.2M / 23 new customers / 78% renewal rate	Finalized Q4 promo plan; launched Product A; visited 5 customers	Follow up on Product A repurchase; prep Double 11 materials	team@company.com; boss@company.com

Key point: when the Accomplishments / Next Week columns have multiple lines, press Alt + Enter inside a cell to break lines (an in-cell newline). Don't press plain Enter—that jumps to the next row and splits one report into two.

Step 1c (let AI tidy the "Accomplishments" and "Next Week" columns): If your week was chaotic, paste your rough notes into an AI first and ask it to compress them into 5–8 lines that "sound like a weekly report." You can copy this prompt directly:

Below is a rough dump of what I did this week. Please condense it into 5 bullet points 
that read like a weekly report, each no more than 30 characters, starting with a verb, 
and with a clear outcome. Preserve any numbers exactly as given—do not invent.

- Had 2 meetings with sales; locked in the Q4 hero product
- Drafted the Product A detail page copy
- Worked with design on detail page images; revised 3 rounds
- Visited 3 existing customers; discussed renewal intent
- Ran 1 internal training; feedback was lukewarm
- Data: this week sales 1.2M, 23 new customers, 78% renewal rate

Paste the AI's output back into Column C. This step is "turning rough notes into report-speak"—it's different from "AI auto-generates the summary," which is done later by VBA calling an API.

Step 2: Build the Word Report Template

The Word template is fixed body + placeholder fields. The macro automatically "drops" the Excel data into the fields, so the template is written once; from then on you only update the Excel data and the Word template stays untouched.

Step 2a (write the body): Open Word → new document → write a template like this (tip: take a screenshot of the final layout so readers can match it):

[{Week} Weekly Report]

Hi all, here is this week's update.

1. Key Metrics
{KPIs}

2. Accomplishments This Week
{Accomplishments}

3. Weekly Summary
{AISummary}

4. Plan for Next Week
{NextWeek}

— {Sender}

Type the {Week}, {KPIs}, {Accomplishments}, {NextWeek}, {AISummary}, {Sender} placeholders using ASCII curly braces manually (the macro will later use Find & Replace to swap them with real data from Excel). The braces must be ASCII { }—not full-width ｛｝.

Step 2b (save the template): File → Save As → set the path to C:\Templates\WeeklyReport_Template.docx (**create the C:\Templates\ folder first**, and keep it in sync with the TEMPLATE_PATH constant in VBA).

Key point: don't keep the template and the Excel file in the same folder, otherwise the weekly-generated Report_20251025.docx will get mixed up with the template. A good habit: templates live in a read-only folder; outputs go in a dedicated folder.

Step 3: Write the VBA Macro (Excel → Word)

VBA (Visual Basic for Applications) is Office's built-in programming language, designed specifically for stitching repetitive operations together.

Step 3a (open the VBA editor): Back in WeeklyReport_Automation.xlsm → press Alt + F11 → in the left "Project" pane find your workbook (it'll be named VBAProject (WeeklyReport_Automation.xlsm)) → double-click ThisWorkbook (don't create a new module—ThisWorkbook is fine; the macro travels with the workbook).

Step 3b (paste the code + edit the config): Paste the complete code below into the right-hand code window. The "Configuration" block at the top has 5 constants you must change to match your paths and recipients:

vba
Sub GenerateWeeklyReportAndEmail()
    '=============================================
    ' Purpose: Read Excel data → Generate Word report → Send via Outlook
    ' Usage:   Update the "WeeklyData" sheet weekly → place cursor inside this Sub → press F5
    ' Prereqs: Word template in place, Outlook signed in, Trust Center configured
    '=============================================
    
    '—————— Configuration (edit to match your setup) ——————
    Const DATA_SHEET     As String = "WeeklyData"               '★ Sheet name holding the data
    Const TEMPLATE_PATH  As String = "C:\Templates\WeeklyReport_Template.docx"  '★ Path to Word template
    Const OUTPUT_FOLDER  As String = "C:\WeeklyReports\"        '★ Output folder for reports
    Const EMAIL_SUBJECT  As String = "Weekly Report"            '★ Email subject prefix
    Const SENDER_NAME    As String = "Zhang San"                '★ Sender name (shown in signature)
    '—————— End of configuration ——————
    
    Dim ws As Worksheet
    Dim fso As Object
    Dim wordApp As Object, doc As Object
    Dim outlookApp As Object, mail As Object
    Dim weekRange As String, kpi As String, completed As String
    Dim nextPlan As String, recipients As String
    Dim outputPath As String
    
    '—— 1. Read Excel data (row 2; extend as needed) ——
    Set ws = ThisWorkbook.Worksheets(DATA_SHEET)
    weekRange  = CStr(ws.Range("A2").Value)  'Week
    kpi        = CStr(ws.Range("B2").Value)  'KPIs
    completed  = CStr(ws.Range("C2").Value)  'Accomplishments
    nextPlan   = CStr(ws.Range("D2").Value)  'Next Week
    recipients = CStr(ws.Range("E2").Value)  'Recipients (multiple separated by ; per MAPI standard)
    
    '—— 2. Auto-create the output folder if missing (fault-tolerant) ——
    Set fso = CreateObject("Scripting.FileSystemObject")
    If Not fso.FolderExists(OUTPUT_FOLDER) Then fso.CreateFolder OUTPUT_FOLDER
    
    '—— 3. Create a Word document from the template + replace fields ——
    Set wordApp = CreateObject("Word.Application")
    wordApp.Visible = False  'suppress the Word window; set True to watch progress
    
    Set doc = wordApp.Documents.Add(Template:=TEMPLATE_PATH, NewTemplate:=False)
    
    'Use Find & Replace to fill fields (placeholders are bracketed strings like {Week})
    With doc.Range.Find
        .ClearFormatting
        .Replacement.ClearFormatting
        .Forward = True
        .Wrap = 1  'wdFindContinue (keep searching from the top after reaching the end; 0 would be wdFindStop)
        
        Dim fieldList As Variant
        fieldList = Array( _
            Array("{Week}", weekRange), _
            Array("{KPIs}", kpi), _
            Array("{Accomplishments}", completed), _
            Array("{NextWeek}", nextPlan), _
            Array("{AISummary}", ""), _           'leave blank for now; AI fills it next, or stays empty
            Array("{Sender}", SENDER_NAME))
        
        Dim i As Long
        For i = LBound(fieldList) To UBound(fieldList)
            .Text = CStr(fieldList(i)(0))
            .Replacement.Text = CStr(fieldList(i)(1))
            .Execute Replace:=2              '2 = wdReplaceAll
        Next i
    End With
    
    '—— 4. Save as a new document (filename includes date/time) ——
    outputPath = OUTPUT_FOLDER & "Report_" & Format(Now, "yyyymmdd_hhnnss") & ".docx"
    doc.SaveAs2 outputPath
    doc.Close SaveChanges:=False
    wordApp.Quit
    
    '—— 5. Outlook: create the email + attach the file + send ——
    Set outlookApp = CreateObject("Outlook.Application")
    Set mail = outlookApp.CreateItem(0)       '0 = olMailItem
    
    mail.To = recipients
    mail.Subject = EMAIL_SUBJECT & " - " & weekRange
    '★ Plain-text body (less likely to be flagged as spam). For HTML formatting, change Body to HTMLBody.
    mail.Body = "Hi all," & vbCrLf & vbCrLf & _
                "Please find this week's report attached." & vbCrLf & _
                "Reply directly to this email if you have any questions." & vbCrLf & vbCrLf & _
                "— " & SENDER_NAME
    
    mail.Attachments.Add outputPath
    
    '★ mail.Send = send immediately. To preview first, use mail.Display and click Send manually.
    mail.Send
    
    MsgBox "Done!" & vbCrLf & _
           "Report saved at: " & outputPath & vbCrLf & _
           "Email sent to: " & recipients, vbInformation, "Weekly Report Automation"
End Sub

Key points (read 3 times before running):

Save after editing the configuration: after changing anything in the configuration block, press Ctrl + S to save, then close the VBA editor and return to Excel.

Outlook will block the macro on first run: a "Allow program to send email?" dialog will pop up—you must click "Yes". You also need Programmatic Access configured in the Trust Center (covered in troubleshooting item #2).

Template fields must use ASCII braces: the VBA above uses {Week}-style ASCII curly braces. The Word template body must use ASCII braces too—don't type ｛Week｝, or the macro won't match them.

Step 3c (do a dry run): In Excel, click anywhere inside the code → press F5 → a dialog pops up saying "Done!" along with the report path and recipients → check C:\WeeklyReports\ for Report_20251025_xxx.docx → open it and verify every field is filled in correctly → check Outlook's **Sent Items** to confirm the email actually went out (if you used mail.Display instead of mail.Send, the email won't be sent—it will sit in an Outlook window for you to review).

Tip (use Display before Send): on your first run, or whenever you're unsure, temporarily change mail.Send to mail.Display. The macro will stop with a ready-to-send email in an Outlook window; open it, verify the body and attachment, then click Send manually. Once you're confident, change it back to mail.Send for true one-click delivery.

Step 4: (Optional Enhancement) Let AI Write the Weekly Summary

If you have an API Key for OpenAI, DashScope (千问), Wenxin (文心一言), or any other LLM, you can have AI automatically generate a ~100-character "Weekly Summary" from the "Accomplishments" column and slot it into Word's {AISummary} placeholder.

Step 4a (obtain an API Key): Sign up at OpenAI (platform.openai.com), DashScope (dashscope.aliyun.com), or Wenxin (cloud.baidu.com) → create an API Key in the console → copy it.

Step 4b (add a new module in Excel VBA): Press Alt + F11 → in the Project pane right-click → Insert → Module → paste the code below (this is a separate function; the main macro from step 3 will call it):

vba
Function CallAIForSummary(ByVal completed As String, ByVal kpi As String) As String
    '—— Uses CreateObject late binding; no manual references needed ——
    '—— If no API Key is set, this returns "" and the main macro keeps running. ——
    
    Const API_KEY As String = "sk-XXXXXX"          '★ Replace with your own API Key
    Const API_URL As String = "https://api.openai.com/v1/chat/completions"
    Const MODEL_NAME As String = "gpt-4o-mini"
    
    Dim http As Object
    Set http = CreateObject("MSXML2.XMLHTTP")
    
    'Build prompts: explicitly cap at ~100 characters and forbid inventing numbers
    Dim sysPrompt As String
    sysPrompt = "You are a concise weekly-report assistant. Based on the user's facts, write a weekly summary of no more than 100 characters. Do not fabricate any numbers, do not use exclamation marks, and avoid filler phrases."
    
    Dim userPrompt As String
    userPrompt = "Accomplishments this week:" & vbLf & completed & vbLf & vbLf & "Key metrics:" & vbLf & kpi
    
    'Hand-assemble the JSON body (avoids referencing a JSON library)
    Dim reqBody As String
    reqBody = "{""model"":""" & MODEL_NAME & _
              """,""messages":[{" & _
                  """role"":""system"",""content"":""" & EscapeJson(sysPrompt) & _
                  """},{" & _
                  """role"":""user"",""content"":""" & EscapeJson(userPrompt) & _
                  """}],""temperature"":0.3}"
    
    On Error GoTo AI_Fail
    
    http.Open "POST", API_URL, False
    http.setRequestHeader "Content-Type", "application/json"
    http.setRequestHeader "Authorization", "Bearer " & API_KEY
    http.send reqBody
    
    Dim response As String
    response = http.responseText
    
    '—— Status-code fallback: anything other than 200 → AI_Fail ——
    If http.status <> 200 Then
        Debug.Print http.status & ": " & Left(response, 200)
        GoTo AI_Fail
    End If
    
    'Minimal JSON parsing: extract the content field from responseText
    Dim startPos As Long, endPos As Long
    startPos = InStr(response, """content"":""")
    If startPos > 0 Then
        startPos = startPos + Len("""content"":""")
        endPos = InStr(startPos, response, """")
        If endPos > startPos Then
            'Lightweight extraction for now; production code should use ScriptControl for JSON.parse
            CallAIForSummary = UnescapeJson(Mid(response, startPos, endPos - startPos))
            Exit Function
        End If
    End If
    
AI_Fail:
    'Any error returns "" so the main macro keeps running (don't break the email flow on AI failure)
    CallAIForSummary = ""
End Function

'—— Two JSON-escaping helpers ——
'(If inputs may contain backslashes or Unicode, consider ScriptControl or the VBA-JSON library)

Private Function EscapeJson(ByVal s As String) As String
    s = Replace(s, "\", "\\")
    s = Replace(s, """", "\""")
    s = Replace(s, vbLf, "\n")
    s = Replace(s, vbCr, "\r")
    EscapeJson = s
End Function

Private Function UnescapeJson(ByVal s As String) As String
    '—— Order matters: undo \\ first, then \n \r \" — otherwise \\n would be misread as newline ——
    s = Replace(s, "\\", Chr(92))   'Use Chr(92) to turn the JSON-escaped backslash \\ back into a single \
    s = Replace(s, "\n", vbLf)
    s = Replace(s, "\r", vbCr)
    s = Replace(s, "\""", """")
    UnescapeJson = s
End Function

Step 4c (have the main macro call it): Back in ThisWorkbook, in the main macro, find the field-replacement block (the line with "{AISummary}") and change its value from "" to CallAIForSummary(completed, kpi):

vba
'Original:
Array("{AISummary}", ""),
'Change to:
Array("{AISummary}", CallAIForSummary(completed, kpi)),

Step 4d (run it): Back in Excel, place the cursor in the main macro → F5. The {AISummary} field will now be filled with AI-generated text. If the API errors out, the main macro will not crash—CallAIForSummary returns an empty string and the report still gets generated.

Key points (API choice):

OpenAI: the example uses gpt-4o-mini, which is cheap and capable. For DashScope (千问), change API_URL to https://dashscope.aliyun.com/compatible-mode/v1/chat/completions and the model name to qwen-turbo (the endpoint is OpenAI-compatible—two line changes is enough). DashScope API Keys are issued by the Alibaba Cloud Bailian console (start with sk-); the Bearer header is unchanged; the recommended environment-variable name is DASHSCOPE_API_KEY.

Never hard-code API Keys in shared files: if the weekly-report automation file will be shared with teammates, store the API_KEY line separately, or read it from an environment variable (Environ("OPENAI_API_KEY")).

Don't panic if you don't have an API Key: just skip Step 4 and run the pure VBA pipeline from steps 1–3. You've already captured 80% of the value—the core win is VBA auto-generation + auto-email; the AI summary is just the cherry on top.

(Alternative) No VBA? Power Automate Works Too

If your company blocks macros, or you'd rather not touch VBA at all, Power Automate (Microsoft's low-code automation platform) can build a nearly identical pipeline. The idea in three steps:

Trigger: use a "Schedule" trigger, set to run every Friday at 16:00.
Read Excel: use an Excel file in OneDrive for Business / SharePoint + a "Get a row" action to pull this week's data.
Create Word + Send email: use "Populate a Word template" to generate the report from a SharePoint-hosted template, then "Send an email (V2)" to deliver it with the report attached to the recipient list.

The upside is zero code. The cost is dependence on the Microsoft cloud (OneDrive / SharePoint / Exchange Online), which you can't use without an M365 subscription. The VBA approach is fully local—any PC with the Office suite will run it. Pick whichever suits you; this article focuses on VBA and treats Power Automate as a backup.

4. How It Works (Under the Hood)

In one sentence: VBA is Office's built-in "code-writing glue", and it strings Excel, Word, and Outlook—three apps that normally live in their own worlds—together through COM (Component Object Model—a protocol that lets Office apps talk to each other; think of it as a shared language). Excel uses CreateObject("Word.Application") to summon Word, Documents.Add to ask Word to create a new doc from the template, Range.Find to ask Word to replace fields, and SaveAs2 to ask Word to save; then it uses CreateObject("Outlook.Application") to summon Outlook, CreateItem(0) to ask Outlook to compose an email, and Send to ask Outlook to send it. The whole chain has zero mouse clicks—VBA does all the clicking for you. AI plays exactly one role in this chain: in the "Excel data → Word report body" translation, it polishes the "Weekly Summary" paragraph—essentially turning the raw "facts" from Excel into "natural language," saving you the 10 minutes of figuring out the right wording.

5. Troubleshooting

Macro won't open / Excel disables macros on open? Office disables macros by default. Save the file as .xlsm (macro-enabled workbook), then add the file's folder to File → Options → Trust Center → Trust Center Settings → Trusted Locations (far smaller blast radius than "Enable all macros"; on a corporate PC, check with IT).
Outlook pops up "Allow program to send email?" It will pop up on the first macro run; you must click "Yes" manually. To stop the prompt for good: File → Options → Trust Center → Trust Center Settings → Programmatic Access, and choose "Never warn me about suspicious activity" (again, only on your own PC; Outlook 2016+ with antivirus installed usually stops prompting). On corporate PCs the setting is often locked by Group Policy—in that case you can fall back to mail.Display and click Send manually.
mail.Send errors with "cannot send" or the email lands in "Drafts"? 99% of the time Outlook isn't signed in or isn't online. Try sending an email manually from Outlook first; if that works, run the macro. On corporate Exchange with a "recall / moderation" policy, the macro-sent email may land in "Outbox" rather than Sent Items—that's expected.
Template fields won't replace / Word still shows {Week}? Three possibilities: ① the template uses full-width braces ｛｝ (switch back to ASCII {}); ② the field names in the fieldList array don't exactly match the template (watch capitalization and spaces—{W eek} with an extra space won't match); ③ the fields have been touched by Word's spell-checker in a way that confuses the macro (rare, but as a precaution, set Application.ScreenUpdating = True before running).
AI call returns 401 / 429? 401 means the API Key is wrong or expired—regenerate it in the platform console; 429 means you're being rate-limited—add a line at the top of CallAIForSummary: Application.Wait Now + TimeValue("00:00:02") to slow things down. Prefer another provider? Claude, Gemini, and Grok all offer OpenAI-style chat endpoints — just swap API_URL, the API key, and the model name. If the network is unreachable, comment out the CallAIForSummary call line and the pure VBA pipeline still works end-to-end.
What separator should multiple email addresses use in Outlook's "To" field? mail.To requires the ASCII semicolon ; between addresses—consistent across all Outlook versions. If the Excel cell uses a Chinese ; or a comma ,, normalize with Replace first.

6. Going Further

Want the Excel data source to "grow on its own"? In real work, weekly-report data usually comes from CRM / project-management systems; this article assumes manual entry. For automatic extraction, see B2 (Clean Dirty Data with One Click Using AI, L2 Intro): use Power Query to wash the raw table into the "WeeklyData" sheet, refresh it monthly or weekly, and the whole pipeline becomes fully automatic; for a deeper dive into the M language, see B26 (Write Power Query M Queries with AI, L2 Advanced).
Want the report plus a PPT in the same email? Excel → Word → PPT is the classic reporting combo—see B11 (Auto-Generate Presentation PPT from Excel Data Using AI, L3 Practice): the Excel data structure from this article can be reused as-is; just add a small chunk of AI code that generates a PPT outline, and the chain is complete.
Want to send different versions of the report to different recipients? Say the boss gets the "full" version and teammates get the "summary" version. That's conditional bulk-sending—level up to B7 (Batch-Generate Notifications with AI + Mail Merge): in VBA, pick the Word template based on the recipient. The "Excel data source + VBA orchestration" idea in this article is exactly the prerequisite for B7.
Want zero manual intervention? Run it automatically at 16:00 every Friday without anyone pressing F5—pair the macro with Windows Task Scheduler (Win+R → taskschd.msc) and trigger it on a schedule; at the appointed time Excel opens, runs the macro, closes, and the email goes out. This is the final step from "point automation" to "unattended automation."
No VBA needed either: this article also gives a Power Automate alternative, which depends on an M365 subscription (OneDrive + SharePoint + Exchange). For a deeper look at Power Automate vs. VBA trade-offs, see D4 (Local vs. Cloud AI Sizing) and B17–B20 (PowerShell / Automation Script Series).

📦 Automated Weekly Report (Excel → Word → Email)