Modification of VBA code
Results 1 to 9 of 9

Thread: Modification of VBA code
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2014
    Posts
    178
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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:
    <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.

  2. #2
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,978
    Post Thanks / Like
    Mentioned
    91 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Modification of VBA code

    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,638
    Post Thanks / Like
    Mentioned
    445 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Modification of VBA code

    @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.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    Board Regular
    Join Date
    Jun 2014
    Posts
    178
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Modification of VBA code

    Quote Originally Posted by Fluff View Post
    @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 by Damian37; Apr 24th, 2019 at 11:35 AM.

  5. #5
    Board Regular
    Join Date
    Jun 2014
    Posts
    178
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Modification of VBA code

    Quote Originally Posted by Rick Rothstein View Post
    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.

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,638
    Post Thanks / Like
    Mentioned
    445 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Modification of VBA code

    Which link are you referring to?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,978
    Post Thanks / Like
    Mentioned
    91 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Modification of VBA code

    Quote Originally Posted by Damian37 View Post
    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 by Rick Rothstein; Apr 24th, 2019 at 01:29 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  8. #8
    Board Regular
    Join Date
    Jun 2014
    Posts
    178
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Modification of VBA code

    Quote Originally Posted by Rick Rothstein View Post
    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.

  9. #9
    Board Regular
    Join Date
    Jun 2014
    Posts
    178
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Modification of VBA code

    Quote Originally Posted by Fluff View Post
    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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