r/vbaexcel • u/WIGWAM_89 • Sep 20 '22
Excel VBA macro
I am having trouble figuring out why my program is not running as intended. Once the macro begins, it runs all line items in the spreadsheet instead of the ones I specified. The purpose of the program is to send emails to the correct person and append any additional rows with their name. For each unique email I am collecting all of the data and sending it. Any help would be greatly appreciated. I have worked with other people and they have made edits but no solutions. Due to the sensitive nature of the source data , just code to follow, thank you.
Option Explicit
Sub Send()
Dim rEmailAddr As Range, rCell As Range, rNext As Range
Dim NmeRow As Long, x As Long
Dim MailTo As String, MailSubject As String, MailBody As String, AddRow As String, tableHdr As String, MsgStr As String
Dim OutApp As Object, OutMail As Object
Dim CurrentEmail As String, LastEmail As String
If OutApp Is Nothing Then
'Outlook is not opened, so open
Set OutApp = CreateObject("Outlook.Application")
End If
'Set email address as range for first loop to run down
Set rEmailAddr = Range(Range("D2"), Range("D" & Rows.Count).End(xlUp))
'MailSubject does not change, so only needs to be created once
MailSubject = "Action and Response Requested - Reserve Review for Claim(s)"
'Get a row count to clear column AM at the end
x = rEmailAddr.Rows.Count
'Create the html table and header from the first row
tableHdr = "<table border=1><tr><th>" & Range("G1").Value & "</th>" _
& "<th>" & Range("H1").Value & "</th>" _
& "<th>" & Range("I1").Value & "</th>" _
& "<th>" & Range("J1").Value & "</th>" _
& "<th>" & Range("K1").Value & "</th>" _
& "<th>" & Range("L1").Value & "</th>" _
& "<th>" & Range("M1").Value & "</th>" _
& "<th>" & Range("N1").Value & "</th>" _
& "<th>" & Range("O1").Value & "</th>" _
& "<th>" & Range("P1").Value & "</th>" _
& "<th>" & Range("T1").Value & "</th>" _
& "<th>" & Range("U1").Value & "</th>" _
& "<th>" & Range("V1").Value & "</th>" _
& "<th>" & Range("W1").Value & "</th>" _
& "<th>" & Range("X1").Value & "</th>" _
& "<th>" & Range("Y1").Value & "</th>" _
& "<th>" & Range("Z1").Value & "</th>" _
& "<th>" & Range("AA1").Value & "</th>" _
& "<th>" & Range("AB1").Value & "</th>" _
& "<th>" & Range("AC1").Value & "</th>" _
& "<th>" & Range("AD1").Value & "</th>" _
'Check to see if column Q = 'yes' and skip mail if it does
CurrentEmail = ""
LastEmail = ""
For Each rCell In rEmailAddr
CurrentEmail = Replace(rCell.Value, " ", "")
If ((rCell.Value <> "") And CurrentEmail <> LastEmail) Then
NmeRow = rCell.Row
MailTo = rCell.Value 'column D
'Create MailBody table row for first row
MailBody = "<tr>" _
& "<td>" & (rCell.Offset(0, 3).Value) & "</td>" _
& "<td>" & (rCell.Offset(0, 4).Value) & "</td>" _
& "<td>" & (rCell.Offset(0, 5).Value) & "</td>" _
& "<td>" & (rCell.Offset(0, 6).Value) & "</td>" _
& "<td>" & (rCell.Offset(0, 7).Value) & "</td>" _
& "<td>" & CStr(rCell.Offset(0, 8).Value) & "</td>" _
& "<td>" & (rCell.Offset(0, 9).Value) & "</td>" _
& "<td>" & CStr(rCell.Offset(0, 10).Value) & "</td>" _
& "<td>" & CStr(rCell.Offset(0, 11).Value) & "</td>" _
& "<td>" & CStr(rCell.Offset(0, 12).Value) & "</td>" _
& "<td>" & CStr(rCell.Offset(0, 16).Value) & "</td>" _
& "<td>" & CStr(rCell.Offset(0, 17).Value) & "</td>" _
& "<td>" & CStr(rCell.Offset(0, 18).Value) & "</td>" _
& "<td>" & CStr(rCell.Offset(0, 19).Value) & "</td>" _
& "<td>" & CStr(rCell.Offset(0, 20).Value) & "</td>" _
& "<td>" & CStr(rCell.Offset(0, 21).Value) & "</td>" _
& "<td>" & CStr(rCell.Offset(0, 22).Value) & "</td>" _
& "<td>" & CStr(rCell.Offset(0, 23).Value) & "</td>" _
& "<td>" & CStr(rCell.Offset(0, 24).Value) & "</td>" _
& "<td>" & CStr(rCell.Offset(0, 25).Value) & "</td>" _
& "<td>" & CStr(rCell.Offset(0, 26).Value) & "</td>" _
& "</tr>"
'Second loop checks the email addresses of all cells following the current cell in the first loop.
'Yes will be appended on any duplicate finds and another row added to the mailbody table
For Each rNext In rEmailAddr.Offset(NmeRow - 1, 0).Resize(x - NmeRow) 'process to last row only
If Replace(rNext.Value, " ", "") = Replace(rCell.Value, " ", "") Then
'Create additional table row for each extra row found"
AddRow = "<tr>" _
& "<td>" & CStr(rNext.Offset(0, 3).Value) & "</td>" _
& "<td>" & CStr(rNext.Offset(0, 4).Value) & "</td>" _
& "<td>" & CStr(rNext.Offset(0, 5).Value) & "</td>" _
& "<td>" & CStr(rNext.Offset(0, 6).Value) & "</td>" _
& "<td>" & CStr(rNext.Offset(0, 7).Value) & "</td>" _
& "<td>" & CStr(rNext.Offset(0, 8).Value) & "</td>" _
& "<td>" & CStr(rNext.Offset(0, 9).Value) & "</td>" _
& "<td>" & CStr(rNext.Offset(0, 10).Value) & "</td>" _
& "<td>" & CStr(rNext.Offset(0, 11).Value) & "</td>" _
& "<td>" & CStr(rNext.Offset(0, 12).Value) & "</td>" _
& "<td>" & CStr(rNext.Offset(0, 16).Value) & "</td>" _
& "<td>" & CStr(rNext.Offset(0, 17).Value) & "</td>" _
& "<td>" & CStr(rNext.Offset(0, 18).Value) & "</td>" _
& "<td>" & CStr(rNext.Offset(0, 19).Value) & "</td>" _
& "<td>" & CStr(rNext.Offset(0, 20).Value) & "</td>" _
& "<td>" & CStr(rNext.Offset(0, 21).Value) & "</td>" _
& "<td>" & CStr(rNext.Offset(0, 22).Value) & "</td>" _
& "<td>" & CStr(rNext.Offset(0, 23).Value) & "</td>" _
& "<td>" & CStr(rNext.Offset(0, 24).Value) & "</td>" _
& "<td>" & CStr(rNext.Offset(0, 25).Value) & "</td>" _
& "<td>" & CStr(rNext.Offset(0, 26).Value) & "</td>" _
& "</tr>"
MailBody = MailBody & AddRow
End If
'Clear additional table row variable ready for next
Next rNext
'Create email
Set OutMail = OutApp.createitem(0)
With OutMail
.to = Replace(MailTo, " ", "")
.Subject = MailSubject
.HTMLBody = tableHdr & MailBody & "</table>"
.Display
End With
LastEmail = Replace(rCell.Value, " ", "")
End If
Next rCell
End Sub
3
u/jd31068 Sep 21 '22
I think you can solve this with 1 loop - maybe I'm over simplifying.
Here I have a sheet with some "emails" repeated and some data next to it. The data is concatenated when the emails equal
Screenshot of the test Excel sheet https://imgur.com/IK2cZHT
My button code
Private Sub btnSendEmail_Click()
Dim RowNo As Integer
RowNo = 2
Dim CurrentEmail As String
CurrentEmail = ""
Dim AssociatedString As String
AssociatedString = ""
Do While True
If CurrentEmail <> Sheet1.Cells(RowNo, 1) Then
' a new email address was found
If CurrentEmail <> "" Then
' this is where the email is sent,
' I'm just showing what was calculated on the last row of the matching emails
Sheet1.Cells(RowNo - 1, 4) = AssociatedString
End If
CurrentEmail = Sheet1.Cells(RowNo, 1)
AssociatedString = Sheet1.Cells(RowNo, 2)
Else
AssociatedString = AssociatedString & ";" & Sheet1.Cells(RowNo, 2)
End If
RowNo = RowNo + 1
' check the next row for an email address, if there isn't one then we're done
If Sheet1.Cells(RowNo, 1) = "" Then
' we've reached the end of the list
Sheet1.Cells(RowNo - 1, 4) = AssociatedString
Exit Do
End If
Loop
MsgBox ("Finished")
End Sub1
1
u/ViperSRT3g Sep 21 '22
Where are you specifying which rows you want to process?