Send mails using Excel VBA and Outlook

Share

Facebook
Twitter
LinkedIn

This is a guest post by Vijay, our in-house VBA Expert.

Send mails using Excel VBA and Outlook - how to

In this article we well learn how to use VBA and Microsoft Outlook to send emails with your reports as attachment.

Scenario

We have an excel based reporting template for the Customer Service Dashboard. We want to update this template using VBA code to create a static version and email it to a list of people. We will define the recipient list in a separate sheet.

Features

1. Code will automatically create necessary folders to save the output file.
2. Email sheet to contain the list of people who are going to receive the report.
3. Sending mail using Microsoft Outlook, primary target is corporate people who are using Outlook as their mail program.
 

 
On our VBA project we would need to add references to the below
1. Microsoft Outlook Object Library
2. Microsoft Scripting Runtime Library
Please note the Outlook library will be available depending on the version of Microsoft Outlook installed on your system, in the example workbook the reference is towards version 14 as available with Outlook 2010. If you have a different version of Outlook installed on your system, you need to point to the correct library installed.
 

 
We have assumed the data used to create the report is already available in the sheet called “rawData”.
We have then updated the “rawData” sheet with 2 new columns having the Date and Time.
Date has been calculated in the rawData sheets using the Date Function.
=DATE(YEAR(B2),MONTH(B2),DAY(B2))
The time has been calculated by converting the actual time of the call into the relevant 30 minute interval.
=INT((TIME(HOUR(B2),MINUTE(B2),SECOND(B2)))/(1/48))*(1/48)
If you need to setup your report into 15 minutes interval then replace 1/48 with 1/96.
We have then used the COUNTIFS and SUMIFS function to create the data view in the Interval Data sheet.
 

 

Understanding the VBA code to send mails

I will be discussing only the key elements of the code here.

Sheets(Array("Cover", "Interval Data", "rawData")).Copy

This list will create a new workbook containing the 3 sheets that we have included within the Array() parameter. If your report has more sheets feel free to add them.

Set objfile = New FileSystemObject

If objfile.FolderExists(xDir & xMonth) Then
If objfile.FileExists(xPath) Then
objfile.DeleteFile (xPath)
newWB.SaveAs Filename:=xPath, FileFormat:=xlOpenXMLWorkbook, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

Application.ActiveWorkbook.Close
Else
newWB.SaveAs Filename:=xPath, FileFormat:=xlOpenXMLWorkbook, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
Application.ActiveWorkbook.Close
End If
Else
xNewFolder = xDir & xMonth
MkDir xNewFolder
newWB.SaveAs Filename:=xPath, FileFormat:=xlOpenXMLWorkbook, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
Application.ActiveWorkbook.Close
End If

The above code checks if the correct folder exists for the report to be saved or not and creates one if not existing. This also takes cares of overwriting the existing report in case you need to re-run the report again during the same day.
Creating the List of recipients

currentWB.Activate
Sheets("Email").Visible = True
Sheets("Email").Select

strEmailTo = ""
strEmailCC = ""
strEmailBCC = ""

xStp = 1

Do Until xStp = 4
Cells(2, xStp).Select
Do Until ActiveCell = ""
strDistroList = ActiveCell.Value
If xStp = 1 Then strEmailTo = strEmailTo & strDistroList & "; "
If xStp = 2 Then strEmailCC = strEmailCC & strDistroList & "; "
If xStp = 3 Then strEmailBCC = strEmailBCC & strDistroList & "; "
ActiveCell.Offset(1, 0).Select
Loop
xStp = xStp + 1
Loop

The above code will create the list of people for whom the report is intended. We make use of the Do Until Loop here to update the 3 variables to hold the TO, CC and BCC list. The actual email addresses are captured from the Email sheet of the report template.
Please note: there should be no blanks in the list when you are defining the same.

Set olApp = New Outlook.Application
Dim olNs As Outlook.Namespace
Set olNs = olApp.GetNamespace("MAPI")
olNs.Logon
Set olMail = olApp.CreateItem(olMailItem)
olMail.To = strEmailTo
olMail.CC = strEmailCC
olMail.BCC = strEmailBCC
olMail.Subject = Mid(xFile, 1, Len(xFile) - 4)
olMail.Body = vbCrLf & "Hello Everyone," _
& vbCrLf & vbCrLf & "Please find attached the " & Mid(xFile, 1, Len(xFile) - 4) & "." _
& vbCrLf & vbCrLf & "Regards," _
& vbCrLf & "Chandoo.Org"

The above code creates a new instance of Outlook and then logs in to your default mailbox, using which we will be sending the mail out to the recipients. We also create the body of the mail and specify the To, CC and BCC list.

olMail.Attachments.Add xPath
olMail.Display

Finally we add the attachment to the email we have created and then using the Display method bring it on the screen. You may also use the .Send method to send the mail directly.
That is all the code we needed to create a copy of the report with selected few sheets and then send them out using VBA. There are a lot of other methods using which you may be able to send out mails, however this specifically helps out to create report templates to use within your organization and send out mails.
Do you also use VBA and Other methods to send mails, if yes please share the same for the benefit of everyone.

Download Excel File

Click here to download the file & save it on your system and use it to understand this technique.

Do you use Excel to automate emails?

I often use Excel to automatically email reports & messages. This is quite useful when you have to send a snapshot of a report to a large team, but need to customize the email for each recipient.
What about you? Have you used Excel to automate emails? What is your experience like? Do you use VBA or some other technique? Please share using comments.

More on VBA & Macros

If you want to learn more about using VBA to automate reporting & email tasks, read these:

Join our VBA Classes

