CODEWITHSUNDEEP
excelvbaemail
James Parker
James Parker

Reputation: 3

Get all non blank values from column and convert into email list

I am trying to create a VBA code that sends an email to a list of email addresses which exist in column E. My emails in column E appear like so:

[email protected]
[email protected]
<Blank Cell>
[email protected]
<Blank Cell>
[email protected]

Here is my code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("M2")) Is Nothing And Range("M2").Value = "Send Prospect Alerts" Then

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

    strbody = "This is an email"

    On Error Resume Next


    With OutMail
        .To = Range("E6:E100").Value
        .CC = ""
        .BCC = ""
        .Subject = "Sales Connect Tracker: A Task has been completed"
        .HTMLBody = strbody            'You can add a file like this
        .Send 'displays outlook email
        'Application.SendKeys "%s" 'presses send as a send key
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing

MsgBox "Email Sent"

End If
End Sub

At the moment I am trying to reference my range E6:E100 in the email TO field, but this doesn't work and no email is sent.

I can only assume this is because the email addresses are displaying like so:

 [email protected]@email.com<Blank Cell>[email protected]<Blank Cell>[email protected]

Where as outlook requires the emails to be separated like:

[email protected]; [email protected]; <Blank Cell>; [email protected]; <Blank Cell>; [email protected]

Also as you may notice, there are some blank cells in between the email addresses, I'm not sure whether or not this would be considered an issue or not as if no email address exists then it should simply just ignore the blank cells.

So my question is basically how can I get this to work and get my email sent to all the email addresses in my column E?

Please can someone show me where I am going wrong, thanks

Upvotes: 0

Views: 139

Answers (1)

Dirk Reichel
Dirk Reichel

Reputation: 7979

When entering a range or an array, it simply doesn't know what to do... but it will try to set the .To to it. While .To only accepts a string.

To get this:

[email protected]; [email protected]; [email protected]; [email protected]

try this:

Function getMailList(rng As Range) As String
  Dim str As String, i As Variant
  For Each i In rng
    If Len(i.Value) > 0 Then If Len(str) = 0 Then str = i Else str = str & "; " & i
  Next
  getMailList = str
End Function

and change

.To = Range("E6:E100").Value

to

.To = getMailList(Range("E6:E100"))

For Each ... in ... simply returns every item in the set range
If the length of the item is not 0 (could check for isempty, but it returns false for "") Then If your output (str) is empty Then set it to the item (i) Else add ", " and the item to it

Edit: Ah lol... just copy/paste-error... didn't delete the <Blank Cell> in the first line :P

Upvotes: 1

Related Questions