+ Reply to Thread
Results 1 to 9 of 9

Data Validation Paired with VBA Email Macro

Hybrid View

  1. 07-02-2015, 11:01 AM #1
    murp5972
    murp5972 is offline
    Registered User
    Join Date
    06-26-2015
    Location
    St.Paul, Minnesota
    MS-Off Ver
    Excel 2010
    Posts
    37

    Data Validation Paired with VBA Email Macro

    Hi,

    I've got an email macro that generates an email to a particular company from a dropdown selection. When an Email is sent, another macro button is clicked to record the date in a " Date Sent" column on another sheet for that particular company. I'm wondering if i can attach a data validation to the to Email macro that would generate a popup alert to the user when they attempt to send an email to a company if that company already has a date stamp recorded in the "Date Sent" in its respective row. Thanks in advance! Here's the email code: 
    Sub Mail_workbook_Outlook_1()
    Dim OutApp As Object
    Dim OutMail As Object

    EmailTo = Worksheets("E-mail Sheet").Range("B26")

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
        .To = Worksheets("E-mail Sheet").Range("B26")
        .CC = Worksheets("E-mail Sheet").Range("B19")
        .BCC = ""
        .Subject = "Updating Credit File - " & Worksheets("E-mail Sheet").Range("D26")
        .Body = "Dear " & Worksheets("E-mail Sheet").Range("C26") & "," & vbNewLine & vbNewLine & _
        "We are requesting the following information to bring your credit file up to date." & vbNewLine & vbNewLine & Worksheets("E-mail Sheet").Range("B2") _
        & Worksheets("E-mail Sheet").Range("C2") & vNewLine & vbNewLine & Worksheets("E-mail Sheet").Range("B3") & Worksheets("E-mail Sheet").Range("C3") _
        & vNewLine & vbNewLine & Worksheets("E-mail Sheet").Range("B4") & Worksheets("E-mail Sheet").Range("C4") & vNewLine & vbNewLine & Worksheets("E-mail Sheet").Range("B5") _
        & Worksheets("E-mail Sheet").Range("C5") & vNewLine & vbNewLine & Worksheets("E-mail Sheet").Range("B6") _
        & Worksheets("E-mail Sheet").Range("C6") & vNewLine & vbNewLine & Worksheets("E-mail Sheet").Range("B7") _
        & Worksheets("E-mail Sheet").Range("C7") & vNewLine & vbNewLine & Worksheets("E-mail Sheet").Range("B8") _
        & Worksheets("E-mail Sheet").Range("C8") & vNewLine & vbNewLine & Worksheets("E-mail Sheet").Range("B9") _
        & Worksheets("E-mail Sheet").Range("C9") & vNewLine & vbNewLine & Worksheets("E-mail Sheet").Range("B10") _
        & Worksheets("E-mail Sheet").Range("C10") & vNewLine & vbNewLine & Worksheets("E-mail Sheet").Range("B11") _
        & Worksheets("E-mail Sheet").Range("C11") & vNewLine & vbNewLine & Worksheets("E-mail Sheet").Range("B12") _
        & Worksheets("E-mail Sheet").Range("C12") & vNewLine & vbNewLine & Worksheets("E-mail Sheet").Range("B13") _
        & Worksheets("E-mail Sheet").Range("C13") & vNewLine & vbNewLine & Worksheets("E-mail Sheet").Range("B14") _
        & Worksheets("E-mail Sheet").Range("C14") & vNewLine & vbNewLine & Worksheets("E-mail Sheet").Range("B15") _
        & Worksheets("E-mail Sheet").Range("C15") & vNewLine & vbNewLine & Worksheets("E-mail Sheet").Range("B16") _
        & Worksheets("E-mail Sheet").Range("C16") & vNewLine & vbNewLine & Worksheets("E-mail Sheet").Range("B17") _
        & Worksheets("E-mail Sheet").Range("C17") & vbNewLine & vbNewLine & "If possible please provide us this information by " & Worksheets("E-mail Sheet").Range("A2") _
        & vbNewLine & vbNewLine & "If you have any questions please contact " & Worksheets("E-mail Sheet").Range("A19") & "at " & Worksheets("E-mail Sheet").Range("C19") _
        & vbNewLine & vbNewLine & "Sincerely," & vbNewLine & vbNewLine & "'Name'" & vbNewLine & vbNewLine & Worksheets("E-mail Sheet").Range("A44") & vNewLine & vbNewLine _
        & vNewLine & vbNewLine & Worksheets("E-mail Sheet").Range("A45") & vNewLine & vbNewLine & Worksheets("E-mail Sheet").Range("A46") & vNewLine & vbNewLine & Worksheets("E-mail Sheet").Range("A47")
        
        

         

        .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub
    And this is the Date Stamp Code:

    Sub Sent_Button_Click()
    Dim Found As Range
    
    Set Found = Sheets("Current Clients").Columns("C").Find(What:=Sheets("E-mail Sheet").Range("A26").Value, _
                                                   LookIn:=xlValues, _
                                                   LookAt:=xlWhole, _
                                                   SearchOrder:=xlByRows, _
                                                   SearchDirection:=xlNext, _
                                                   MatchCase:=False)
                                               
    If Not Found Is Nothing Then
        Found.Offset(, -1).Value = Date
    Else
        MsgBox "Company: " & Sheets("Sheet1").Range("A26").Value, , "No Match Found"
    End If
    Reply With QuoteRegister To Reply
  2. 07-02-2015, 10:22 PM #2
    humdingaling
    humdingaling is offline
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Data Validation Paired with VBA Email Macro

    i don't really know how your data is setup but if you want to check the a range before sending

    just insert an IF statement at the start of your mail_workbook code before you enter the first lot of SET lines

    if range(whatever date stamp is) <> ""  then 'assume its blank if its not date stamp?
