Create Outlook email and appointments from Excel with VBA
The power of Microsoft Office is quite undeniable. The premier application of the suite is Excel. The love/hate relationship we have all had with it endures. Often times you may find the need to utilize multiple Office applications from within one another. An extremely common request is to create emails and appointments in Outlook, all from within the confines of Excel. Thanks to VBA this is all possible, and can be done quite easily.
In this code sample I have created an Excel workbook. I created a Table (i.e. Insert tab, clicked Table) which housed three columns. Column A housed names, deftly titled “Name”, column B housed email addresses, again so cryptically titled “Email Addresses”, and column C housed a date column titled “Email Sent”. I prefer the Short Date format, but any will suffice. Here is a screenshot of the created Table.
Example Table used
Basically I wanted to keep this table as a reminder of who I emailed on a certain subject and when that email was dispatched. I also had a need to add this to my Outlook calendar for when I emailed this person, for record keeping purposes. Rather than take the time to open Outlook, create a new email item, input all of my data, go to my calendar, create new appointment item, input all of my data, send and save respectively, I decided to take a somewhat faster approach and speed it up with VBA. I like making my life easier, and if I can take 30-60 minutes to code this solution which will probably save me an accumulative 7-8 hours over the next month or so (10 minutes per day x 5 days a week x 52 weeks = 7.22 hrs per month), and I could do so at the click of a button, I’m all for it.
The below code is commented, so I won’t waste time making double comments here. This will basically loop through the entire Table’s column C, check for any value in the cell (besides a blank null, i.e. =”" ), none being found it will create an email to that person with the address listed as well as put it to my default Outlook calendar. There are some settings you can utilize to customize it in multiple fashions. When it’s done with the current iteration it will put the current system date into column C. I do this for two reasons: 1) so I know the date I created the email, and 2) next time I run this I don’t inundate that person with another email.
There are some other web references in the code to both help explain and show other options. If you want to truly utilize the code to best fit your needs, take the time to read through the comments and check out some of the links provided. They are links to some of the most frequently asked questions regarding the use of Outlook via Excel VBA.
I hope you enjoy the code!
In a module titled “modTest” (can be any name):
Option Explicit
Sub SentEmailToRecipients()
'Table structure:
'Table name: tblEmails
'Column A - Name, expressed as full name
'Column B - Email Address, expressed as a valid email address
'Column C - Email Sent, expressed as a date
'Declare variables
Dim WS As Worksheet
Dim tblEmails As ListObject
Dim c As Range
'Set variables
Set WS = ThisWorkbook.Sheets("Sheet1")
Set tblEmails = WS.ListObjects("tblEmails")
'/// The following loop goes through the table column C
'/// Excellent information on coding to Tables can be found here:
'/// http://www.jkp-ads.com/Articles/Excel2007TablesVBA.asp?AllComments=True
'Turn off some application properties to speed up code
Call TOGGLEEVENTS(False)
'Loop through table column C, if no email sent, send one now, set reminder
For Each c In WS.Range("tblEmails[Email Sent]")
'Check if an email was sent to this person
If Len(c.Value) = 0 Then
'Create email
'/// We minus one from the current loop row in lieu of the Table header row
Call CreateEmail(tblEmails.DataBodyRange(c.Row - 1, 2), "SUBJECT", "BODY")
Call CreateAppointment(VBA.Date(), VBA.Date(), True, "Emailed " & tblEmails.DataBodyRange(c.Row - 1, 1), _
"Created email for this person.", False)
'Add the date to the Table
c.Value = VBA.Date()
End If
Next c
'Turn application properties back to default state
Call TOGGLEEVENTS(True)
End Sub
In a module titled “modMisc”:
Option Explicit
Public Sub TOGGLEEVENTS(blnState As Boolean)
'Originally written by Zack Barresse
'/// Used to easily toggle on/off application properties to speed up coding
With Application
If Not blnState And Not ActiveWorkbook Is Nothing Then .Calculation = xlCalculationManual
.DisplayAlerts = blnState
.EnableEvents = blnState
.ScreenUpdating = blnState
If blnState Then .CutCopyMode = False
If blnState Then .StatusBar = False
If blnState And Not ActiveWorkbook Is Nothing Then .Calculation = xlCalculationAutomatic
End With
End Sub
Public Function GetOL(Optional Plaebo As Variant) As Object
'/// Used to set an Outlook object by late binding
On Error Resume Next
Set GetOL = GetObject(, "Outlook.Application")
If GetOL Is Nothing Then
Set GetOL = CreateObject("Outlook.Application")
End If
On Error GoTo 0
End Function
Public Function GetNS(ByRef olApp As Object) As Object
'/// Used to set an Outlook Namespace by late binding
Set GetNS = olApp.GetNamespace("MAPI")
End Function
In a module titled “modEmail”:
Option Explicit
Public OL As Object
Public NS As Object
Private olMail As Object
Private olFolder As Object
Private olAppt As Object
Private olItem As Object
Sub CreateAppointment(ByVal dtStart As Date, _
ByVal dtEnd As Date, _
ByVal bAllDay As Boolean, _
ByVal sSubject As String, _
ByVal sBody As String, _
Optional ByVal bReminder As Boolean, _
Optional ByVal iReminderInMinutes As Long)
'Set Outlook variables
Set OL = GetOL()
Set NS = GetNS(OL)
'Check if variables were able to be set
If NS Is Nothing Then
'no Outlook found
Exit Sub
End If
'Set folder as default calendar constant
Set olFolder = NS.GetDefaultFolder(9) 'olFolderCalendar - default calendar
'Create a new appointment item
Set olAppt = olFolder.Items.Add("IPM.Appointment")
'Set start date (& time)
olAppt.Start = dtStart
'Set whether all day event or not
olAppt.AllDayEvent = bAllDay
'If start date is same as end date, no time is given, AND an all day event, make the same
If bAllDay = True And Int(dtStart) Int(dtEnd) Then
olAppt.End = dtEnd
End If
'Set subject
olAppt.Subject = sSubject
'Set body of item
olAppt.Body = sBody
'Check if reminder added, if so set it
If bReminder = True Then
'Set minutes of reminder
olAppt.ReminderMinutesBeforeStart = iReminderInMinutes
End If
'Set reminder status
olAppt.ReminderSet = bReminder
'Save the item
olAppt.Save
'Close the item and save (zero constant is for saving)
olAppt.Close 0 'olSave
'/// Please note we are NOT closing Outlook when we're done
End Sub
Sub CreateEmail(ByVal sTo As String, _
ByVal sSubject As String, _
ByVal sBody As String, _
Optional ByVal sCC As String, _
Optional ByVal sBCC As String)
'Set Outlook variables
Set OL = GetOL()
Set NS = GetNS(OL)
'Check if variables were able to be set
If NS Is Nothing Then
'no Outlook found
Exit Sub
End If
'Create email item
Set olMail = OL.CreateItem(0)
'Set email To field
olMail.To = sTo
'If a CC was set, enter it
If Len(sCC) > 0 Then olMail.Cc = sCC
'If a BCC was set, enter it
If Len(sBCC) > 0 Then olMail.Bcc = sBCC
'Set subject
olMail.Subject = sSubject
'Set body
olMail.Body = sBody
'Display email
olMail.Display
'/// NOTE:
'/// It is quite possible to send an email (via command "olMail.Send") although by default this
'/// action is not performed immediately, rather there is a security message one will encounter
'/// due to this being a possible security breach, as VBA code should never be truly be
'/// considered 'secure'. As such, there are multiple workarounds for this.
'///
'/// For more information check out these websites:
'/// http://www.rondebruin.nl/sendmail.htm
'/// http://forums.techguy.org/business-applications/925513-excel-send-emails-automatically-without.html
'/// http://www.excelguru.ca/content.php?174
'/// Please note we are NOT closing Outlook when we're done
End Sub
The only routine which needs to be run is SentEmailToRecipients. In it you will see the two Call lines where the information needs to be set. This was created in Excel 2010, but can be run in 2007 as well. It is set in this example, customize to your hearts content.
Regards,
Zack Barresse
Visit My Website
Zack,
Great post! I covered similar ground earlier, and would love to get your feedback on it:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/A_4316-Automate-Outlook-in-VBA-with-the-OutlookCreateItem-Class.html
Patrick
Visit My Website
Nice write-up Patrick! You took it to the extreme. This is fairly down and dirty with only a few basic (most used) items to set. Very simple, very short. I really like how you made use of the Outlook constants though, and kept the referencing late bound. Extremely agile coding!
Zack
Visit My Website
Thanks Zack! It was…overkill, in many respects, but my goal was to have a “one-stop shopping” Outlook automation class.