How to Batch Send Multiple Emails via Excel VBA

Posted January 21, 2017 By AuthorFH

We examine the code required to send emails in bulk from Excel with data particular to each recipient, plus a general attachment. The workbook used in this exercise can be found here

The article assumes the reader has the Developer ribbon displayed and is familiar with the VBA Editor. If not, please Google “Excel Developer Tab” or “Excel Code Window”.

It is further assumed that Outlook is the email program.

Data

Copy the following text into a workbook:

Title Member Subs Email
Mr Robert Jones $90.00 r.jones@acme.com
Miss Mary Muffet $75.00 mary.m@gmail.com
Master Jack Sprat $50.00 jacksprat@gmail.com

Copy The Text Into A Workbook

Discovering after the event that an automated emailing has run berserk is embarrassing. So, test your code by initially using your own email address; enter it in column F. You need only two or 3 instances

Open the Code window, and select Tools>References> and tick Microsoft Outlook nn.n Object Library,

The Process.

The body takes up most of our code, and looks daunting without explanation. Our “storyboard” goes like this:

  • Excel creates a new instance of Outlook.
  • It asks for an attachment filename. If one is to be included, please save it for simplicity sake into the same directory from which the workbook opened (our workbook simply looks back along its own path for additional documents).
  • Excel will cycle through the spreadsheet rows, using column B as a base……and will create a message body containing varying subscription amounts for members.
  • The body will look like the image. Char(9) is used for tabbing, Char(13) for line feeds.Message Body
  • Any file specified is attached
  • The emails are sent.

Other Considerations

Barriers to sending multiple emails could manifest in Antivirus settings, Or your ISP could block excessive emails. If so, you might have to program a run in batches of, say, 50.

Outlook might block multiple emails itself if there is no antivirus software present. Check this via Outlook menus File -> Options -> Trust Center -> Trust Center Settings -> Programmatic Access.

If the way is clear, we can proceed. In the Code window, choose Insert -> Module

The Code(1)

