+ Reply to Thread
Results 1 to 5 of 5

pull email address from a column based on criteria from another column

Hybrid View

cinstanl pull email address from a... 09-06-2016, 04:22 PM
daffodil11 Re: pull email address from a... 09-06-2016, 05:10 PM
cinstanl Re: pull email address from a... 09-07-2016, 09:00 AM
cinstanl Re: pull email address from a... 09-07-2016, 10:13 AM
cinstanl Re: pull email address from a... 09-09-2016, 08:09 AM
  1. #1
    Registered User
    Join Date
    05-25-2016
    Location
    Erlanger, KY
    MS-Off Ver
    2010
    Posts
    81

    pull email address from a column based on criteria from another column

    I need to add a cc column to the email code that I have. I have an email that, once the button ("Send Return Due Date Email") is pushed it looks for anything with current date and sends an email to a specific person, but I need to be able to copy the technician in on the email. The technician names change based upon who has the part at that time. The code to send the email is: I'm not sure what to add to the Macro. I have also attached the file. The button for the email is on the Tools page as well as who the technician is (column I) and the email addresses are on the Indexes page in Columns AF:AG.

    Sub Mail_small_Text_Outlook()
    
        Dim OutApp As Object
        Dim OutMail As Object
        Dim strbody As String
       
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
    
        ' -- CREATE BODY OF EMAIL PORTION START -----------------------
        
        strbody = "The Items listed below are expected to be returned to inventory today , " & Date & vbNewLine & vbNewLine
        
        Dim cell As Range
        Dim numRows As Integer
        
        ' Find out number of rows:
        numRows = Range("B6", Range("B6").End(xlDown)).Rows.Count
        
        ' Check each cell in Return Due Date and see if it matches today
        For Each cell In Range("M6:M" & numRows)
            
            If cell.Value = Date Then
            
                strbody = strbody & cell.Offset(0, -11).Value & vbNewLine
            
            End If
        
        Next
                  
        ' -- CREATE BODY OF EMAIL PORTION END ------------------------
    
        On Error Resume Next
       
        
        With OutMail
            .to = "cinstanl@hotmail.com"
            .cc = 
            .Subject = "Inventory Due Today"
            .Body = strbody
            .Display
        End With
        
        On Error GoTo 0
    
        Set OutMail = Nothing
        Set OutApp = Nothing
        
        ' Update date on sheet:
        shtInventoryList.Range("K3").Value = Date & " at " & Time
        
    End Sub
    Attached Files Attached Files

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: pull email address from a column based on criteria from another column

    Your logic is sound so far.

    After you identify if cell.Value = Date, you just need a catch to identify the email address.

    Uncomment your ccTo, and then check if it's empty.

            If cell.Value = Date Then
                If ccTo = "" Then
                    ccTo = _
                        WorksheetFunction.Index(Sheets("Indexes").Range("AG2:AG32"), _
                            WorksheetFunction.Match(cell.Offset(, -4), Sheets("Indexes").Range("AF2:AF32"), 0))
                End If
                strbody = strbody & cell.Offset(0, -11).Value & vbNewLine
            
            End If
    and then your .cc = ccTO

    You could also use the VLOOKUP as you were intending.
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Registered User
    Join Date
    05-25-2016
    Location
    Erlanger, KY
    MS-Off Ver
    2010
    Posts
    81

    Re: pull email address from a column based on criteria from another column

    Please see post below... tried to delete this post and it wouldn't give me the option.
    Last edited by cinstanl; 09-07-2016 at 10:40 AM.

  4. #4
    Registered User
    Join Date
    05-25-2016
    Location
    Erlanger, KY
    MS-Off Ver
    2010
    Posts
    81

    Re: pull email address from a column based on criteria from another column

    Ok, This worked, but I need to know how to send multiple emails. For instance. If 3 of the parts on the attachment are due today and they are for 3 different technicians, how to send 3 individual emails or make sure that all 3 technicians are cc on the emails. So that each person is only being copied on the email with the part that they are responsible for.
    Last edited by cinstanl; 09-08-2016 at 08:16 AM.

  5. #5
    Registered User
    Join Date
    05-25-2016
    Location
    Erlanger, KY
    MS-Off Ver
    2010
    Posts
    81

    Re: pull email address from a column based on criteria from another column

    I was able to figure this out. Just in case anyone ever needs it... I am adding the code to this page and closing this ticket.

    Option Base 1
    Option Explicit
    Sub Send_Email(cc As String, body As String)
    
        Dim OutApp As Object
        Dim OutMail As Object
        
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        
        On Error Resume Next
            
        With OutMail
            .To = "emailaddress@whereever.com"
            .cc = cc
            .Subject = "Inventory Due Today"
            .body = body
            .Display
        End With
        
        On Error GoTo 0
    
        Set OutMail = Nothing
        Set OutApp = Nothing
    
    End Sub
    Sub Email_CC_Body()
        
       ' Dim strbody As String
        Dim ccTo As String
        Dim cell As Range
        Dim numRows As Integer
        
        ' A 2D-array where the first column will be the assignee and the second column will be their body for the email:
        Dim Assignee_And_Body() As String
        Dim personAssigned As String
        Dim numAssignees As Integer
        Dim sizeArray As Integer
        Dim i As Integer
        numAssignees = 0
    
        ' -- CREATE BODY OF EMAIL PORTION START -----------------------
        
       ' strbody = "The Items listed below are expected to be returned to inventory today , " & Date & vbNewLine & vbNewLine
        
        ' Find out number of rows:
        numRows = Range("B6", Range("B6").End(xlDown)).Rows.Count
        
        ' Set the size of the array:
        sizeArray = Application.WorksheetFunction.CountIf(Range("M6:M" & numRows), Date)
        ReDim Preserve Assignee_And_Body(sizeArray, 2)
        
        ' Check each cell in Return Due Date and see if it matches today
        For Each cell In Range("M6:M" & numRows)
            
            If cell.Value = Date Then
                
                ' Find out the person assigned
                personAssigned = cell.Offset(0, -4).Value
                
                ' Check if personAssigned already exists in the array
                If IndexInArray(personAssigned, Assignee_And_Body) > 0 Then
           
                    ' If they already exist, add onto the body:
                    Assignee_And_Body(IndexInArray(personAssigned, Assignee_And_Body), 2) = Assignee_And_Body(IndexInArray(personAssigned, Assignee_And_Body), 2) & cell.Offset(0, -11).Value & " - " & cell.Offset(0, -10).Value & vbNewLine
                
                Else
            
                    ' Increase the number of assignees by 1
                    numAssignees = numAssignees + 1
                        
                    ' Add the new person assigned and build the initial body:
                    Assignee_And_Body(numAssignees, 1) = personAssigned
                    Assignee_And_Body(numAssignees, 2) = "The Items listed below are expected to be returned to inventory today , " & Date & vbNewLine & vbNewLine & cell.Offset(0, -11).Value & " - " & cell.Offset(0, -10).Value & vbNewLine
                    
                End If
                    
            End If
        
        Next
                  
        ' -- CREATE BODY OF EMAIL PORTION END ------------------------
        
        ' Loop through the array to send out the email:
        For i = 1 To UBound(Assignee_And_Body)
        
            If Assignee_And_Body(i, 1) <> "" Then
                
                ' Generate the CC email address
                ccTo = WorksheetFunction.index(Sheets("Indexes").Range("AG2:AG500"), WorksheetFunction.Match(Assignee_And_Body(i, 1), Sheets("Indexes").Range("AF2:AF500"), 0))
                
                ' Send the email out for that CC and the body:
                Call Send_Email(ccTo, Assignee_And_Body(i, 2))
            
            End If
            
        Next
        
        ' Update date on sheet:
        shtInventoryList.Range("K3").Value = Date & " at " & Time
        
    End Sub
    Function IndexInArray(stringToBeFound1 As String, arr As Variant) As Integer
    
        Dim x As Integer
        Dim index As Integer
        
        index = 0
        
        For x = 1 To UBound(arr)
        
            If stringToBeFound1 = arr(x, 1) Then
                index = x
                Exit For
            End If
            
        Next
        
        IndexInArray = index
    
    End Function

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Copy email address in column dependant on another column
    By N1k100 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 03-31-2015, 10:09 AM
  2. Formula to pull rows of data based on a column cell value (Criteria)
    By PaulLor89 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-30-2013, 11:26 AM
  3. [SOLVED] Extracting email chain email address and converting to Excel column
    By Leah_Hael in forum Excel General
    Replies: 4
    Last Post: 12-28-2012, 04:05 PM
  4. [SOLVED] Help with a formula to extract email address to a new column
    By Clarence Leong in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-11-2012, 04:05 AM
  5. Vlookup with email address column
    By lamp123 in forum Excel General
    Replies: 1
    Last Post: 06-11-2012, 11:54 AM
  6. [SOLVED] can I copy a column of email addresses, paste into email address?
    By Lizizfree in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 07-20-2006, 05:05 PM
  7. [SOLVED] How do you add a parentesis to a column of email address?
    By a_dallas_boy in forum Excel General
    Replies: 2
    Last Post: 02-18-2005, 10:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1