DVDAddin

English

Tiếng Việt

English

Tiếng Việt

Appearance

Integration: Outlook ​

DVDAddin has no mail server of its own. Every email operation goes through the Microsoft Outlook that is installed and signed in on that same machine. This page collects the real procedures, the mechanism underneath and how to troubleshoot when you use the add-in's two email commands.

The three real email commands ​

CommandRibbon locationWhat it does
Send EmailDVD Addin › File and PrintComposes a message from sheet data, sends one message or a whole batch through Outlook
Import EmailDVD Addin › File and Print › Email MenuScans an Outlook folder, pours the message content into the Output sheet and downloads the attachments
Email TemplateDVD Addin › File and Print › Email MenuOpens SendEmail.xlsx — the standard column layout for batch sending

Apart from these three commands, the add-in does not touch Outlook's Calendar, Contacts, Categories or Rules.

Use cases ​

1. Send emails in bulk through Outlook ​

→ See Send Email.

The mechanism is not "one row = one email" the way mail merge works; it is a driver cell: you nominate a cell (for example B1) and the add-in writes 1, 2, 3… into it in turn; the sheet recalculates itself with INDEX/VLOOKUP to produce the recipient – subject – body for the current iteration, and the message is sent. Because of that, all of the data-selection logic sits in your own Excel formulas, and it can be as complex as you like.

Each iteration can also export the selected sheets as a single PDF file and attach it automatically, with the file name = base + _ + the iteration number.

2. Pull the mailbox into Excel ​

→ See Import Email.

Choose an Outlook folder + a date range + the file extensions you want to collect. The result is written to a sheet named Output with exactly 7 columns:

No.SubjectBodySender NameReceived timeAttachment countFolder location
1BBNT MC-01 phê duyệt(message body, truncated at 32.000 characters)Nguyễn Văn A2026-05-15 09:122D:\...\Attachments\15052026

Attachments are not placed next to the workbook but into <the folder you chose>\Attachments\<ddMMyyyy>\ — a subfolder named after the day you ran the command, not the day the message was received. A file with a duplicate name gets the suffix _1, _2… instead of being overwritten.

The "Subject" column is the conversation topic, not the literal Subject line

The add-in takes the message's ConversationTopic (falling back to Subject only when that is empty). Outlook has already stripped the RE: and FW: prefixes from this value, so a reply and the original message land under the same subject string. That is exactly what makes the reconciliation step below work.

3. Reconcile the replies from each party ​

Because column B has already had the RE: prefix removed, you look up directly with the original subject of the message you sent — there is no need to concatenate "Re: ":

=dvdXlookup(A2; Output!$B$2:$B$5000; Output!$E$2:$E$5000; "No reply"; "No reply"; 0)

dvdXlookup returns the received time of the first message whose subject matches, or the string "No reply" if it finds nothing — see dvdXlookup.

If you import month by month into several sheets, dvdLookupAllSheets searches every visible sheet in the workbook (skipping hidden sheets and skipping the sheet that holds the formula itself), and you specify the columns by column number rather than by range:

=dvdLookupAllSheets(A2; 2; 5)

(column 2 = Subject, column 5 = Received time on the Output sheet — the same column order as in the table above.) This function matches the whole cell, not part of it, and returns #N/A when it finds nothing — wrap it in IFNA if you want the text "No reply".

The full procedure:

  1. Send Email in bulk → 50 contractors receive the acceptance request form.
  2. The parties reply → the messages arrive in the Inbox.
  3. Import Email from the Inbox folder (or from a subfolder already filtered by an Outlook Rule) → the Output sheet.
  4. The Status column of the original table uses dvdXlookup to look the original subject up against the Received time column.
  5. Every cell that returns "No reply" is on the list of people to chase up.

For step 4 to match, the subject you set when sending must be fixed and unique for each party (for example YCNT-MC-01 — Nha thau A). If the recipient edits the subject when replying, there is no longer any way to look it up.

4. Classify messages with AI ​

No worksheet function calls the AI with a free-form question — none of the add-in's 47 UDFs does that (the only AI function is dvdAIExplain, which only explains Excel formulas). To have the AI read and classify message content, use AI Chat: open the chat window, paste the Subject and Body columns from the Output sheet and ask for them to be grouped, then paste the result back into a helper column.

The mechanism underneath ​

Connecting to Outlook ​

The add-in late-binds through the ProgID instead of holding a hard reference to the Outlook library:

csharp
Type olType = Type.GetTypeFromProgID("Outlook.Application");
if (olType == null) { /* report "Microsoft Outlook is not installed." then stop */ }
dynamic outlook = Activator.CreateInstance(olType);
dynamic ns = outlook.GetNamespace("MAPI");
try { ns.Logon(); } catch { }

