Email different emails different cell ranges

[andrewb90]

Hello All,

I was wondering if it was possible to email a cell range to a specific email and then a different cell range to a different email?
I already use CDO to email a pdf to an email list, but this is much different and I'm not really sure if it is doable.

A few details:
The emails would be K6:L42. Each person has up to 2 emails. There can and will be blanks.
The cell range that needs to get emailed is a little trickier. All the emails need the range Print!D1:AB4, however each row on the email list would get a row just for them K6:L6 would get Print!D6:AB6, and so on so forth down the sheet.

I hope this all makes sense and that somebody can give some insight on how to make this work.

 

[andrewb90]

Ok, so I put that code in...

Was that only meant to give me a msgbox example of what the message would look like? Because there was no email. I also can't tell if that would give a different result to each email address (per row)

It did look very odd in the msgbox. I am just trying to figure out how to send a different range of values to each email address, once I get that down, I'll figure out how to change my sheet to give the results I want. Most likely I anticipate creating a new sheet with the particular data that needs to be copied. That's simple enough, I just need to be able to have each email get a different cell range.

 

[humdingaling]

yes the msgbox was for display only

i wasnt sure if you wanted individual rows added in all the whole range as your sTo was consolidated...doesnt make sense to add individual lines

i really dont know how your print sheet looks like or even what type of data it is
or if formatting exists which needs to be translated
if so...you probably need to go down the HTML route which is a different set of syntax

if you are going to seperate sTo then it would make sense to define each range for each email

to get a new email for each cell

make the whole within the C in R loop

For Each c In r
    With c
      If InStr(.Value2, "@") <> 0 Then sTo = sTo & "," & .Value2
    End With
  Next c

 

[humdingaling]

forgot to re-add your GMAIL line in post #10

    If Sheets("Settings").Range("c12").Value = "test" Then
        strbody = vbNewLine & "Here is the upcoming schedule, updated as of " & Now & vbNewLine & "Regards," & vbNewLine & "Schedule Master Zax" & vbNewLine & "" & vbNewLine & "" & vbNewLine & "Please do not respond to this message, replies will be fed to the chickens."
    Else
        strbody = vbNewLine & "Here's the upcoming schedule and a special message from " & Sheets("Custom").Range("C4").Value & vbNewLine & "Regards," & vbNewLine & "Schedule Master Zax" & vbNewLine & "" & vbNewLine & Sheets("Custom").Range("C5").Value & vbNewLine & "- " & Sheets("Custom").Range("C4") & vbNewLine & "" & vbNewLine & "Please do not respond to this message, replies will be fed to the chickens."
    End If
    
    i = Application.WorksheetFunction.CountA(Worksheets("Employees").Range("K6:K42"))
    Set rng = Worksheets("print").Range("D6:AB" & 3 + i)
    
    
    For Each c In rng
        strPrint = strPrint & c.Value2
    
    Next
    
    Gmail "zzz@gmail.com", "1234", "", "o> " & strbody & vbNewLine & strPrint, sTo, "1@2.3", pdf