Modification of VBA code

Damian37

Board Regular
Joined
Jun 9, 2014
Messages
227
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:
<code>
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,</code><code>"

</code>
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.
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,051
Office Version
  1. 365
Platform
  1. Windows
@Damian37
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 

Damian37

Board Regular
Joined
Jun 9, 2014
Messages
227
@Damian37
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Hello,
I wasn't aware that both sites were connected, my apologies.
D.
 
Last edited:

Damian37

Board Regular
Joined
Jun 9, 2014
Messages
227

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,051
Office Version
  1. 365
Platform
  1. Windows
Which link are you referring to?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,307
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

I appreciate you calling me out on cross-posting. I wasn't aware that both sites were the same, but identifying that I have cross-posted, and not providing any helpful suggestions just seems rude.
D.
I wasn't calling you out... I was warning others that you had the same question posted elsewhere so that they could check the other forum to see if an answer they might want to develop for you had already been posted there.
 
Last edited:

Damian37

Board Regular
Joined
Jun 9, 2014
Messages
227
I wasn't calling you out... I was warning others that you had the same question posted elsewhere so that they could check the other forum to see if an answer they might want to develop for you had already been posted there.

I see. I appreciate the assistance. I apologize if I came off harsh. Just a bit frustrated by this auto-email modification. Have a great day!

D.
 

Damian37

Board Regular
Joined
Jun 9, 2014
Messages
227
Which link are you referring to?

Sorry, I edited my previous post to you. I had posted on two separate sites without realizing they were part of the same forum. The link one of the other members had posted in response to my initial ask was a link to my second post within the other forum. I posted my question within MrExcel and VBA express without realizing they were connected.
D.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,577
Messages
5,523,686
Members
409,531
Latest member
wo1f

This Week's Hot Topics

Top