It is useful sometimes to read a worksheet line-by-line and selectively take data from it. An example below shows how to do this.
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”.
The following example manipulates the data by examining each row of email addresses and concatenating them into a single address line. While Excel has built-in Transpose functions, we intend with our code to exclude some specific addressees and add delimiters.
Our application needs one sheet only. Copy the information below to cell A1:
| Email address |
| tcruise@paramount.com |
| julia.R@disney.com |
| Jules.dassin@gaumont.co.fr |
| EnricoFellini@cinecitta.co.it |
| marymontrary@bedlam.org.uk |
Place a button on the form to kick-off the code.
Let’s assume there are hundreds of these emails instead of only five; we want to send each the same message and the same copy of our screenplay. There are several ways of doing this, most notably by calling the emailer, like Outlook, from VBA code. However, this exercise serves two purposes:
- Show how to cycle easily through a worksheet;
- Highlight Excel’s ability to examine each row, and act on it.
The VBA Code
The VBA code will concatenate all the addresses, inserting a semi-colon after each, so that they can be pasted in one go into the email’s addressee field. You will note from the code that, after examining the rows, it will exclude French ones.
Insert a module, and enter the following:
Option Explicit
Dim strEmail As String
Dim strAll As String
Sub Main()
strAll = ""
Range("C2") = strAll 'clear previous program results
Sheets("Sheet1").Select
Range("A2").Select
Do While ActiveCell > "" 'loop until row 7
strEmail = ActiveCell
If Right(strEmail, 2) <> "fr" Then 'exclude France
strAll = strAll & strEmail & ";"
End If
Range("A" & ActiveCell.Row + 1).Select 'move on a row in the SAME column
Loop
Range("C2") = strAll
End Sub
The code keeps the active cell in column A, only the rows move on. To reference information elsewhere in a row, we would use:
Variable = Activecell.offset(offset rows, offset columns)
To get a value from column B in the active row, for instance, the code would read Activecell.offset(0, 1). To get A2’s value when we’re in A3 would read Activecell.offset(-1, 0).
To test your code, assign the button to the macro “Main”.
Some Internet Service Providers, as an anti-spam precaution, might limit the number of emails you can send at once. If you use this method, it is probably best to check with them before despatching hundreds of emails in one go.
Handling Excel File Corruption
From time to time we encounter the corruption of workbooks to the extent that Excel itself can’t recover them. This is a disaster for most Excel users, including the experts. However, if you have an effective recovery tool to hand, you can restore damaged xlsx files easily and quickly.
Author Introduction:
Felix Hooker is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair rar corruption and sql recovery software products. For more information visit www.datanumen.com
There is also no need to select anything. It is not good VBA programming practice to do so. That is inefficient and may cause the screen to jump around. Also, there is no reason for the variables to have scope outside the subroutine. Better coding would be:
Sub Main()
Dim lng As Long
Dim r As Range
Dim strEmail As String
Dim strAll As String
With ThisWorkbook.Sheets(“Sheet1”)
strAll = “”
Set r = .Range(“A1”).CurrentRegion
For lng = 2 To r.Rows.Count Step 1
strEmail = r.Rows(lng).Cells(1)
If Right(strEmail, 2) “fr” Then ‘exclude France
strAll = strAll & strEmail & “;”
End If
Next lng
.Range(“C2”) = strAll
Set r = Nothing
End With
End Sub
This is very bad code. Among other issues, you have an actual logic error. The lines
Range(“C2”) = strAll
Sheets(“Sheet1”).Select
should be reversed in order.