A visitor to our forums wanted a macro that could read the subject and sender on incoming mail. It's an intriguing idea and reading the subject is a lot better than a generic "You've got mail!".
But what I really need is my appointment reminders read, especially since Outlook's reminder window no longer takes focus. I can't begin to count the number of appointments I missed because I didn't see the reminder!
The .Speak method uses the syntax .Speak(Text, SpeakAsync, SpeakXML, Purge), with SpeakAsync parameter defaulting to False. This controls whether the speech is asynchronous. You'll want this set on true to eliminate any delays or hangs in Outlook as the test is being spoken. So in Outlook, you'll use xlApp.Speech.speak text, True to prevent hangs.
To use these macros, you will need to set a reference to your version of Excel in the VBA Editor's Tools, References dialog.
You'll also need to set Macro security to low in File, Options, Trust Center, Macro Settings.
This macro is triggered when an appointment reminder fires. It reads the subject, tells you how soon the appointment begins and the start time.
This macro is added to ThisOutlookSession.
Private Sub Application_Reminder(ByVal Item As Object) If Item.MessageClass <> "IPM.Appointment" Then Exit Sub End If Dim xlApp As Excel.Application Dim timeOffset As Long Dim strTimeOffset As String Set xlApp = Excel.Application timeOffset = (Item.Start - Now) * 1440 Select Case True Case timeOffset < 60 'starts in under 1 hour strTimeOffset = timeOffset & " minutes, " Case timeOffset <= 1440 'starts in under a day timeOffset = timeOffset / 60 strTimeOffset = timeOffset & " hours, " Case timeOffset > 1440 'starts in more than a day timeOffset = timeOffset / 1440 strTimeOffset = timeOffset & " days, on " & Format(Item.Start, "mmmm d") End Select xlApp.Speech.Speak Item.Subject & "Starts in " & strTimeOffset & " at " & Format(Item.Start, "hh:mm am/pm"), True Set xlApp = Nothing End Sub
Read Subject and Sender
The original question was "how to read the subject and sender on incoming message". This code sample is used in a run a script rule. Paste this macro into a module.
Create a rule and select the script as the only action. In my example, it speaks the sender and subject of all new messages but you can use conditions to control which messages it speaks.
See Run a Script Rules for more information (and other code samples).
Sub ReadSubject(Item As Outlook.MailItem) Dim xlApp As Excel.Application Set xlApp = Excel.Application xlApp.Speech.Speak "From " & Item.SenderName & Item.Subject, True Set xlApp = Nothing End Sub
Talking Reminders Tutorial
Change the Voice
By default, the voice is male. If you want to use the female voice, you'll need to change the Text to Speech settings in Windows.
All Windows versions: Go to Control Panel, find Speech Recognition then select Text to Speech to change the voice. (If you've never opened it before, you may be asked to set it up. Close it and reopen to accept the defaults.)
Note to Windows 10 users: use the Control Panel Text to Speech settings, not the Speech settings in the new Settings area.
How to use the macros
First: You will need macro security set to low during testing.
To check your macro security in Outlook 2010 or 2013, go to File, Options, Trust Center and open Trust Center Settings, and change the Macro Settings. In Outlook 2007 and older, it’s at Tools, Macro Security. If Outlook tells you it needs to be restarted, close and reopen Outlook. Note: after you test the macro and see that it works, you can either leave macro security set to low or sign the macro.
Now open the VBA Editor by pressing Alt+F11 on your keyboard.
To use the macro code in ThisOutlookSession:
- Expand Project1 and double click on ThisOutlookSession.
- Copy then paste the macro into ThisOutlookSession. (Click within the code, Select All using Ctrl+A, Ctrl+C to copy, Ctrl+V to paste.)
To put the code in a module:
- Right click on Project1 and choose Insert > Module
- Copy and paste the macro into the new module.
More information as well as screenshots are at How to use the VBA Editor.
Speech.Speak Method (Excel) (MSDN)