Hi guys,
I'm a VBA user, and my
VB knowledge is very limited so forgive me if I come back a few times for clarification.
I would normally be using an access database and wouldn't have a problem otherwise but having to work with what I've been given at the moment I'm a little stuck; I have an excel spreadsheet containing 5 columns and 100 rows. Each Column should have some information in, but if column E is empty, I need to chase for the relevant information.
I currently have something that begins an email, keeps randomly selecting a row until it comes across one where column E is empty, and then it prints the information from columns A, B, C & D into the email.
It does this 5 times and then finishes building the email.
My problem is that by randomly selecting 5 lines this way, it could potentially pick the same 5 lines in each email. Is there a better way of doing this whilst staying in Excel? I've stripped down the code to the essential bits here;
Quote:
Sub CustomerProgramNotes()
Dim MacroRowCount
Dim MacroSelectedRow
Dim MacroSelectedCell
Dim MacroBlankCellCount
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim MacroRandomRow
Dim MacroRandomRowCount
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
strbody = ""
MacroRandomRowCount = 1
Do Until MacroRandomRowCount = 6
Dim RandomRowIsBlank
RandomRowIsBlank = "No"
Do Until RandomRowIsBlank = "Yes"
MacroRandomRow = Int(Rnd * ((MacroRowCount - 1) - 2) + 2)
MacroSelectedCell = "D" & MacroRandomRow
If Len(Range(MacroSelectedCell).Value) < 1 Then
RandomRowIsBlank = "Yes"
Else
RandomRowIsBlank = "No"
End If
Loop
MacroSelectedCell = "A" & MacroRandomRow
strbody = strbody & " Customer: " & Range(MacroSelectedCell).Value & vbCrLf
MacroSelectedCell = "B" & MacroRandomRow
strbody = strbody & " Type: " & Range(MacroSelectedCell).Value & vbCrLf
MacroSelectedCell = "C" & MacroRandomRow
strbody = strbody & " Scanner: " & Range(MacroSelectedCell).Value & vbCrLf
MacroSelectedCell = "D" & MacroRandomRow
strbody = strbody & " Notes: " & Range(MacroSelectedCell).Value & vbCrLf
MacroRandomRowCount = MacroRandomRowCount + 1
MacroRowCountx = MacroRowCountx + 1
Loop
On Error Resume Next
With OutMail
.To = """
.CC = ""
.BCC = ""
.Subject = "Customer Software Requirements"
.Body = strbody
.Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
|
dBforums
>
Help Improve this process, or show me how to avoid duplicates in 5 random numbers?
06-08-11, 09:57
Help Improve this process, or show me how to avoid duplicates in 5 random numbers?
Linear Mode
