Creating a bulk email program using Excel.
Introduction
In the workplace there are emails that are routinely forwarded. These could be emails to possible suppliers requiring quotes to contacting individuals who have signed up for a mailing list.
If there is no infrastructure these emails are forwarded individually, at times accessing the sent folder, amending the previous email and sending it to a new party. This process can be highly time consuming and prone to errors.
However there are methods to improve this process.
Amongst a plethora of methods is the use of Excel and its VBA functions to create an emailing process. The benefit of this system is that it is relatively easy to establish and is low cost. This project outlines how such a system can be created.
Simple Bulk email
This is a bulk email system that preserves the user's desire to control the contents of the email as they wish to a certain extent. This means that provided that attachments are not included the user is able to use the program for a number of different occasions.
The system works as follows:
Auto send
This cell is formatted using data validation to take only enabled and disabled as its inputs, with a prompt that explains its purpose and its risk
The purpose of this function is to decide whether the emails require additional human review or whether it can be sent without such review.
CC
Whilst the use may be limited, there may be instances where the company/ team policy may be to CC a specific email address for all outgoing emails.
If this is the case, this cell can be used
Subject
As with a typical email, the user is prompted to add a subject
Message
The user is able to add their message.
The message can vary in size as one pleases, however line breaks must fall under a new cell. This favours the average user as line breaks are typically done so with the use of the enter key. This in Excel shifts the highlighted cell down.
Names
The name, if known, of the recipient
The system does account for unknown names such as that addressed to a company.
It also handles poor typing and will capitalise names if they are missing.
Email Addresses
Email address self explanatory and indicates where the email should be sent to.
The user completes the above fields and select create email to draft and/or forward the emails.
In addition a clear button, resets the document to enable a new email to be drafted.
Deeper understanding of its key features
Hidden to the user there are two cells which contain key information.
Editing the message
For the message to appear correctly in the email, it must contain HTML syntax indicating line breaks. As it is unlikely that a user with no coding experience will know or remember to include such syntax, there is a hidden formula in B11 that adds this line break syntax.
This is the reason why the user is asked to use new lines when drafting the email.
The formula used is:
=TEXTJOIN("<br>",TRUE,C10:C1048576)Noted: there is a method to make this dynamic but in this case, I have opted for this method and making the text blend in with the background.
Updating the names
As indicated, the system supports the user typing the name without capitals or missing names.
This is done by a formula being added by the VBA to process the names. If there is no name, the email will be drafted with the phrase "To whom it may concern" in the first line. If not it will capitalise the first later of each word in the name column and add it to the email after "Dear ".
This formula is:
=IFS(ISBLANK(G5),"""",ISBLANK(F5),""To whom it may concern"", TRUE, CONCAT( ""Dear ""&PROPER(F5)))"The formula is dragged to the length of email addresses added to the file to ensure it is correctly applied to all names.
VBA
The VBA used to create the emails is as follows. It should be noted that firstly this system only supports outlook and therefore if other mailing software is used the code will require revising. Secondly, it is set so as to use the user's own signature.
Sub Send_email()
' create objects needed to send emails
Dim outlook_app As Object
Dim msg As Object
Dim sign As String
Dim Email As Worksheet
Dim str As String
' set the main sheet used to creat email
Set Email = ThisWorkbook.Sheets("Email")
' process names
lastrow = Range("G5").End(xlDown).Row
ActiveSheet.Range("H5").Formula = "=IFS(ISBLANK(G5),"""",ISBLANK(F5),""To whom it may concern"", TRUE, CONCAT( ""Dear ""&PROPER(F5)))"
Range("H5").AutoFill Destination:=Range("H5:H" & lastrow)
' For each address create email
For i = 5 To Email.Range("G" & Application.Rows.Count).End(xlUp).Row
' create email
Set outlook_app = CreateObject("Outlook.application")
Set msg = outlook_app.createitem(0)
msg.display
sign = msg.HTMLBody
' fill email
With msg
.To = Email.Range("G" & i).Value
.CC = Email.Range("C6").Value
.Subject = Email.Range("C8")
' create email body
' fix email name
.HTMLBody = Email.Range("H" & i) & ",<br><br>" & _
Email.Range("B11").Value & _
.HTMLBody
If Email.Range("C4").Value = "Enable" Then
.send
Else
.display
End If
End With
Next i
Email.Select
Set msg = Nothing
Set outlook_app = Nothing
End Sub
To view this file, visit https://github.com/Lostinworlds/Excel/blob/main/Automated%20email.xlsm
Comments