This is a guest post by Vijay, our in-house VBA Expert.
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:
- Automatically Generate Report Variations using Excel
- Birthday Reminder & Email in Excel
- What is VBA & Macros? Introduction
- Excel VBA Example Macros
- VBA tutorial videos
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.
12 Responses to “29 Excel Formula Tips for all Occasions [and proof that PHD readers truly rock]”
Some great contributions here.
Gotta love the Friday 13th formula 😀
Great tips from you all! Thanks a lot for sharing! bsamson, particularly you helped me on a terribly annoying task. 🙂
(BTW, Chandoo, it's not exactly "Find if a range is normally distributed" what my suggestion does. It checks if two proportions are statistically different. I probably gave you a bad explanation on twitter, but it'd be probably better if you fix it here... 🙂 )
Great compilation Chandoo
For the "Clean your text before you lookup"
=VLOOKUP(CLEAN(TRIM(E20)),F5:G18,2,0)
I would like to share a method to convert a number-stored-as-text before you lookup:
=VLOOKUP(E20+0,F5:G18,2,0)
@Peder, yeah, I loved that formula
@Aires: Sorry, I misunderstood your formula. Corrected the heading now.
@John.. that is a cool tip.
Hey Chandoo,
That p-value formula is really great for a statistics person like me.
What a p-value essentially is, is the probability that the results obtained from a statistical test aren't valid. So for example, if my p value is .05, there's a 5% probability that my results are wrong.
You can play with this if you install the Data Analysis Toolpak (which will perform some statistical tests for you AND provide the P Value.)
Let's say for example I've got two weeks of data (separated into columns) with the number of hours worked per day. I want to find out if the total number of hours I worked in week two were really all the different than week one.
Week1 Week2
10 11
12 9
9 10
7 8
5 8
Go to Data > Data Analysis > T-Test Assuming Unequal Variances > OK
In the Variable 1 Box, select the range of data for week 1.
In the Variable 2 Box, select the range of data for week 2.
Check "Labels"
In the Alpha box, select a value (in percentage terms) for how tolerant you are of error.
.05 is the general standard; that is to say I am willing to accept a 95% level of confidence that my result is accuarate.
Select a range output.
Excel calculates a number of results: Average (mean) for each week's data, etc.
You'll notice however that there are two P Values; one-tail and two-tail. (one tail tests are for > or .05), the number of hours I worked in week two is statistically equivalent to the number of hours I worked in week one.
So here’s a way you might want to use this. You put up a new entry on your blog. You think it’s the best entry ever! So you pull your webstats for this week and compare it to last week. You gather data for each week on the length of time a visitor spends on your website. The question you’re trying to prove statistically is whether there’s an average increase in the amount of time spent on your website this week as compared to last week (as a result of your fancy new blog post). You can run the same statistical test I illustrated above to find out. Incidentally, it matters very little to the stat test whether the quantity of visitors differs or not.
Anyhow, the Data Analysis toolpack doesn't perform a lot of stat tests that folks like me would like to have access to. In those cases I have to either use different software, or write some very complicated mathematical formulas. Having this p-value formula makes my life a LOT easier!
Thanks!
Eric~
Fantastic stuf..One line explanation is cool.
Thanks to all the contributors
OS
Take FirstName, MI, LastName in access (you can fix it to work in excel) capitalize first letter of each and lowercase the rest and add ". " if MI exists then same for last name:
Full Name: Format(Left([FirstName],1),">") & Format(Right([FirstName]),Len([FirstName])-1),"") & ". ","") & Format(Left([LastName],1),">") & Format(Right([LastName],Len([LastName])-1),"<")
I teach excel, access, etc etc for a living and i have my access students build this formula one step at a time from the inside out to show how formulas can be made even if it looks complicated. Yes I know I could just do IsNull([MI]) and reverse the order in the Iif() function but the point here is to nest as many functions as possible one by one (also I illustrate how it will fail without the Not() as it is)
Extract the month from a date
The easiest formula for this is =MONTH(a1)
It will return a 1 for January, 2 for February etc.
if in a column we write the value of total person for eg. 10 if we spent 1.33 paise each person then how we get total amount in next column and the result will in round form plzzzzz solve my problem sir................... thank u
@Anjali
If the value 10 is in B2 and 1.33 paise is in C2 the formula in D2 could be =B2*C2
If the values are a column of values you can copy the formula down by copy/paste or drag the small black handle at the bottom right corner of cell D2
kindly share with me new forumulas.
How to convert a figure like 870.70 into 870 but 871.70 into 880 using excel formula ? Please help.