Sub SendMsg(Optional AttachmentPath)
  Dim strYear As String
  Dim objOutlook As Outlook.Application
  Dim objOutlookMsg As Outlook.MailItem
  Dim objOutlookRecip As Outlook.Recipient
  Dim objOutlookAttach As Outlook.Attachment
  Dim strAttach As String
  Dim nCol As Integer

  On Error GoTo ErrorMsgs
  strYear = Format(Now(), "YYYY")
  ActiveWorkbook.Sheets("Sheet1").Range("B3").Select             'Start point
  If ActiveCell.Offset(0, 4) = "" Then nCol = 3 Else nCol = 4           'Establish email address column
 
  Set objOutlook = CreateObject("Outlook.Application")        ' Create the Outlook session.
  strAttach = InputBox("Enter attachment name", "", ActiveWorkbook.Path & "\")

So far, we have booked our variable space, and positioned the cursor at the start point, B3.

We have also checked if the user has placed any email address in the test section, cell F4.

If Activecell.offset(0,4) = “etc”…

If so the program will send only emails to addresses found in column F until they run out. Otherwise the program will use the proper emails.Start Point Selected

Lastly, we have asked for an attachment file name, which we’ll store in strAttach. If there is no attachment, we expect the user to press <Cancel>.

The remainder of the code follows:

The Code(2)

  For i = 3 To 500                                            ‘Enter a suitable range.
        If Cells(i, 2) = "" Then Exit For    ‘Escape the loop if complete        
      Cells(i, 2).Select    ‘i.e. B3    
      sTitle = ActiveCell    
      sMember = ActiveCell.Offset(0, 1)
      nSubs = ActiveCell.Offset(0, 2)
      sRecip = ActiveCell.Offset(0, nCol) 'Email address

      '@@@@@@@@@ create body @@@@@@@@@@@@@@@@@@@@@@
      Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
      With objOutlookMsg
        Set objOutlookRecip = .Recipients.Add(sRecip)
        objOutlookRecip.Type = olTo
        
        'Set the Subject & Body.
        .Subject = "Membership Fees " & strYear
        sBody = "The Flat Earth Society " & Chr(13) & Chr(13)
        sBody = sBody & "Membership Statement - " & sTitle & " " & sMember & Chr(13)
        sBody = sBody & "-------------------------------------------------------------" & Chr(13) & Chr(13)
        sBody = sBody & "Year" & Chr(9) & "Amount" & Chr(13)
        sBody = sBody & "------" & Space(4) & "------------" & Chr(13)
        sBody2 = "Please make your payment by EFT into our account at xxxx. If you have already paid, please advise accordingly " & Chr(13) & Chr(13)
        sBody2 = sBody2 & "We appreciate your membership and support in keeping the Earth flat." & Chr(13) & Chr(13)
        
        sBody2 = sBody2 & "Attached is a copy of the Outings Programme which is also available on our website at http://www.flatearth.org" & Chr(13) & Chr(13)
        sBody1 = sDesc & strYear & Chr(9) & nSubs & Chr(13) & Chr(13)
        sBody = sBody & sBody1 & sBody2 & Chr(13) & Chr(13)
        'MsgBox sBody           'Uncomment this line to observe the layout of your message
        .Body = sBody
        '@@@@@@@@@ End body @@@@@@@@@@@@@@@@@@@@@@@
        
        If strAttach > "" Then     'Attach a document
                    Set objOutlookAttach = .Attachments.Add(strAttach) 
        End If
        If Not objOutlookRecip.Resolve Then     'Resolve each Recipient's name.
                    objOutlookMsg.Display
        End If
        .Send
      End With
  Next i
  MsgBox "Complete"
    Set objOutlookMsg = Nothing
    Set objOutlook = Nothing
    Set objOutlookRecip = Nothing
    Set objOutlookAttach = Nothing
    Exit Sub
ErrorMsgs:
    If Err.Number = "287" Then
        MsgBox "You clicked No to the Outlook security warning. " & _
        "Rerun the procedure and click Yes to access e-mail" & _
        "addresses to send your message. For more information, " & _
        "see the document at http://www.microsoft.com/office" & _
        "/previous/outlook/downloads/security.asp. "
    Else
        MsgBox Err.Number, Err.Description
        Resume Next
    End If
End Sub

Test Your Code.

Save the workbook as an xlsm, in order to establish an automatic path to possible attachments. Remember to save attachments into this same directory.

Using the Developer’s tab, insert a command button and assign it to the macro SendMsg.

Press the button to run.Press The Button To Run

This basic application is open to further customisation and improvements (and will run from database tables as well).

Disaster Recovery.

In instances where software crashes, it would be useful to have a tool that can recover Excel files where its own self-recovery routines fail. Such corruption of files is not as rare as one would expect, particularly when dealing with large and complex workbooks.

Author Introduction:

Felix Hooker is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair rar data and sql recovery software products. For more information visit www.datanumen.com

Be the first to comment
             

Data, data, data – without it a database would be pretty useless. So it’s no surprise that one of the most frequently asked questions when it comes to getting that data into your Access table(s) is – how can I make my life easier when I have to regularly import lots (and lots!) of files? As it’s such a common task, there’s obviously a number of ways to skin that particular cat, but here we’re going to look at just one – in my opinion the easiest – especially if you’re doing this on a regular, or even scheduled timeframe. A little planning (and I do mean a little!) and a little VBA magic and you’ll be good to go – read on for to get started…

Importing all Excel files in a folder

Import Excel File To AccessFor this article, we’re going to look at importing a number of Excel files within a given folder, but you could just as easily import csv files etc – we’ll discuss how to change the code to handle csv files shortly.

Firstly, let’s define what we want the code to do:

“We want to import all files from a given folder into an existing table – we also want to specify whether the files have a header line or not”

Seems simple enough, so let’s look at the code to do this:

Sub ImportfromPath(path As String, intoTable As String, hasHeader As Boolean)

Dim fileName As String

'Loop through the folder & import each file
fileName = Dir(path & "\*.xls")
While fileName <> ""
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, intoTable, path & fileName, hasHeader
   'check whether there are any more files to import
    fileName = Dir()
Wend

End Sub

Checking for problems…

Access Database CorruptionBefore we get into the detail about how this code works, it’s important to note that there is no error checking included in the code above – this is deliberate so that the code is kept easily readable but I’d strongly suggest you add error handling code to your database so you keep the chances of Access database corruption to a minimum!

OK – first things first. To keep the code as usable in different circumstances as possible we’re asking for the path name (where the files are stored), the name of the table you want to import these files into, and whether the files in this folder all have a header or not (i.e. do the field names appear on the first row of the spreadsheet).

You’ll notice that the part of code that searches for files – Dir(path & “\*.xls”) – appends a wildcard and file extension to the supplied path. I mention this as it would be very easy to alter the code if, for example, you wanted to only import files that matched a particular naming convention (e.g. all files that have “January”, or “2017” as part of the file name). You could, if you wanted to, even add that filter as a parameter to the subroutine call.

Anyway, if a file matching the given criteria (any “.xls” file in the folder using the in this case), it immediately attempts to import that file using the TransferSpreadsheet command.

Where to change the code to suit your needs…

It is at this point that you could alter the code so that instead of importing a spreadsheet, it imports csv files (using the “TransferText” command) etc

The code then loops until there are no further files to import in that folder.

It’s a small piece of code, but one that you’ll find yourself using over and over again I’m sure!

Author Introduction:

Mitchell Pond is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair SQL corruption and excel recovery software products. For more information visit www.datanumen.com

Be the first to comment
             

One needs to apply apt thought while choosing a server side antivirus on an Ms Exchange Server and also remain aware of possible issues that may crop up.  

Points To Consider For Running Antivirus Programs On Ms Exchange ServersThe Ms Exchange mailbox databases are full of important and sensitive information which needs all the protection that they can get. Purely from a security view point, running an antivirus program on Ms Exchange Server is a good choice. In fact most experts agree that multilevel security goes a long way in securing your network.  However there are several key points that you need to keep in mind while going down this path which we will discuss in detail.

Key Components that any Antivirus Program you choose must Possess

Antivirus ProgramAny antivirus program that you choose should provide all possible forms of protection to your Exchange databases. Key components of such a program must include

1. Real Time Protection

Memory Resident Scanning is responsible for monitoring all data, stored in the form of files and process, loaded and running in the active memory of the computer. The application you choose should offer this at all times.

2. File Level Scanning

Such a program checks files contained on the hard disk for any kind of viruses, you can either choose to set this on regular schedules, or do it manually. A lot of anti – virus programs automatically begin scanning once the virus signatures get updated, this is done to make sure that all files get scanned for latest signatures.

3. Intelligent Detection Mechanism for Emerging Threats

The Server side antivirus that you choose must be able to proactively determine programs which can be a threat to the system and Exchange data. It should be able diagnose attack vectors and block them before any harm is done.

4. Points to Remember

Installing the antivirus program might be helpful in protecting your system and data; there are certain things that need to be considered while you run the program. This kind of antivirus program needs to be configured before they can be implemented. While configuration, you have to make sure that it is done properly, if there is any kind of problem in configuration, the program will not work properly. For the purpose of configuration, you should either take help of someone experienced, or understand it properly yourself.

Probable Errors

There are lots of possible errors that could arise when you begin using the program; here are a few of the most common ones.

Locking or quarantining of an open log file or database file that needs modifications by Exchange.

This could further lead to failures in the application, most common one being error 1018 (log error). This can be prevented by excluding log files from the process of scanning by the program all together.

Data corruption errors can also occur in some cases. Such issues typically originate out of access conflicts when an antivirus scan locks on to an element accessed by any Exchange Server process. In some cases, data corruption can be extensive and it would mandate the need for an Exchange ost recovery operation.

Author Introduction:

Van Sutton is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Outlook mail problem and bkf recovery software products. For more information visit www.datanumen.com

Be the first to comment