Tuesday, April 7, 2009


It was appraisal time, I was proudly a part of the Team HR and more specifically an HR partner for one of the largest delivery groups.
It was time to communicate the KRA (key result areas) and CSFs( Critical sucess factors) to each and everyone employee and after a round of who's eligible, who's not, why? etc. we had a quite an enormous number, close to 1800!
We were not still geared up for an ERP and so doing this manually was quite a task.
We spoke to one of the teams here, who helped us with an intranet application. This application was shortlived with it's life begining from the HR admins uploading a KRA onto the system against the respective employee and the employee downloading the same. The problem came when we had to do this to employees who were onsite (other locations where intranet applications do not work)
After looking at the number of people who were working OF and Onsite, we had to think of a way to do this automatically, We first thought of mail merging, but with attachments it would not be possible.
we figured out that, it is possible to send mails with attachments with the help of Macros.
With the help of Google and a colleague, we got this in place and when this worked, I was overjoyed!!
we had found a way to send attachments to approx 500 people within minutes.
To help those in need, a detailed list of action items to code Macros to send attachments.

Step 1: Open an excel sheet and define the basic colums such as the name, Email ID and the exact path to the attachment and save this.

Emp ID


Email ID





C:\Documents and Settings\Desktop\ABC_1111.xls




C:\Documents and Settings\Desktop\XYZ_2222.xls

Step 2:
Go to Tools>Macros>Record New Macro
This options opens a window where one has to name a macro, give a relevant, unique name

Step 3:
Once you have names the Macro, again go to Tools, and hit on Macro (The option above Record new macro)
A click on the relevant would lead you to a small window with option such as Run, cancel, Step into, Edit, Delete, Options and more. Hit on Step into

Step 4:
Quite a variety of these codes are available on the net. a few of them work and a few throw an error. However after some editing this seems to successfully work. I am pasting the exact code here below

Macro Code to email Customized Attachments
Sub Macroname()
Dim intRecCount As Integer

Dim intCounter As Integer

Dim strEmailID As String

Dim strPath As String

Dim OutApp As Object

Dim OutMail As Object

For intCounter = 2 To 1000

If Len(ThisWorkbook.Sheets(1).Range("A" & intCounter).Value) > 0 Then
strEmailID = ThisWorkbook.Sheets(1).Range("C" & intCounter).Value
strPath = ThisWorkbook.Sheets(1).Range("D" & intCounter).Value

Set OutApp = CreateObject("Outlook.Application")


Set OutMail = OutApp.CreateItem(0)

On Error Resume Next

With OutMail
.To = strEmailID

.Subject = "Mention your subject here"

.Body = "The body of your mail"

.Attachments.Add (strPath)

.Send 'or use .Display

End With

On Error GoTo 0

Set OutMail = Nothing
OutApp = Nothing

Exit For
End If

Next intCounter

End Sub
Step 4: This code should be saved and closed. Get back to the excel sheet and save it with this formula.
Follow step 3 and choose run.

Step 5: Ensure your Outlook is open. Once you click on run. There is an alert message click yes to continue.
Soon after the mail is sent to all mentioned in your reference sheet. The process ends.

When i was in search of a solution to this, i found a relatively large amount of queries on sending attachments as a mail merge. I am not sure if there is a better way to do this. However i firmly think i found one step better if not the best!