0

Working on a large macro to automate an end of day email process that involves sending emails to different customers. Everything is working fairly well except in the several instances that some firms have different emails for different employees. It would be simple if these employees were the only ones that would be sent to that email, but that is of course not the case.

I'm struggling to find a solution to finding the names and referencing the email. But referencing multiple names without knowing how many names it could be ahead of time (meaning in the future a firm could have 10 employees with the same email confirmation and some may have as little as 1 (for those that require separate emails per employee)). Would I use an array for this and test against the array? I would also need to store the employee names in order to ensure duplicates aren't created. See Text example and code below:

The names are stored in a sheet named emailMaster in this format say starting in cell(1,3) (Joe GoodGuy; James Johanson; Jimmy TheHat (All encapsulated in one cell)) and the email they correspond to is found at .Offset(0,1). To clarify, these gentlemen may work for the firm "CodersUnited", but their may be another group from the firm "CodersUnited", who require a different email address for their end of day receipts and they could be in cell(1,5) (Jimmy John; Franky TwoToes; Jimmy Hendrix) and their corresponding email found in .Offset(0,1).

Row   ____________________C____________________  __D___  ___________________E____________________  __F__
 1    Joe GoodGuy; James Johanson; Jimmy TheHat  Emails  Jimmy John; Frank TwoToes; Jimmy Hendrix  Email

The solution below only works if their is one name corresponding to one email. There needs to be multiple names corresponding to one email.

'Gets firm name
            If firmName = emailMaster.Cells(emrow_num, 1) Then
                continue = False
                cFirm = firmName
                iFirm = emailMaster.Cells(emrow_num, 2)

                If IsEmpty(emailMaster.Cells(emrow_num, 4)) = True Then
                    firmEmail = emailMaster.Cells(emrow_num, 3)

                'Tests for separate employee emails 
                ElseIf emailMaster.Cells(emrow_num, 4) = "Yes" Then
                    empSeparate = True

                    'Captures separate emp email
                    Set empTestFinder = emailMaster.Rows(emrow_num).Find(empName)
                    empFinder = empTestFinder.Address
                    firmEmail = emailMaster.Range(empFinder).Offset(0, 1)

                    Else
                        MsgBox ("Firm designated as different emails for employees.  Either change designation of firm or add employee.  Contact dev for more assistance.")
                        Exit Sub
                    End If
                End If
Improve this question

2 Answers 2

Reset to default
1

In my view you are mixing three tasks:

  1. modelling your data
  2. determining a convenient method of holding that data in Excel
  3. determining a convenient method of holding that data in VBA variables

You need to tackle these tasks in sequence but be willing to revert to a previous task if you encounter problems attempting the current task..

Below is a data model which I have deduced from your question. This model does not conform to any standard modelling notation but, if you are not a data modeller, I think you will find this notation easier to understand.

You need to send emails to all your customers. I have shown three example customers. At Customer1 there is only one person you send emails to who has there own email address. At Customer2, there are three people to whom you send emails who each have their own email address. Customer3 is more complicated. Person13 has their own address but the other contacts share addresses. For example, EmailAddr5 is shared by Person5, Person6 and Person7.

Customer1────EmailAddr1────Person1

Customer2─┬──EmailAddr2────Person2
          ├──EmailAddr3────Person3
          └──EmailAddr4────Person4

Customer3─┬──EmailAddr5─┬──Person5
          │             ├──Person6
          │             └──Person7
          ├──EmailAddr6─┬──Person8
          │             ├──Person9
          │             ├──Person10
          │             ├──Person11
          │             └──Person12
          └──EmailAddr7────Person13

Is this a correct representation of your data? I would summarise this as:

  • There are 1 or many PERSONs per EMAILADDR
  • There are 1 or many EMAILADDRs per CUSTOMER

If you are not familiar with modelling data, this may be confusing at first but I believe that with a little study it will become clear.

The question you need to answer is: “Is this a complete description of my data?” Only when you are convinced there are no special cases not covered by this model can you proceed to Task 2.

Unless your final model is much more complicated than mine, I do not believe you will need two or more worksheets to hold this data. So, task 2 is to map your data model onto an Excel worksheet.

During task2, the person to consider is the user who will create and maintain this data. For example, I would have thought holding multiple people in a single cell would be awkward to maintain. How does this data arrive? Does Acme Supplies tell you to contact John Smith whose email address is [email protected] or do they tell you use [email protected] to contact any of: Angela Brown, Cherry White and John Smith? If data arrives as “name – address”, the model above may be correct but inconvenient. Would this be a convenient arrangement of the data for the user to maintain?

