SendMail

SendMail - Sending Images by Email using VBA & Outlook

A White Paper by Mark Kubiszyn
30Jan 2016

SendMail Documentation

By: Mark Kubiszyn
SendMail Excel Screenshot


This White Paper for VBA Developers demonstrates two methods to send Images by Email using VBA and Outlook. SendMail ensures that Images are Sent by Displaying the Message and letting Outlook embed the HTML Markup. SendMail2 uses the Outlook.PropertyAccessor to Attach and hide embedded Images referencing the modified HTML Markup for Images with a 'CID:' Tag. Both SendMail Subroutines use the Outlook Progress Dialog to force a Send & Receive in Outlook. Mail and MAPI Namespace Objects are invoked via Late Binding. The Default Mail Profile for Outlook on the Local Machine is used to send Email. The Examples demonstrate sending Email with a Default Signature, HTM Signatures and HTML Templates. They cover using Mailing Lists, using an Excel Range as a Body, Mailing the Workbook, Mailing individual Worksheets, Mailing the Workbook as a PDF File, changing the Importance or Sensitivity of an Email and changing the Formula in a Worksheet or Workbook to Values. Finally, the White Paper touches upon using Table Cells and Stylised HTML as an alternative to using Images

FEATURES

- Send Email using the Default users Outlook Signature
- Dynamically build a HTML Body with Social Icons using Web Links
- Display an Email by Building a HTML Body using a String with an Excel Icon using Table Cells
- Use a Dialog to Prompt for Multiple Attachments and Email the Attachments using a Default Signature
- Force an Email to be Sent using Send & Receive
- Send an Email with High Importance, Confidential Sensitivity & Request a Read Receipt using a Default Signature
- Mail the Active Workbook using a Default Signature
- Mail the Active Workbook as a PDF File using a Default Signature
- Mail Sheet1 & Sheet2 as single .XLSX Workbook Report Attachments
- Mail Sheet1 & Sheet2 as Separate Workbook Report Attachments (.XLSX & .XLSM)
- Mail Sheet1 & Sheet3 as a Single PDF File using a Default Signature
- Mail Sheet2 & Sheet3 as a Single Workbook as Special Values
- Send an Email with an Excel Range as the Body together with a Default Signature
- Send an Email with a Default Signature using Mailing Lists
- Send an Email with a HTML Template containing Local Images
- Get the Users Default Signature Name
- Get all of the Users Signature Names
- Get the Users Default Reply Message Signature Name from Microsoft Word
- Using a Settings List to Send Mutiple Emails using a Default Signature
- Read in HTML from a Template into a VBA Code Module
- 32 Subroutines accompany this White Paper

The main reason for writing this White Paper was to write Subroutines that I could use to Send Email with Signatures and Images via automation. Due to the way I have written the Code SendMail2 Subroutines make this possible - the Code can silently push out Email without Outlook even being open making it ideal for integration into automated Excel Workbooks whenever there is a requirement to Email out the finished Workbook.

REQUIRED

Microsoft Excel 2010-2013, Microsoft Outlook 2010 client and Microsoft Word must be installed on the Local Machine. Macros and access to the VBA Project Object Model must be allowed in Excel. Microsoft Outlook does not need to be running to use SendMail or SendMail2

BEFORE YOU START

Press ALT+F11 and configure your Email Addresses at the top of the Code Module and for the Mailing Lists in 'Sheet1'
Download the Files to accompany this article on the link below and extract them to a new Folder C:\SendMailFiles
Other parts may need some configuration (they are highlighted Yellow) - you should do this prior to Clicking the Button to run the Example
Download the HTML Template Files to use with the SendMail Examples

HOW TO USE SENDMAIL IN YOUR OWN PROJECTS - 4 STEPS

1. Open an existing Macro-enabled Workbook or create a New one
2. Copy the Code at the top of the "mdlWhitePaper" Code Module marked "## BEGIN ##" to "## END ##". Paste into the top of your Code Module
3. Copy & Paste in the relevant SendMail Subroutine and any Helper Subroutines / Functions required
4. Configure the Call to SendMail or SendMail2 by changing the Parameters and compile the Code


THE FULL LIST OF EXAMPLE SUBROUTINES
- SendMail Examples

