How to Integrate UiPath With Outlook and Excel for Daily Reporting

Summary

This guide shows how to build a daily reporting robot that reads data, aggregates in Excel, and distributes a formatted report via Outlook (Desktop or Microsoft 365). You’ll get an implementation blueprint, project structure, activity setups, and error-handling patterns.

1) Prerequisites

• UiPath Studio 2022.10+ (Windows compatibility).
• Packages: UiPath.Excel.Activities (≥2.20), and either UiPath.Mail.Outlook.Activities (≥1.18) or UiPath.MicrosoftOffice365.Activities (≥2.5).
• Outlook account (profile) or Microsoft 365 tenant permissions; Excel installed (or use Workbook activities).
• (Optional) Orchestrator for scheduling and credential storage (Assets).

2) Target Architecture

Data Sources → UiPath Robot → Excel Report → Email (Outlook Desktop or M365 Graph)

• GetData: Read CSV/DB/API → DataTable.
• Transform: Compute KPIs, filter date window, shape output tables.
• BuildReport: Write to Excel template, refresh pivots/charts, save as dated file.
• SendEmail: Compose HTML body, attach report or share link, send.

3) Recommended Project Structure

DailyReporting/
  ├─ Main.xaml
  ├─ Framework/
  │   ├─ Init.xaml
  │   ├─ GetData.xaml
  │   ├─ Transform.xaml
  │   ├─ BuildReport.xaml
  │   └─ SendEmail.xaml
  ├─ Config/
  │   ├─ Settings.xlsx
  │   └─ Constants.json
  ├─ Templates/
  │   ├─ DailyReport_Template.xlsx
  │   └─ EmailBody_Template.html
  ├─ Output/
  │   └─ DailyReport_yyyyMMdd.xlsx
  └─ Logs/

4) Config-Driven Settings

Settings.xlsx (sheet Settings)

Constants.json (snippet)

{
  "DateFormat": "yyyy-MM-dd",
  "NumberFormat": "#,##0.00",
  "RetryCount": 2,
  "RetryDelaySec": 15
}

5) Option A — Outlook Desktop

1. Use Excel File → write dtData to Data sheet; refresh pivots/charts; save as Output/DailyReport_{Now:yyyyMMdd}.xlsx.
2. Read Text File (HTML body) → replace placeholders (date, totals).
3. Send Outlook Mail Message → To/Cc from config, IsBodyHtml=True, attach the XLSX.

Note: Desktop path requires Outlook profile and is best for attended/VDI robots.

6) Option B — Microsoft 365 (Graph API)

1. Microsoft 365 Scope → configure delegated or application permissions.
2. Use Excel File (Office 365) → write data, save to OneDrive/SharePoint.
3. Send Mail (Microsoft 365) → HTML body, attach file or include a shared link.

Note: Best for unattended robots and cloud-hosted executions.

7) Transform Snippets (VB.NET in Assign/Invoke Code)

' Add computed Revenue
dtRaw.Columns.Add("Revenue", GetType(Double))
For Each r In dtRaw.AsEnumerable
    r("Revenue") = CDbl(r("Qty")) * CDbl(r("UnitPrice"))
Next

' Filter last 24h
dtData = (From r In dtRaw.AsEnumerable()
          Where CDate(r("Date")) >= Now.AddDays(-1).Date
          Select r).CopyToDataTable()

8) Email Body Template

<h2 style="margin:0 0 8px">Daily KPIs — {date:yyyy-MM-dd}</h2>
<p>Total Revenue: <strong>{TotalRevenue}</strong></p>
<p>Top Channel: <strong>{TopChannel}</strong></p>
<p>Attached: DailyReport_{date:yyyyMMdd}.xlsx</p>

Replace placeholders just before sending:

emailHtml = emailHtml.
  Replace("{date:yyyy-MM-dd}", Now.ToString("yyyy-MM-dd")).
  Replace("{date:yyyyMMdd}", Now.ToString("yyyyMMdd")).
  Replace("{TotalRevenue}", totalRev.ToString("#,##0.00")).
  Replace("{TopChannel}", topChannel)

9) Scheduling & Robustness

• Orchestrator trigger at 08:30 (Europe/Istanbul).
• Wrap main blocks in Try/Catch; use Retry Scope for transient mail failures.
• Log success/failure; store credentials in Assets.

10) Validation Checklist

• Data rows > 0; guard clauses pass.
• Excel saved to Output/DailyReport_yyyyMMdd.xlsx.
• HTML body placeholders replaced; attachment present or link accessible.
• Logs capture exceptions with message & stack.

11) Troubleshooting

• Outlook activity hangs: prefer M365 in unattended; ensure profile is configured.
• Attachment locked: close workbook before mail send if using Excel App.
• Locale issues: set number/date formats in Excel template.
• HTML not rendering: set IsBodyHtml=True or BodyContentType=HTML.