Array of Email Addresses

[DragonWood]

Greetings,


I have created a workbook that, among other things, will send an email to other engineers in the region to request equipment needed for an upcoming job. Most of it works, except the Array.


I have a page in the workbook called "Lists". On this page is a column that contains the names, email addresses, and phone numbers of the other engineers in the region. However, not all regions have 20 engineers, so sometimes the list will not be complete.


The problem is two-fold:

First, how do you account for the blank cells in the array?


Second, how do I actually copy the data into the RegionalEmail variable?


Here is my code so far.

Sub EquipmentRequestEmail()
'Sends an email to all regional Field Engineers to request equipment.


    Dim CellsToCheck As Variant
    Dim cll As Variant
    Dim RegionalEmail As String
    Dim strSubject As String
    Dim strBody As String
    Dim objApp As Object
    Dim objMail As Object


    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With


    With ActiveWorkbook.Worksheets("Lists")
    
    CellsToCheck = Array("J22", "J26", "J30", "J34", "J38", "J42", "J46", "J50", "J54", "J58", "J62", "J66", "J70", "J74", "J78", "J82", "J86", "J90", "J94")


    For Each cll In CellsToCheck
        With .Range(cll)
            .Copy
        End With


    Next cll


    End With


    RegionalEmail = CellsToCheck
    
    strSubject = "Equipment Request"


    Set objApp = CreateObject("Outlook.Application")
    Set objMail = objApp.CreateItem(0)
    
    On Error Resume Next
    With objMail
        .To = RegionalEmail
        .Subject = strSubject
        .Body = "Guys," & vbNewLine & vbNewLine & vbTab & "I need a (Insert Equipment Here) for next week please." & vbNewLine & vbNewLine
        .Display
    End With
    On Error GoTo 0
    
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With




    Set objMail = Nothing
    Set objApp = Nothing


End Sub

Thanks in advance.

[mancubus]

post your wrkbook pls. so we can see the info in cells and table structure.

[DragonWood]

My workbook is too large to upload.

The info in the cells will be email addresses.

The table structure is simply column J, every 4 rows, the actual numbers are in the array sample I posted.

I know the array looks at the correct data. What I don't know is how to make it use that data to create the "To" email address and to ignore any cells that are blank.

[DragonWood]

Does anyone have any ideas on this?

I'm thinking the output from the array could simply be the list of email addresses (i.e. email1@email.com, email2@email.com, email3@email.com, etc.). But when the array finds a cell that is blank, it stops and simply makes the list from the addresses it found. That way there are not a bunch of blanks with commas after the last email address.

I just don't know how to make that happen.

Thanks.

[Paul_Hossler]

Post a workbook with everything that is not needed to show the problem removed: sheets, columns, etc.

If the array contains data from blank cells, then I'd say it is NOT looking at the correct data

[snb]

Never use copied VBA Code you don't fully understand.

This is all you need:

Sub M_snb()
  with CreateObject("Outlook.Application").CreateItem(0)
    .To = join(filter([transpose(Lists!J22:J94)],"@"),",")
    .Subject = "Equipment Request"
    .Body = replace("Guys,##" & vbTab & "I need a (Insert Equipment Here) for next week please.##","#",vblf)
    .Send
  End With
End Sub

[DragonWood]

I finally was able to try this out and it worked great. I did make a few minor changes so naming conventions and such matched the rest of my code. However, the process is exactly what I needed.

Thank you.

[snb]

Naming conventions in a code that doesn't contain any variables ?

[DragonWood]

I know you didn't put variables in here, but I modified it because I'll be using the same concept in other parts of my workbook (and possibly other workbooks) and I do use variables. Basically, I attach this part to a variable:

join(filter([transpose(Lists!J22:J94)],"@"),",")

That way I can include two separate lists of email addresses. One that will be just to the other engineers, and one that will include the office personnel.

It still works beautifully, thanks again.