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.
Eg:
| Emp ID | Names | Email ID | Path |
| 1111 | ABC | ||
| 2222 | XYZ |
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")
OutApp.Session.Logon
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 Set
OutApp = Nothing
Else
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!
