PDA

View Full Version : Modification of VBA code



Damian40
04-23-2019, 03:15 PM
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
04-23-2019, 03:30 PM
Cross-posted here...

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