E-mailing Sorted data to various people

[Robert110]

Hi,
I have a table of 213 rows (with may change in the future)

Every row needs to be grouped together depending on a value in column G. (Which I can do using the Sort function)

However, the problem is, once the grouping is done, I then need to e-mail the data to various people using outlook.

For example
IF Column G = A (e-mail to Paul); IF Column G =B (e-mail to Gemma) etc.

There can be over 100 different types of values in Column G, so I guess I would need to use a v-look up table. But I don’t have much experience with V-look up and I am not too sure how I can make it so that only each value gets e-mailed to the right person.
I also can’t let person who gets value A information get value B information either.

If anyone could help me, it would be very much appreciated
I hope I have explained this ok, but if you need me to explain further let me know, I can also attached a copy of the spreadsheet if needed.
Thanks in advance
Rob

[ragulduy]

You could try something like this:

Sub macro_1()

Dim outApp, outMail, count, Mail_Address

Set outApp = CreateObject("Outlook.Application")
count = 1
Do Until Range("G" & count) = ""
    If Range("G" & count) = "A" Then
        Mail_Address = "Paul@Test.com"
    ElseIf Range("G" & count) = "B" Then
        Mail_Address = "Gemma@Test.com"
    ElseIf Range("G" & count) = "C" Then
        Mail_Address = "Rob@Test.com"
    End If
    Set outMail = outApp.createitem(0)
    With outMail
        .to = Mail_Address
        .Subject = "Subject"
        .body = Range("A" & count)
        .display
    End With
Loop

End Sub

If you wanted to use a lookup table then change the if statements to:

Mail_Address = worksheetfunction.vlookup(Range("G" & count),Range("H1:I25"),2)

Where H1:I25 is your lookup range, with column H containing the possible values of column G and column I containing the matching email addresses.

It's not clear exactly what information you want to send to each person so this is based on sending whatever is in column A. you need to change the .body to whatever it should actually be.

[Robert110]

Hi yudlugar,

First of all thank you very much for the very quick response.
I have been trying the different ways and the one using a look up would suit my needs the best, however, I am having a little trouble with it.
When I run it I get "Unable to Get the Vlookup property of the WorksheetFunction class" at the " Mail_Address = WorksheetFunction.VLookup(Range("G" & count), Range("Email"), 2)" and it can't continue on, I have tried changing the vlook up range, but still I get the error.
Any ideas on what I am doing wrong? The code is below

Thanks
Rob

Sub macro_1()

Dim outApp, outMail, count, Mail_Address

Set outApp = CreateObject("Outlook.Application")
count = 1
Do Until Range("G" & count) = ""

   Mail_Address = WorksheetFunction.VLookup(Range("G" & count), Range("Email"), 2)
   
    Set outMail = outApp.createitem(0)
    With outMail
        .to = Mail_Address
        .Subject = "Report"
        .body = Range("A" & "F")
        .display
    End With
Loop

End Sub

[ragulduy]

There is nothing wrong with how you are using vlookup in the code so the problem is in the data you are using the lookup on. There could be a few reasons that error could be brought up:

-Range("G" & count) does not exist in Range("Email")
-Range("Email") has only one column
-Range("G" & count) is not in the first column of Range("Email")

First thing to try is to put the vlookup on the worksheet and check that you get an answer. If you upload a worksheet with some sample data that still gives you the error I can take a look.

[Robert110]

Ok, thanks I changed it slightly to include column G,

 IMail_Address = WorksheetFunction.VLookup(Range("G" & count), Range("G1:I25"), 2)

But when it makes the e-mail it doesn't put the e-mail address in (the subject works ok) and the body text is only cell A1.

I know I didn't make it clear in my first e-mail but I would like all rows (column A to F) with e.g. "775" in column G to be sent in the body text.
then in a seperate mail all rows (column A to F) with e.g. "789" in column G to be sent in the body text.

I have attached a file of how it look at the moment incase I am not explaining my self very clearly

Email.jpg

And a file of an exapmle of the information that I would like to send "Paul@Test.com in another file (rows with "775" in column G)

email after sort.jpg

Thanks for all your help
Rob

[ragulduy]

Are you using IMail_Address in your code for the vlookup and Mail_Address when assinging the email to the .to property? That will be causing a problem.

Also, you don't increment the count in your code (you need something like count = count+1 at the end of the loop) so it will presumably get stuck in an endless loop.

As I mentioned in post 2 you need to change .body to be what you actually want to send. To send columns A to F it would be something like:

.body = Range("A" & count) & Range("B" & count) & Range("C" & count) 'etc up to F

To send all rows with a specific value in a single e-mail you will need to change the structure of your loop. As you move down column G the process would be:
-Find if value has already been sent (check values in rows above)
-If it is first occurance, loop through to bottom row to find all the other rows with same column G value

[Robert110]

Hi yudlugar,

I must have made a mistake when copying the script as I did mean to have IMail in the code.

I have been trying to figure this out, but I seem to get a bit stuck.

If you wouldn't mind I have attached a worksheet with some sample data in it. I just can't get it to work.

Thanks
Rob

[ragulduy]

You need:

Sub macro_1()
Dim outApp, outMail, count, IMail_Address
Set outApp = CreateObject("Outlook.Application")
count = 2
Do Until Range("G" & count) = ""
    IMail_Address = WorksheetFunction.VLookup(Range("G" & count), Range("H1:I3"), 2)
    Set outMail = outApp.createitem(0)
    With outMail
        .to = IMail_Address
        .Subject = "Report"
        .body = Range("A2")
        .display
    End With
    count = count+1
Loop
End Sub

That will get the correct e-mail adresses in your sample. You were looking at columns G to I in your code whereas the lookup table was only in H and I.

I don't know how to get the body as you want it.

[Robert110]

Wow Yudlugar,

That is great thank you very much.

The body text is causing me a problem is though.
I needed the data to be filtered by the figure in column G
Some examples would be:
Any row with 775 in it, would be e-mailed to Paul@test.com
Any row with 789 in it, would be e-mailed to Gemmal@test.com
Any row with 803 in it, would be e-mailed to Robert@test.com
and so no for all numbers in column G
I have attached a spreadsheet for example of how they would be filtered, and also an e-mail example of how it could look.

Sorry for not being clear in the first place.
And thank you so much for your time and help with this
Rob
E-mail example.jpg

[ragulduy]

I understood what you wanted, I just don't know how to do it.
Try looking here:
http://www.rondebruin.nl/mail/folder3/mail4.htm

[Robert110]

Ah ok.

Thank you so much for your help and the quick response everytime:-)

Rob