1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: VBA email excel workbook based on cell values using; If Then ElseIf Please he

Discussion in 'Business Applications' started by mikey0o, Sep 18, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. mikey0o

    mikey0o Thread Starter

    Joined:
    Sep 18, 2009
    Messages:
    9
    I need a code that will allow the workbook to be emailed when Column A is populated by certian numbers. The numbers in column A corespond to particular email addreses. This is the code I've been working but it isn't functional.

    Sub Email_Out()
    If Worksheets("Sheet1").Range("A5:A200") = "190030001" Then
    ActiveWorkbook.SendMail Recipients:=("[email protected]")
    ElseIf Worksheets("Sheet1").Range("A5:A200") = "190450025" Then
    ActiveWorkbook.SendMail Recipients:=("[email protected]")
    End If
    End Sub

    All help is greatly appreciated!
    Mikey
     
  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    What is going to trigger the code? Are you going to run the macro manually or do you want the code to be fired automatically by an event such as a cell change? Will all cells in column A contain the same value or will there be different values throughout the column? More details please.

    Regards,
    Rollin
     
  3. mikey0o

    mikey0o Thread Starter

    Joined:
    Sep 18, 2009
    Messages:
    9
    I will run the macro manually.
     
  4. mikey0o

    mikey0o Thread Starter

    Joined:
    Sep 18, 2009
    Messages:
    9
    There will be different values through out column A. Each number represents a department, and the email will need to be sent to the manager of that dept.
    Thanks for helping me with this!
     
  5. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Is is possible to upload a sample workbook so we can see how your data is arranged? I am a bit confused by what you are trying to do. As I understand it right now you want to be able to query for a value(s) in Column A and if it exists in there you want to email the entire workbook to a particular manager? Does that not mean that each manager that receives an email will be getting all the data in the sheet instead of just the data that pertains to his department? Wouldn't you want to filter the rows to each specific department and then email returned records for that department only?

    Regards,
    Rollin
     
  6. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Instead of managing all of these numbers via code, can you perhaps create a table with two columns: one for housing your numbers, the other for housing the respective email address?

    Also, please post all of your code thus far. What versions are you using? Is this Excel and Outlook?
     
  7. mikey0o

    mikey0o Thread Starter

    Joined:
    Sep 18, 2009
    Messages:
    9
    [​IMG]Rollin, your understanding it correctly and the info isn't sensitve so I don't care that other departments see it.

    Zack, I have created a two column table and placed VLOOKUP in column F that populates the coresponding email address to the number placed in column A. I just can't get my VB code to grab those email address when I run the email out macro.

    I don't care if it works using the usintg VLOOKUP from the table, or if the VB code will recongnize the number in column A as representing a particular email address. The code I pasted above doesn't use the VLOOKUP.

    Thanks for the help!!
     
  8. mikey0o

    mikey0o Thread Starter

    Joined:
    Sep 18, 2009
    Messages:
    9
  9. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Well I'm not sure if this is going to help or not, details are a little sparse, but perhaps...
    Code:
    Sub Email_Out()
        Dim rEmail As Range, c As Range, vFind As Variant, sSubject
        sSubject = "Your subject here"
        On Error Resume Next 'for the Vlookup()
        For Each c In Worksheets("Sheet1").Range("A5:A200").Cells
            If Len(c.Value) <> 0 Then
                vFind = WorksheetFunction.VLookup(c.Value, Worksheets("Sheet2").Range("A:B"), 2, 0)
                If IsEmpty(vFind) = False Then
                    ActiveWorkbook.SendMail vFind, sSubject
                Else
                    ActiveWorkbook.SendMail "[email protected]", sSubject
                End If
                vFind = Nothing
            End If
        Next c
    End Sub
     
  10. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Zack,

    Your method will send duplicate emails since the values in column A do not appear to be unique according to the screenshots. Now we both know that you are smart enough to overcome this limitation but for others who may be reading this thread and don't know how to handle this I would recommend doing one of two things:

    The easiest way to handle this is to add another column to your lookup table on Sheet2 and use this column to flag the record as having been previously used. Before the macro emails the workbook it would simply need to check for a value in this column to determine if the email was already sent or not.

    The other way of handling this situation would be to put the column A values into a dynamic array after the workbook has been emailed. You would then add logic to your code to loop through the array to see if the value exists or not. If the value is not found in the array then you would email the workbook. If the value was found in the array it would mean that the email was sent and you could skip to the next value.

    Regards,
    Rollin
     
  11. mikey0o

    mikey0o Thread Starter

    Joined:
    Sep 18, 2009
    Messages:
    9
    Zack,

    Your code works great! Thanks for you all your help!
    The only snag is that for each email sent out, I the Allow/Deny box pops up. Do you know how I can avoid this?

    Rollin,

    I am avoiding duplicate emails by using a pivot table to populate column A on sheet 1, thanks for your input on that and your help as well!

    Greatly appreciative,
    Michael
     
  12. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
  13. mikey0o

    mikey0o Thread Starter

    Joined:
    Sep 18, 2009
    Messages:
    9
    Rollin,

    I am using Outlook 2007. The pic on the link you posted is the exact allow/deny box I am getting. I just can't find the option in outlook to turn it off. Thanks for the help!
     
  14. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Read all of the link. Ron de Bruin has spent a lot of time working with Excel and sending via Outlook. Scroll down a bit below the picture and take a look at either the Express ClickYes or Outlook Redemption.
     
  15. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Rollin, I must apologize, I hadn't actually looked at the values that closely. I personally would have used other code, but I like having full control of my code, and be able to customize it any way I want. :cool:
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/861934

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice