dave3009

EFF_Santad

Jun 24th, 2021
897
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Option Explicit
  2.  
  3. Sub ProcessEmails()
  4. Dim lst As Long, i As Long
  5. Dim email As String, dealer As String
  6. Dim codeno As String, tel As String
  7.  
  8. lst = Range("A1").CurrentRegion.Rows.Count
  9.  
  10. For i = 2 To lst
  11.     email = Range("A" & i).Value
  12.     dealer = Range("B" & i).Value
  13.     codeno = Range("C" & i).Value
  14.     tel = Range("D" & i).Value
  15.     'send email
  16.    SendEmail email, dealer, codeno, tel
  17. Next i
  18. End Sub
  19.  
  20. Sub SendEmail(email As String, dealer As String, codeno As String, tel As String)
  21. Dim oApp As New Outlook.Application
  22. Dim oMail As MailItem
  23. Dim msg As String
  24.  
  25. Set oMail = oApp.CreateItem(olMailItem)
  26.  
  27. msg = msg & "<p>Dealer Name: <span style='color:red'>" & dealer & "</span><br/>"
  28. msg = msg & "Code: <span style='color:red'>" & codeno & "</span> Tel: <span style='color:red'>" & tel & "</span><br/>"
  29. msg = msg & "----------------------------------------------</p>"
  30. msg = msg & "<p>Dear sir,</p>"
  31. msg = msg & "<p>Please prepare data for this month.</p>"
  32. msg = msg & "<p>Regards</p>"
  33. msg = msg & "<p>Your Boss</p>"
  34.  
  35.  
  36. With oMail
  37.     .To = email
  38.     .Subject = "Recheck Data for " & dealer
  39.     .HTMLBody = msg
  40.     .Display 'or .Send
  41. End With
  42. End Sub
RAW Paste Data