Modification of VBA code

[Damian40]

Hello everyone,
I have some VBA code written that reviews a worksheet looking for a specific value, and any rows in which this condition is met, an auto e-mail is generated and sent to e-mail addresses listed within 2 columns. I would like to modify the code to review worksheet to identify specific store numbers that have information pertaining to them within the worksheet, and then generate an auto e-mail listing the references within worksheet and have the e-mail addressed to point of contact for that store. The current code I have is:

 Sub Send_Email()

  Dim OutLookApp As Object
  Dim OutLookMailItem As Object
  Dim iCounter As Integer
  Dim MailDest As String
  Dim MailDest2 As String

  Set OutLookApp = CreateObject("Outlook.application")
  Set OutLookMailItem = OutLookApp.CreateItem(0)

  Worksheets("Agreements").Activate
 For iCounter = 2 To WorksheetFunction.CountA(Columns(29))

     MailDest = ""
      If Len(Cells(iCounter, 28).Offset(0, -1)) > 0 Then
If MailDest = "" And Cells(iCounter, 28).Offset(0, -1) = "W/IN 30 DAYS" Then

     Set OutLookMailItem = OutLookApp.CreateItem(0)
     With OutLookMailItem

     MailDest = Cells(iCounter, 28).Value
     MailDest2 = Cells(iCounter, 29).Value

        .To = MailDest
        .CC = MailDest2
        .Subject = "Blah Blah Blah"
        .HTMLBody = "To Whom It May Concern,"

I would like to modify the bolded line to look for specific store number, and then associate that number with specific point of contact. Any help would be greatly appreciated. Thank you very much!

D.

[rothstein]

Cross-posted here...

https://www.mrexcel.com/forum/excel-...-vba-code.html