New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Send Worksheet Data By E-Mail

Free Excel Templates

Send Worksheet Data By E-Mail

Postby Sisyphus » Sun Dec 15, 2013 4:10 am

  • 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 attached workbook does the latter, but the possibilities are literally endless. Whenever you wish to send the data contained in a single row of your database this template can do the job for you - in any language.

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
When you install this code in your own workbook you may specify another ResponseColumn - for example by changing Const ResponseColumn = "A" into Const ResponseColumn = "C". By modifying the Const TemplateName As String = "Template1" you can send another e-mail which is set up in another template. You can name your templates freely. There are instructions on the template itself how to make copies of it.

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
If True = True is always True. But it is very easy to replace this useless formula with one that actually differentiates between accounts due and overdue, sufficient inventory and low balances, etc. etc.

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
This code will prepare the emails and place them into your Outlook but it will wait for you to press the Send button. In order to actually send the mails created by the program please modify these two lines as shown below.
Code: Select all
'        .Display                    ' View, edit and Send manually
        .Send
The apostrophe before the Display command will prevent it from being executed. Instead, the Send command is now hot.

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.
You do not have the required permissions to view the files attached to this post.
Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Return to Excel Templates

Who is online

Users browsing this forum: No registered users and 3 guests