If you want to learn how to develop applications like these and more, please consider joining our VBA Classes. It is a step-by-step program designed to teach you all concepts of VBA so that you can automate & simplify your work.

Click here to learn more about VBA Classes & join us.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

13 Responses to “Gantt Box Chart Tutorial & Template – Download and Try today”

  1. Oli says:

    Hi Chandoo

    As one of your students I have followed your detailed example through with great success. However, Excel is acting in an unexpected way and I wonder if you could take a look?
    http://cid-95d070c79aef808e.office.live.com/self.aspx/.Public/Gantt%20Box%20Chart.xlsm
    On my version, I have to type 40239 (Which equates to 2 Mar 2010) to get the chart to display 31 May 2010 (which should be 40329)!!??

    Have I done something wrong or is Excel acting up?

    Thx
    Oli
    PS Your example file in 2007 displays correctly.

  2. Dave says:

    Hi,

    I like this idea a lot, but I agree the name is a little drab.

    As an American I may just be seeing things, but to me the combination of lines and bars on your chart looks like a bunch of cricket bats.

    Maybe you could work that into a catchier name. 🙂

    Cheers!

  3. Bob says:

    Here is some code I use to keep the axis synched.
    It may be useful to some of your readers
    It is based on a comment I saw on Daily Dose of Excel.

    Function SynchGanttAxis(Cname, lower, upper)
    'Sets the X min and X max for Category axis

    Application.Volatile

    On Error Resume Next
    '
    'Top Horizontal Axis
    With ActiveSheet.Shapes(Cname).Chart.Axes(xlCategory, 1)
    .MinimumScale = lower
    .MaximumScale = upper
    End With

    'Bottom Horizontal Axis
    With ActiveSheet.Shapes(Cname).Chart.Axes(xlValue, 2)
    .MinimumScale = lower
    .MaximumScale = upper
    End With

    End Function

    Function SynchVerticalAxis(Cname, lower, upper)
    Application.Volatile
    On Error Resume Next
    ' Excel 2007 only
    'Right hand vertical axis
    With ActiveSheet.Shapes(Cname).Chart.Axes(xlValue, 1)
    .MinimumScale = 0
    .MaximumScale = upper
    End With

    End Function

  4. Chandoo says:

    @Oli.. Can you check your file again.. I see 40329...

    @Dave: Even I saw things.. the bars actually looked like lollipops. How about calling this lollipop chart - now that would be yummy and goes along the tradition of naming charts after eatables (bar, pie, donut...)

    @Bob: Superb stuff... thanks for sharing 🙂

  5. Mike H says:

    Hi Chandoo
    This looks really good and I think it can also be applied to show project phases / milestones.

    Question: Thinking further could this be amended to display a project lifecycle (Idea through to Implementation say 7 phases) on one bar / row? Just imagine 20 projects within a programme all on one chart one bar each showing their respective lifecycle stages i.e. on one page.

    Idea: As the Gantt Box Chart this is quite intensive to set up re formatting etc how about the added extra of once you have completed this to "Save as template" i.e. saves the formatting and layout of the chart as a template so you can apply to future charts. Simple to do and will save the time formatting etc again and again and again.
    Therefore tip: Click on your chart demo and then click on Save As template icon (2007) - edit file name and click on save. Ready to use / apply via Templates in Change Chart Type window.

    Thanks and be very interested if the lifecycle question can be resolved

    Mike

  6. Oli says:

    How embarrassing.

    I was obviously suffering from numerical dyslexia. I was one of those days.

  7. Chandoo says:

    @Mike H: You can easily make this chart to work like a generic project lifecycle plan chart. All you have to do is,

    1. in a separate sheet define the steps of lifecycle and various dates in a table (with 5 columns for each of the projects you have).
    2. now use a control cell to input the project name you want to show in the chart
    3. based on the input, use OFFSET Formulas to get the correct data
    4. Rest is same as the tutorial above

    For more info on the dynamic charting visit http://chandoo.org/wp/tag/dynamic-charts/ and http://chandoo.org/wp?s=OFFSET

  8. Your solution is really smart but in the en Excel isn't meant to do stuff like this. I, as a former PM, always thought is was frustrating that you had to do stuff like this for something simple like a Gantt chart. So I built Tom's Planner. And would like to plug it here. I think it really solves the problem you are trying to solve in the most efficient way. Check out http://www.tomsplanner.com for a free account or play around with the demo.

  9. Lopi says:

    Hi there,
    Chandoo - this is really a very nice and helpfull chart - I adopted it, so I can report a forecast or the delay of a certain task (coming from my role as an auditor for projects).
    One topic I´m currently struggeling with: I do have a project lasting for lets say 12 month. For a management reporting, I want to have kind of snapshot, lets say one month back and 2 month in the future. I tried with the offset formula, but failed. Any idea?
    Thx
    Lopi

  10. [...] Ein viel geliebter Klassiker ist die Erstellung von GANTT-Diagrammen mit Excel. Wir hatten das Thema wiederholt schon hier. Chandoo.org hat sich mal wieder mit einer neuen Variante hervorgetan: Das GANTT-Box-Chart. [...]

  11. David says:

    Hi Chandoo - fantastic xls. One thing I can't figure out how to do is adjust the alignment of the vertical axis. I would like to left align so that I could indent to represent sub tasks. Can that be done? Or is there a better way?

  12. Paul says:

    I've been trying to work out if there's a way to show weekends on the graph. The closest thing I've got is to add them on a secondary axis, but then I haven't been able to keep both axis lined up together! Any ideas?

    Following on from this - is it possible to show things like holidays?

Leave a Reply