Acme Supplies  |  Brown, Angela  |  [email protected]
Acme Supplies  |  Chester, Neal  |  [email protected]
Acme Supplies  |  Smith, John    |  [email protected]
Acme Supplies  |  White, Cherry  |  [email protected]

With this arrangement, there is a row per person per company.

If you really think your data model is correct, how about:

Acme Supplies|[email protected]|Chester, Neal|[email protected]|Brown, Angela|Smith, John|White, Cherry|

where I am using vertical lines to represent cell boundaries.

What I done is take:

Customer3─┬──EmailAddr5─┬──Person5
          │             ├──Person6
          │             └──Person7
          ├──EmailAddr6─┬──Person8
          │             ├──Person9
          │             ├──Person10
          │             ├──Person11
          │             └──Person12
          └──EmailAddr7────Person13

and arrange it as:

Customer3|EmailAddr5|Person5|Person6|Person7|EmailAddr6|Person8|Person9| and so on

This could work because every address contains the symbol “@” while no name contains this symbol.

As you develop the mapping from the data model to Excel, you may need to revise the data model. Your original model may be technically correct but implementation in Excel may be inconvenient. Provided your revised model can handle any combination of company, address and name you can envisage and providing that revised model maps cleanly to Excel you will be all right.

Lastly, task 3 is to map the Excel worksheet to VBA variables. This depends on how you need to process the data. For example, you might have a list like:

CompanyA, PersonZ
CompanyB, PersonY
CompanyB, PersonX
    :        :

With this it might be easier to leave the data in the worksheet and have code like this:

For each line of list
  Search for Person
  If not found
    Report “No such person”
  Else
    Record row on which first Person found 
    Do While True
      If Person belongs to Company Then
        Generate email
        Exit Do
      End If
      Search for next Person
      If row for Person is the row for the first Person Then
        Report “No such person”
        Exit Do
      End If
     Loop
  End If
Next

I think the above is as much as I can offer because there is too much uncertainty about your requirement. This may be enough for you to solve your own problem. If not, you need update your question clarify your requirement.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thank you for your extensive response. To clarify, one individual will never require multiple email addresses. If the firm does not require separate receipts sent to different employees than they receive it in a batch, which is a simple solution of storing the email addresses in standard form separated by semi colons in a single cell. It's when multiple employees are connected to one email address that is the problem. The program is looping through a report by firm for the day and sending receipts to specified customers. There are really only two levels of classification.
My data storing the email addresses is actually arranged very similar to your example above that you have setup. I think I am struggling with the logic of checking if the receipt is connected with any of the employees for that one email address. Perhaps I can group them in descending rows rather than increasing columns. And use a small loop to run through the employees associated with that email address.
@StormsEdge. "The program is looping through a report by firm for the day and sending receipts to specified customers." I am confused. I thought firm and customer were different names for the same thing. Are you sending a separate email to every contact? Are you sending one email to multiple recipients? Are you aware the property Recipients is a collection? You add recipients one at a time; you do not need to know in advance how many there are.
Yes, firm and customer are different names for the same thing in 99% of the cases. In the 1% of cases the firm name is merely an umbrella that groups together other groups that act under that umbrella name. So Company ABC is the "umbrella" company and then grouped under that umbrella is Company Z, Company x, and Company Y. Employees sam, mike, and frank work for Company Z, employees jimmy, jack, and james work for company x and bill, bob, and alex work for company Y. Each of those company's have a separate email address.
The way that I solved the issue was similar to what you recommended. I added a conditional that found the individual employees email and if it was the same as the other one (since each company under the umbrella really only has only one email for receipts) than it added their receipts to the email. It's working. Not super eloquent, but I can refactor after it's fully developed. Thank you for your help and assistance I apologize that it's been a bit hard for me to explain my problem. It's a bit complex without being able to demonstrate.
0

Perhaps you would like a function like the below:

Function FoundEMail(groupArray As Variant, emailArray As Variant) As Boolean

    Dim PersonInGroup As Variant, PersonInEMail As Variant, PersonFound As Boolean

    For Each PersonInGroup In groupArray
        PersonFound = False
        For Each PersonInEMail In emailArray
            PersonFound = StrConv(Trim(PersonInGroup), vbUpperCase) = StrConv(Trim(PersonInEMail), vbUpperCase)
            If PersonFound Then Exit For
        Next PersonInEMail
        If Not PersonFound Then Exit Function
    Next PersonInGroup
    FoundEMail = True

End Function

So instead of If firmName = emailMaster.Cells(emrow_num, 1) Then you might have something like If FoundEMail(Split(firmName,";"),Split(emailMaster.Cells(emrow_num, 1),";")) Then

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.