msgbox ("whatever the message is")
exit sub ' not sure if you want to exit or continue at this point...if you want to continue just omit this line
end if
    sorry if its vague but without knowing anything about the sheet this is really best can do
    if you want something more inline with what you want

    Create a sample worksheet in which you can share (with santised data of course) and attach
    Last edited by humdingaling; 07-02-2015 at 10:25 PM.
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
    Reply With QuoteRegister To Reply
  3. 07-03-2015, 10:43 AM #3
    murp5972
    murp5972 is offline
    Registered User
    Join Date
    06-26-2015
    Location
    St.Paul, Minnesota
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Data Validation Paired with VBA Email Macro

    Here's the related worksheets. The "email Sheet" will be populated with the company name and email credentials, and the "Current Clients" sheet is where I record the date stamp with the two buttons on the right in the "Email Sheet". Let me know if this helps. Line Ticklers by responsibility excel.xlsm
    Reply With QuoteRegister To Reply
  4. 07-05-2015, 08:38 PM #4
    humdingaling
    humdingaling is offline
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Data Validation Paired with VBA Email Macro

    if i'm understanding what you trying to do

        Dim Found As Range
    
    EmailTo = Worksheets("E-mail Sheet").Range("B26")
    
    Set Found = Sheets("Current Clients").Columns("C").Find(What:=Sheets("E-mail Sheet").Range("A26").Value, _
        LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
                                               
    If Not Found Is Nothing Then
        If IsEmpty(Found.Offset(, -2)) = False Then
            If MsgBox(Range("A26").Value & " has already been sent email reminder on " & Found.Offset(, -2).Value & vbNewLine & "Do you want to continue?", vbYesNo) = vbNo Then
                Exit Sub ' stop macro if user selects No
            End If
        End If
    End If
    This codes does not check the date sent...only that there is a date
    i think you would need another if statement to check the date if you want say within last 10 days or something
    Attached Files Attached Files
    Last edited by humdingaling; 07-05-2015 at 08:45 PM.
    Reply With QuoteRegister To Reply
  5. 07-06-2015, 09:36 AM #5
    murp5972
    murp5972 is offline
    Registered User
    Join Date
    06-26-2015
    Location
    St.Paul, Minnesota
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Data Validation Paired with VBA Email Macro

    Thanks! It's working just fine.
    What would I have to add to the code if I wanted the message to include the "Date Recieved" information as well only if there was a value in that column. Thanks again.
    Last edited by murp5972; 07-06-2015 at 11:01 AM.
    Reply With QuoteRegister To Reply
  6. 07-06-2015, 11:28 AM #6
    humdingaling
    humdingaling is offline
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Data Validation Paired with VBA Email Macro

    add 
    Found.Offset(, -1).Value
    in the msgbox
    Reply With QuoteRegister To Reply
  7. 07-06-2015, 11:47 AM #7
    murp5972
    murp5972 is offline
    Registered User
    Join Date
    06-26-2015
    Location
    St.Paul, Minnesota
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Data Validation Paired with VBA Email Macro

    I only want a "Date Recieved" message to be included only when there is actually a date value in the recieved column. If the recieved column is blank, then I don't want that particular "Date Recieved" line inlcuded in the message. How would this be done? This is what the code looks like currently: 

     Dim Found As Range
    
    EmailTo = Worksheets("E-mail Sheet").Range("B26")
    
    Set Found = Sheets("Current Clients").Columns("C").Find(What:=Sheets("E-mail Sheet").Range("A26").Value, _
        LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
                                               
    If Not Found Is Nothing Then
        If IsEmpty(Found.Offset(, -2)) = False Then
            If MsgBox(Range("A26").Value & vbNewLine & vbNewLine & "A file request has  been sent on: " & Found.Offset(, -2).Value & vbNewLine & _
            "Files were recieved on: " & Found.Offset(, -1).Value & vbNewLine & "See Current Clients page for more information" & vbNewLine & vbNewLine _
            & "Do you want to continue?", vbYesNo) = vbNo Then
                Exit Sub ' stop macro if user selects No
            End If
        End If
    Reply With QuoteRegister To Reply
  8. 07-06-2015, 08:07 PM #8
    humdingaling
    humdingaling is offline
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Data Validation Paired with VBA Email Macro

    add another IF to check offset(,-1)

        If Not Found Is Nothing Then 
        If IsEmpty(Found.Offset(, -2)) = False Then ' check found sent is empty
            If IsEmpty(Found.Offset(, -1)) = False Then 'check if found received is empty
            
                If MsgBox(Range("A26").Value & vbNewLine & vbNewLine & "A file request has  been sent on: " & Found.Offset(, -2).Value & vbNewLine & _
                "Files were received on: " & Found.Offset(, -1).Value & vbNewLine & "See Current Clients page for more information" & vbNewLine & vbNewLine _
                & "Do you want to continue?", vbYesNo) = vbNo Then 'message with File received date
                    Exit Sub ' stop macro if user selects No
                End If
            Else
                If MsgBox(Range("A26").Value & vbNewLine & vbNewLine & "A file request has  been sent on: " & Found.Offset(, -2).Value & vbNewLine & _
                vbNewLine & "See Current Clients page for more information" & vbNewLine & vbNewLine _
                & "Do you want to continue?", vbYesNo) = vbNo Then 'message without File received date
                    Exit Sub ' stop macro if user selects No
                End If
            End If
        End If
    End If
    i dont know what you want for your message but the base is there so you should be able to fix as you like
    Last edited by humdingaling; 07-06-2015 at 08:09 PM. Reason: additional comments
    Reply With QuoteRegister To Reply
  9. 07-07-2015, 10:53 AM #9
    murp5972
    murp5972 is offline
    Registered User
    Join Date
    06-26-2015
    Location
    St.Paul, Minnesota
    MS-Off Ver
    Excel 2010
    Posts
    37

    Re: Data Validation Paired with VBA Email Macro

    Great, thanks!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Email Validation Macro
    By DrNo1 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-26-2023, 12:43 AM
  2. Data Validation Paired with VBA Email Macro
    By murp5972 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-02-2015, 11:38 AM
  3. Run formulae on paired data
    By batman98347 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-22-2015, 05:02 PM
  4. Need Vlookup/If/Macro/? to Return Multiple Paired Matches
    By odbal in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-19-2014, 05:40 PM
  5. 2003 Validation Dependencies email macro
    By andtheboat in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-19-2010, 11:55 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1