1. Display & Send an Email with a Text or HTML Body
2. Display & Send an Email with the Default Signature and a HTML Body + Signature
3. Send an Email with the Default Signature and a HTML Body + Signature (force Send & Receive)
4. Display an Email to the User by Building a HTML Body using a String with Social Icons using Web links
5. Display an Email by Building a HTML Body using a String with an Excel Icon using Table Cells
6. Use a Dialog to Prompt for Multiple Attachments and Email the Attachments using a Default Signature
7. Send an Email with High Importance, Confidential Sensitivity & Request a Read Receipt using a Default Signature
8. Display & Send an Email using an Alternative Method of Passing Parameters
9. Mail the Active Workbook using a Default Signature
10. Mail the Active Workbook as a PDF File using a Default Signature
11. Mail Sheet1 & Sheet2 in this Workbook as a single .XLSX Workbook Report Attachment
12. Mail Sheet1 & Sheet2 in this Workbook as Separate Workbook Report Attachments (.XLSX & .XLSM)
13. Mail Sheet1 & Sheet3 in this Workbook as a Single PDF File using a Default Signature
14. Mail Sheet2 & Sheet3 in this Workbook as a Single Workbook as Special Values
15. Send an Email with an Excel Range as the Body together with a Default Signature
16. Send an Email with a Default Signature using the Mailing Lists in this Workbook

- SendMail2 Examples

17. Send an Email with a HTML Message & Using the Default Signature
18. Send an Email with a HTML Template containing Local Images
19. Send an Email with a HTML Message as the Body & a Default Signature
20. Send an Email with a HTML Message as the Body
21. Send an Email with a HTML Message as the Body using Passive Mode
22. Display an Email with a HTML Message as the Body and Wait for the user to Send
23. Display an Email with a HTML Message & a Default Signature as the Body and Wait for the User to Send
24. Display an Email with a HTML Template containing URL Images and Wait for the User to Send
25. Display an Email with the Active Workbook as the Attachment using a Default Signature
26. Mail the ActiveWorkbook as a PDF File using a Default Signature with Low Importance & Private Sensitivity
27. Get the Users Default Signature Name
28. Get all of the Users Signature Names
29. Get the Users Default Reply Message Signature Name from Microsoft Word
30. Using a Settings List to Send Mutiple Emails using a Default Signature
31. Using a Settings List to Send Mutiple Emails using a HTML Body Signature & Wait For User
32. Read in HTML from a Template into a VBA Code Module


Q&A

Q. I have been using SendMail and all of a sudden I get the Outlook Security Warning Message popup - why is this happening?
A. Sending Email will trigger Outlook to display the Warning Dialog Box asking you to Allow the Email if your Anti-virus Software has not been recently updated. Outlook believes that extra Security is required and displays the prompt regardless of how you write the VBA Code (including my SendMail Code). I know, because this has happened to me at work. All of a sudden I received the Warning below:

Outlook Warning Message
I checked my Anti-virus Software and sure enough it had not updated:

Endpoint Out of Date
I logged a ticket and our IT support sent the Anti-virus Software (System Center Endpoint protection) down again to my PC, refreshed the Group Policy on my machine and I was soon good to go again:

Endpoint Updated
Please Note: the type of Anti-virus Software may be different for your PC or work machine. I also had my work machine upgraded to Office 2013 before they re-installed the Anti-virus Software and Outlook 2013 still displayed the Warning Message so you need to check your Anti-virus is up-to-date and working.

Q. Are there any other methods of disabling the Outlook Security Guard?
A. Of course, there are always other ways. Read the note below and open the link to find out how you can change Outlook's Programmatic Access Options - I have added the Disclaimer below (which is displayed on the Webpage that opens) as I do not condone using these methods and neither do the authors of the article:

"Please note that the advice on this page reduces Outlook's security level and could leave you open to an exploit that takes over Outlook and sends mail on your behalf. We highly recommend you use up-to-date antivirus software that Outlook can detect and leave these settings alone. But, when all else fails, there is a way to disable the programmatic access warning in Outlook 2007 and up"

How to disable the Outlook Security Guard by changing Outlook's Programmatic Access Options

Changelog

07.04.2015 - (Version 1.0.0.1) General release. Fix for the comments in Example 17 & notification to user to configure Signature in Example 3

06.04.2015 - (Version 1.0.0.0) Released for beta testing