Results 1 to 2 of 2

Thread: Modification of VBA code

  1. #1
    VBAX Regular
    Jul 2016
    Greenwood Village, CO

    Modification of VBA code

    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)
     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!


  2. #2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts