cortexnotion
Board Regular
- Joined
- Jan 22, 2020
- Messages
- 150
- Office Version
- 2013
- Platform
- Windows
Hi All
I need to e-mail each e-mail address individually which is in row B. However I get a 'Next Without For error'
Could someone please check my code?
Thanks
I need to e-mail each e-mail address individually which is in row B. However I get a 'Next Without For error'
Could someone please check my code?
Thanks
VBA Code:
Dim OutApp As Object, OutMail As Object
Dim UserRng As Variant, i As Long, LW3 As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
UserRng = ThisWorkbook.Sheets(3).Range("A2:D" & LR3).value
LW3 = "WE-" & Format(LW1, "dd-mm-yyyy")
If InStr(UserRng(i, 2), "error") = 0 Then
With OutMail
.SentOnBehalfOfName = [EMAIL]test@test.com[/EMAIL]
.To = UserRng(i, 2)
.Subject = "Updates: " & LW3 & " - Unapproved User"
.HTMLBody = "<p><span style=""font-family: Calibri; font-size: 11pt; background-color: #ffff00""><b>*** This is an automated e-mail from the Reporting Tool ***</b></span></p>" &
"<p><span style=""font-family: Calibri; font-size: 11pt"">Hi " & UserRng(i, 3) & "</span></p>" & _
"<p><span style=""font-family: Calibri; font-size: 11pt""><em>For" & LW3 & "you submitted asset updates but you are currently an Unapproved User" & _
"<p><span style=""font-family: Calibri; font-size: 11pt; background-color: #00ffff"">You are welcome to continue to use the Training Environment to practice your submissions but please note these will not be actioned.</span></p>" & _
"<p><span style=""font-family: Calibri; font-size: 11pt"">Kind Regards </span></p>" & _
"<p><span style=""font-family: Calibri; font-size: 11pt; color: #000080"">Data Team</span></p>"
.Display
End With
Next
End If
End Sub