- You may wish to inform a customer of his account balance or remind him of an overdue payment.
- You may wish to monitor your inventory and inform the supplier when stocks run low.
- Or you may inform your staff of the balance in their leave account at the end of the year.
The key to this amazing performance is the worksheet 'Template1' included in the attached file. It contains the text of the mail to be sent. It is also the easy-to-use dashboard for selecting the data to be inserted. You can copy this worksheet into your own workbook. There are instructions on how to set it up, and there is a button to start sending e-mails.
Before this button becomes effective you will need to import the code which it calls. Simply drag the module 'MailMan' into your own project. There are more, detailed instructions in the code. You don't need to be able to read or understand code (that's why the instructions are so detailed) but you will have to tell the program the name of the tab where the data will be found. This is what you will find when you look inside,
- Code: Select all
Sub SendToAll()
' This procedure is called by a 'Send To All' button.
' It may also be called from the user interface / keyboard
' Specify the name of the tab on which the data
' are to be found.
Const DataTabName As String = "Data"
' Since you can have many different templates
' in the same workbook you must specify here
' which template is to be used.
' THIS SPECIFICATION IS WITHOUT EFFECT IF ....
' (a) You call this procedure using the 'Send To All'
' button on any of the template tabs, AND ...
' (b) the calling button's name specifies an
' existing template's tab name.
Const TemplateName As String = "Template1"
' Essentially, this specification is a backup
' required only if the procedure is called from
' the keyboard.
Besides being able to send mails to every person in your database you can also set up your system to send mails to selected individuals. One way is to simply double-click on a name, and the mail will be sent. To enable this feature the following code must be copied to the code sheet of your data tab. (Right-click on the tab and select 'View Code' to open that sheet.) In the attached workbook the code is behind the 'Data' tab.
- Code: Select all
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
' Double-click will cause a mail to be sent only
' if you click on a name in the column you specify here,
' at or below the FirstDataRow specified in the template
' (you can define the column using letters or numbers)
Const ResponseColumn = "A"
' Since you can have many different templates
' in the same workbook you must specify here
' which template is to be used when you double-click
Const TemplateName As String = "Template1"
Dim Ws As Worksheet
Dim Rng As Range
Dim C As Long
C = Val(ResponseColumn)
If C = 0 Then C = Range(ResponseColumn & 1).Column
If Target.Column <> C Then Exit Sub
Cancel = True
If SetWorksheet(TemplateName, "template", Ws) Then
Set Rng = Range(Cells(FirstDataRow(Ws, True), C), _
Cells(LastRow(C), C))
If Not Application.Intersect(Target, Rng) Is Nothing Then
SendMaster ActiveSheet, Ws, Target.Row
End If
End If
End Sub
There are many tasks that require mails to be sent to selected groups of recipients, for example, only to customers who didn't pay on time. The code in the attached template is set up for making such selections. Look for this snippet of code in the procedure 'SendMaster'.
- Code: Select all
For R = R To Rl
' Instead of sending a mail to every recipient in the list
' you may apply conditions to select specific recipients
' depending upon the data found in each row.
' Set the conditions in the next line here:-
If True = True Then
SendEmail SendTo, ToCol, R, Msg, WsData, WsTempl
SendCount = SendCount + 1
End If
Next R
In order to send emails using this template you require Outlook. Even so, the attached file is rigged for demonstration and will not, therefore, send any emails. Feel free to test the action. In order to go live look for the code below in the procedure 'SendEmail'.
- Code: Select all
With OutMail
.To = SendTo
.Cc = WsTempl.Cells(NwsCc, NwsData).Value
.Subject = PersonalizedMsg(WsTempl.Cells(NwsSubject, NwsData) _
.Value, InsData)
.Body = Msg
.Display ' View, edit and Send manually
' .Send
End With
- Code: Select all
' .Display ' View, edit and Send manually
.Send
This program hasn't been tested other than in the lab. I shall appreciate feedback on any negative experience you might have with it. I shall also be glad to help you customizing the code to your particular needs. Send me a PM on this site.