Excel Macro to automatically send emails to a list of people

For your own safety, lets be clear on the below understanding before you start reading or possibly using the below excel solution:

You can assume I’m an absolute idiot on MSExcel.

I have absolutely no formal training of any kind on excel, macros or on programming for that matter.

I work like a scavenger searching for bits and pieces of macro codes on the net and putting them together to suit my needs.

There may be bits which I may not understand myself, but there will never be a harmful code posted here as I understand and respect the privacy and integrity of your system.

If you have improvements or suggestions on the excel solutions posted here, please feel free to post a comment or mail me at gulthemacroguy@gmail.com

You can also send in your excel related questions / doubts / requirements on the above email and we can explore the answers together.

Quite often we come across the need to send mails to a number of people automatically. Eg: send personalised greetings, send updates to a list of people, salary details to employees, marks to students and so on.

There are a number of softwares available on the net which do this, but they are typically paid versions.

There are other ways to directly using outlook, but those methods need the user to click “OK” every time a message is sent out. This is again for your own security to ensure that virus or spams are not sent without your knowledge. This means you can’t just click a button and forget it.

I could see the below macro as the most practical way to achieve the desired results.

Before using the macro, you need to do the following actions:

1. Create a list with names in Column A and email addresses in column B in an excel sheet. (this macro is for 3 names, but you can add as many as you want)
2. Create a button in excel.
3. Double click on the button and add the below macro code in the window which opens up.

Private Declare Function ShellExecute Lib "shell32.dll" _

Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _

ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _

ByVal nShowCmd As Long) As Long

Private Sub CommandButton1_Click()

Dim UserName As String

UserName = “Macroguy”

   

    Dim Email As String, Subj As String

    Dim Msg As String, URL As String

    Dim r As Integer, x As Double

    For r = 3 To 5

        Email = Cells(r, 2)

       

        Subj = "Automated Mail Alert"

        Msg = ""

        Msg = Msg & "Hi " & Cells(r, 1) & "," & vbCrLf & vbCrLf

        Msg = Msg & "This is an automated mail alert generated using MS excel macros"

        Msg = Msg & Cells(r, 3).Text & "." & vbCrLf & vbCrLf

        Msg = Msg & UserName

       

        Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")

        Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")

               

        Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")

        URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg

        ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus

        Application.Wait (Now + TimeValue("0:00:04"))

        Application.SendKeys "%s"

    Next r

End Sub

Line by Line analysis of the Macro:

Lines 1-4 are required to define a shell command. Shell commands stand for those system commands which need not be part of excel, but are triggered using the macro.

1. Private Declare Function ShellExecute Lib "shell32.dll" _
2. Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
3. ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
4. ByVal nShowCmd As Long) As Long

Line 5 This is the start of our macro. This macro needs to be attached to a command button. The same macro can be attached to any event such as file open, file close, file save etc.

5. Private Sub CommandButton1_Click()

Line 6-7 Just defined the username, this username will be used as a signature in the email. (there are ways to use shell commands and retrieve the user name from the windows user login, but I’ll cover that some other day)

6. Dim UserName As String
7. UserName = “Gulshan”

Line 8 Defined the email, subject, message body and mail link as strings

8. Dim Email As String, Subj As String
9. Dim Msg As String, URL As String

Line 10 Now comes the best part of this macro. You can send mails to more than one recipients using this macro and the message will go as an individual mail to each of them. We do this be maintaining a list in cells A3 – B5, column A contains the names and B contains the email addresses.

10. Dim r As Integer, x As Double

Line 11 3^rd to 5^th rows in excel contain the email addresses. You can replace 5 with the last row of your list of mails.

11. For r = 3 To 5

Line 12 (r,2) tells the system to look at 2^nd column of each row and take the email address from there.

12. Email = Cells(r, 2)

Line 13 Define the subject. (can also be set to pick up from a cell reference)

13. Subj = "Automated Mail Alert"

Line 14 – 18 Create the message body: (vbCrLf sends “Enter” command or line break to the message)

14. Msg = ""
15. Msg = Msg & "Hi " & Cells(r, 1) & "," & vbCrLf & vbCrLf
16. Msg = Msg & "This is an automated mail alert generated using MS excel macros"

17. Msg = Msg & Cells(r, 3).Text & "." & vbCrLf & vbCrLf
18. Msg = Msg & UserName

Line 19 – 20 Replace all the spaces in the message with the system code for space. This is done because the system will create a hyperlink and hyperlinks are not supposed to contain spaces.

19. Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
20. Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")

Line 21 Replace all the line breaks in the message with the system code for line breaks. This is done because the system will create a hyperlink and hyperlinks are not supposed to contain line breaks.

21. Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")

Line 22 Create the URL link for the message by concatenating the above data.

22. URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg

Line 23 Execute the hyperlink. This is as good as clicking on the link. This will open a new outlook message window with the first recipient, subject and the message body.

23. ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus

Line 24 – 25 Now the system will wait for 4 seconds and then send the send command (keyboard strokes Alt+S) to send the mail. The 4 seconds wait has been created to accommodate for the system speed. You can change it depending on how fast or slow your system is.

24. Application.Wait (Now + TimeValue("0:00:04"))
25. Application.SendKeys "%s"

Line 26 Now move on to the next record.

26. Next r

Line 27 End the macro after all mails have been sent.

27. End Sub