Start Free Trial

asked on

addition to macro - automatically populate column with email address

Hi

I would like to add to the macro assigned to the "import" button on the attached sheet so if will do the following...

1. Once the information from the import file has been copied and pasted into the query log the supplier name in column E will look for the appropriate email address from the "supplier email address's" worksheet and return the corresponding email addre ss from column B into column W.

Note the list of supplier email addresses list will get longer so a looping function will probably need to be added.
Master-Query-Log.xlsm

ASKER

Note: At the moment the supplier email addresses worksheet is just an example.

Once fully complete it will have a list of 200+ supplier names with a different email address for each

Why not just use a VLOOKUP formula?

Saurabh Singh Teotia

You don't have to loop..You can use a simple macro like this which will do what you are looking for...

Sub populateemails()
    Dim lrow As Long
    lrow = Cells(Cells.Rows.Count, "e").End(xlUp).Row

    If lrow > 1 Then

        Range("F2:F" & lrow).Formula = "=IFERROR(VLOOKUP(E2,'supplier email addresses'!A:B,2,0),"""")"
        Range("F2:F" & lrow).Value = Range("F2:F" & lrow).Value
    End If

End Sub

Open in new window

Saurabh...

You don't even need to use VBA to add the formula. Mike I suggest that I tidy up the actual workbook so that we can use more inbuilt Excel features without resorting to VBA for everything.

I have to go out now so I'll check your reply when I get home.

ASKER

I know I could use vlookups the reason I wanted to use VBA was simply so the vlookup formula automatically copies down when a new row is pasted using the current import macro.

However a tidy up of the workbook does sound like a good idea.

thanks

As I said, if you let me change tidy up the actual workbook the formula will copy down. In fact even without this the formula should copy down down. I'll be home after lunch and will set up the workbook and post examples for you.

Hi Mike

I've tidied up the workbook and converted the data to Tables. These have many benefits and will make coding simpler.

Take a look at you will see I've removed the buttons and added a new Tab called Query Tab on the left of the Ribbon to replace them.

I've added the formula to add the emailaddress from the list. You will see that by adding data this formula will copy down - no need for VBA for this part.

Let me know what you think.
Master-Query-Log--3-.xlsm

ASKER

Hi Roy,

Apologies its taken me a couple of days to respond. Thank-you for tidying the workbook up. I particularly like what you have done with the buttons.

First initial observations.

1. When a new query is added to the log it is not adding it as the bottom row and is leaving a space. This also causes the UID number to go back to 1. I think this is caused by the fact the vlookup formula has been copied down to one cell beneath the bottom row. However I tried deleting the formula from the cell and re importing but got the same result.

2. There is no macro assigned to the send update button. I can't seem to figure out how to do it. Could you provide me with the appropriate steps.

Thanks

I'll check this evening. I will also need to change some of the code to accommodate the changes. I was waiting to see if you liked the new set up.

ASKER

Sounds good, ah I see, sorry for jumping the gun!

Hi Mike

I've re-written the import code. It will now work with both query types providing the random query workbook name always starts with random. So now you only need one procedure to run for importing.

I haven't checked the other code. It now working with the Ribbon button but needs amending which I'll look at later

The formulas copy down as the query is imported.
Master-Query-Log--4-.xlsm

ASKER

Hi Roy,

Thanks, a few comments.

1. The macro is not removing the visual source formatting - This means cells colours and fonts etc. are being copied over to the query log. More importantly columns J L M are returning #NA instead of the correct values.

2. I have added two entries to the attached sheet. You will see that A17 has the value Q335 when it should be Q334?

3. Could you change the entry rule so that rather than the random query workbook name having to start with "random" it must start with "member" instead.

If you could address these two issues that would be great.

Thanks
Master-Query-Log--4-.xlsm

I see what is happening. I'll get back to you soon

Hi Mike

I hadn't noticed formulas in the member query file, hence #N/A

I have changed the code for removing the formats and I think it is OK now. Please check and let me know then I'll have a look at the macro that sends the list
Master-Query-Log--4-.xlsm

ASKER

Hi Roy,

Points 1 and 3 from my previous post have been resolved however point 2 is not.

Q335 21/04/2015 334

In this instance Q335 should be Q334.

ASKER

With regards to the Send update macro.

The macro assigned to the button "send supplier update" on the attached sheet works correctly and needs to be assigned to the "send update" tab button on your workbook.

Thanks alot
Mike
Master-Query-Log-V02--1-.xlsm

Sorry Mike. I fixed that part but then reverted to a previous version and forgot to change back again.

The Send Suppliers is assigned to the button but I cannot test it at the moment because I don't have outlook on this computer.
Master-Query-Log--4-.xlsm

ASKER

That's great. Everything works on the "send update" macro apart from the final stage where the rows that have been emailed should also have had column Z populated with the date the update is sent (e.g if it was sent today, todays date would be displayed).

Obviously a date would not appear in the excluded rows (e.g. EXTERNAL or RESOLVED)

Mike

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

That's spot on. I think your'e right about the filtering method. I'll test the current macro with a larger data set and if I find it's becoming laggy I'll upload a new question notifying you at the time.

Thanks again
Mike

ASKER

oh and just a quick one. How do I find the macro (i.e syntax) for the second macro?

When I click on the developer>>macro's only the "EmailIt" macro appears???

I need to change the text in the body of the email that is sent....

Mike

That's the one that I wrote and it's called from the import macro. The other's aren't appearing in the list because they are called by the Ribbon and are in effect Private.

ASKER

So how would I edit the VBA on the other?
Sorry if I'm asking a stupid question?

Sorry which one requires editing?

Developer Tab -> Visual Basic, then open Module 1. Make a backup first.

By the way there are no stupid questions, everyone needs to learn

ASKER

I need to edit the macro assigned to the "send update" button.

Module 1 would give me the code for the Import query button.

Mike

There are three procedures in Module 1, You need Sub EmailIt()
.

You can edit the others but do not remove the Callbacks.

Hi Mike

I missed your comments about filtering. If you let me have an example workbook with more data in then there's a timer procedure we can use to check the difference. I have made these comments in many posts on Excel Forums and always had positive comments back.

Hi Mike

Here's the updated version. It runs using Advanced Filter so will be much faster than looping, especially on larger datasets.

I'm having problems uploading with Google Chrome. Here's alonk to download it from

Updated File

ASKER

Hi Roy,

I haven't been able to test the macro as I am getting a compile error. I have opened a new question as I have also added some additional queries/requirements.

See link Below

https://www.experts-exchange.com/questions/28663202/amendments-to-macros.html