jaeremata
New Member
- Joined
- Jan 20, 2021
- Messages
- 24
- Office Version
- 2019
- 2016
- Platform
- Windows
Hi. Good day. I need some help regarding my code. It's not doing the loop, I need to send email via excel that will do the loop but it's not working. I hope someone can help me fix my code. I know I'm missing something but I don't know where is it.
VBA Code:
Sub Mail_Send()
Dim OutApp As Object
Dim OutMail As Object
On Error GoTo cleanup
Set OutApp = CreateObject("Outlook.Application")
lr = Sheets("Sheet2").Cells(1048576, "F").End(xlUp).Row
For x = 2 To lr
If Sheets("Sheet2").Range("F" & x).Value = "No" Then
vendorName = Sheets("Sheet2").Range("B" & x).Value
supplier = Sheets("Sheet2").Range("C" & x).Value
lead = Sheets("Sheet2").Range("D" & x).Value
director = Sheets("Sheet2").Range("E" & x).Value
Version = Sheets("Sheet2").Range("G" & x).Value
HTMLBody = " <!Doctype html>" & _
"<html><head></head><body><div> Dear " & vendorName & ",</div><br><div><p> As a part of standard work, it is noticed you are using version " & Version & _
" of S-QIP and it is not refreshed with latest scorecard/NC details and associated responses from you.</p><p> Your regular refresh will allow Company to collaborate with you better in improving our product quality. Request to update it and intimate your stakeholders at the earliest.</p>" & _
" <p> If applicable, you are requested to update your S-QIP ver 5.8 as per the guidelines</p><p> Please reach out to respective SQE " & lead & "@gmail.com if you have any help or support.</p><p> Thanks, <br/> SQIP project Team</p></div></body></html>"
With OutMail
.To = vendorName
.CC = lead & "@gmail.com;" & director & "@gmail.com"
.BCC = ""
.Subject = "SQIP Refresh " & vendorName
.HTMLBody = HTMLBody
.Display
'.Send
End With
Sheets("Sheet2").Range("H" & x).Value = "Sent"
End If
Sheets("Sheet2").Range("H" & x).Value = "Nothing"
Next x
End Sub