If Outlook is already running, that session is reused; if it is not, COM starts Outlook silently in the background. On a machine without Outlook the command stops with a message instead of crashing Excel.

Choosing the folder ​

It does not use the GetDefaultFolder(6) constant that most sample scripts on the internet use. The Import Email command takes the folder path as a string returned by the folder tree in the dialog, for example \\ten@congty.com\Inbox\Du an ABC\TVGS, and then walks it level by level. Leave it empty and Outlook opens its own PickFolder box.

The practical consequence: you can scan any subfolder, including a secondary mailbox added to the profile — you are not limited to the default Inbox.

Iterating over messages ​

csharp
foreach (dynamic item in folder.Items)
{
    if ((int)item.Class != 43) continue;   // 43 = olMailItem
    DateTime received = item.ReceivedTime;
    if (start.HasValue && received.Date < start.Value.Date) continue;
    if (end.HasValue   && received.Date > end.Value.Date)   continue;
    // write to the sheet…
}

Two points worth remembering:

  • Only mail items (Class 43) are collected. Appointments, contacts and tasks in the same folder are all skipped.
  • The date filter runs inside the loop, not through Outlook's Items.Restrict. That means the add-in still has to touch every message in the folder even when you narrow the date range.

Message signatures ​

The add-in does not store a signature of its own — there is no signature option in Preferences. When sending, each message is Display()-ed first so that Outlook inserts that account's own default signature, after which the body built by the add-in is placed above the signature and only then is the message sent.

To change the signature: edit it in Outlook (File → Options → Mail → Signatures). To use a different signature for different groups of recipients: pick a different sending account in the From (Sender) field — the signature follows the account.

Performance with large numbers of messages ​

A large folder is slow — narrow the folder, not just the dates ​

Because the date filter runs on the add-in side (see above), setting "From date / To date" does not make Outlook return fewer messages — it only reduces the number of rows written to the sheet. What genuinely makes it faster:

  1. In Outlook, create a Rule or a Search Folder that pushes the project's messages into a dedicated subfolder.
  2. Run Import Email on exactly that subfolder.

Scanning a root Inbox with tens of thousands of messages can take several minutes and look as if Excel has frozen — it is in fact still running (the add-in turns ScreenUpdating off, so the screen stays still).

Every message sent opens a compose window ​

Because Display() is required to pick up the signature, sending 50 messages means the Outlook compose window flashes 50 times. Do not type or click during that time — the window that has focus can swallow your keystrokes. Run large batches while you are not using the machine.

Troubleshooting ​

#1 — "Microsoft Outlook is not installed." ​

The Outlook.Application ProgID is not registered. Common causes: the machine only has Excel (a standalone Office product), or you are using Outlook on the web / New Outlook — New Outlook (the rewritten version) does not provide the COM Object Model, so you have to switch classic Outlook back on.

#2 — The message was sent but the attachment is missing ​

The add-in adds attachments with if (File.Exists(path)) mail.Attachments.Add(path); wrapped in try/catch — a wrong path or a locked file is skipped silently and the message is still sent. There is no error message.

Prevention: before sending a batch, check with a helper column =IF(ISERROR(...),"","OK"), or run File List on the attachment folder to reconcile the names; and always click Preview for the first iteration.

#3 — Outlook shows a security warning ​

Outlook 2007+ has the Object Model Guard, which asks whenever an external application reads addresses or sends mail on your behalf:

"A program is trying to access email addresses you have stored in Outlook…"

Ways to deal with it, in order of preference:

  1. Make sure Windows Security / your antivirus software is on and up to date — Outlook drops the warning by itself once the Security Center reports that the machine is protected.
  2. If the machine is managed by IT: ask IT to relax Trust Center → Programmatic Access through Group Policy.
  3. Lowering the setting manually in the Trust Center is the last resort, because it lowers the protection for every application, not only DVDAddin.

#4 — Messages land in the recipient's Junk folder ​

  • Check Sent Items first: if the message is there, Outlook accepted and sent it, and the rest is beyond the add-in's reach.
  • A batch of dozens of identical messages is easily caught by a spam filter. Vary the content per recipient (you already have the driver cell anyway) and split the send into smaller batches.
  • Ask the recipients to add your address to their Safe Senders.

#5 — The attached PDF is blank or the last iteration is missing ​

When per-iteration PDF export is switched on, the add-in exports the sheet immediately before sending. If the workbook is in manual calculation mode, or a cell is still half-edited, the PDF can capture the old state. Before running a batch: leave cell edit mode (Esc), press F9 to recalculate, and save the workbook.

#6 — The driver cell is overwritten and its original value is lost ​

The iteration number is really written into the driver cell in the worksheet — it is not a temporary value. Once the run finishes, the old value in that cell is gone. Always save the workbook before you click Send.

Released under DVDAddin License.

Copyright © 2026 Đặng Văn Đằng — dvdpm.net