If name not found msgbox states the name which is not found

[Patriot2879]

Hi All happy Friday, I hope you can help me please - i have a snippet of code below where the Range is A2 to last row and under 'Email Links row V to find the matching name. This works great, but i would like to add a message box that if the name is not found in A2 to last row then a message box will pop up stating 'Jo Bloggs' not found! - for example, then asks a yes or no if i want to continue and it will skip that name not found. Can you help please and i hope this makes sense.

   Dim Cl As Range
   For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
      Cl.Value = Trim(Cl.Value)
   Next Cl
  
Dim lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
With Range("I2:I" & lr)
    .Formula = "=XLOOKUP(A2,'Email Links'!A:A,'Email Links'!V:V)"
    .Value = .Value
End With

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshots (not pictures) of your sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Patriot2879]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshots (not pictures) of your sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

Hi good morning, please see the link below for the file i added to drop box.

https://www.dropbox.com/s/y8lyjrhyretxuh5/Test%20Weekly%20Work%20Issue%20Template.xlsm?dl=0

There are 2 sheets with this 'Sheet1' and Email Links'.

On Sheet1 you will notice that i have marked some names up in red thats because they dont match the names in email links, so when you click on the button 'Email All' you will get an error as the name is not found. for example - Steve Bloggs is Stephen Bloggs. so what i want to add to the 'Email All' button is some kind of msgbox, that when this occurs it will say that Steve Bloggs not found do you want to continue yes, no or abort? and if you click on yes it will move on to the next name and so on.

Hope this makes sense?

 

[Patriot2879]

Hi good morning, please see the link below for the file i added to drop box.

Test Weekly Work Issue Template.xlsm

There are 2 sheets with this 'Sheet1' and Email Links'.

On Sheet1 you will notice that i have marked some names up in red thats because they dont match the names in email links, so when you click on the button 'Email All' you will get an error as the name is not found. for example - Steve Bloggs is Stephen Bloggs. so what i want to add to the 'Email All' button is some kind of msgbox, that when this occurs it will say that Steve Bloggs not found do you want to continue yes, no or abort? and if you click on yes it will move on to the next name and so on.

Hope this makes sense?

 

[Patriot2879]

Hi good afternoon all, hope you can help me please with my issue, your help is greatly appreciated.

 

[mumps]

Your code is a little confusing for me. Do you want to create an email for each name in column A of Sheet1 and include in the email all the rows of data associated with that name? Please explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data.

 

[Patriot2879]

Hi that is correct each name sends an email from column a sheet1, If there is several names the same it will only send the one email, but if a name is spelt wrong and not found in column a of email links it will come up as an error

 

[mumps]

Which column in the "Email Links" sheet contains the email addresses. Do you want to include only columns A to G in the email body?

 

[Patriot2879]

Which column in the "Email Links" sheet contains the email addresses. Do you want to include only columns A to G in the email body?

Which column in the "Email Links" sheet contains the email addresses. Do you want to include only columns A to G in the email body? Hi in sheet1 column A the name must match the same as in email links column A, and yes only columns a - g in the email

 

[mumps]

Which column in the "Email Links" sheet contains the email addresses?