amendments to macros

That's odd because i tested it with no errors.

I'll add the columns when I get home and amend the code accordingly.

I'll also see what's happened to cause the error.

Where is the workbook?

It's a follow up to a previous question

The Asker should post the workbook so that everyone can have a chance to answer.

The workbook was posted in a previous question and I made a suggestion that he should use a Filter in his code. The question was marked, I simply posted a a different version. I suppose because Mike wanted to add columns he thought it needed a new question

The module has Option Explicit set (IMO as it should) so sSubject needs to be defined in the sub like this.

Dim sSubject As String

That will get rid of the compile error.

The emails are only displaying for test purposes. You need to change the code to .Send when you have finished testing.  I did tell you this in your original question.

I have added two other possible passwords as requested

I have added code to send using a specific email. You need to find out the Item number of the email account using the macro Which_Account_Number

I have added the new columns. You need to just check that these are correct.

I'm still having problems attaching files so here's a download link

Updated File

Hi Roy,

Initial Comments below

Macro 1
Apologies, your'e right, you did tell me about changing the code to .Send
This macro works fine.

Macro 2

1. The two new column additions are correct and are successfully sending. However columns Z and AA are also being sent. The macro should stop at Y.

2. When running the send "update macro" with the values you have in-putted and the conditions I have set, the following rows that should be sent are... 7,10,13. However in this instance row 13 is not being sent???

3. Also when I alternate the values in columns Z and AA the macro is failing and returning an error. (see attached).

4. filter is not clearing after emails are sent

Thanks Mike
macro-2-error---screenshot.docx

I'm just finishing up at work. I#ll check later for you.

Are you using th workbook that I uploaded, if not attach the latest version.

yes, I am using your last upload

Mike

I completely forgot to amend the Filter criteria rows and I think this was causing the problems. I was concentrating on the code to test for different passwords and the different email addresses.

I've amended the Columns that are sent. Download it again and test it again.

New version

Hi Roy,

I'm still having the same problems.

The macro only works when column W is populated with billbloggs@email.com. However even then it still won't send row 13. This row should be sent as column x contains pending and column y contains EXTERNAL QUERY

1. You will see on the attached workbook I have changed all of the email addresses in column W to rob.marr@thstools.co.uk - When I do this the macro fails and an email is not sent. I get the same error as I highlighted and attached in my previous message. (feel free to test with this email). When the macro fails the filter does not clear.

Could you also confirm that when there are multiple different email addresses populated in column W, separate emails and files will be sent to each different address. Obviously if the same email address appears in multiple rows only one email and attachment is sent to this email address.

Also the values in columns X and Y should be available via data validation rather than being free typed.
Finally the attached file for macro two needs to be called "supplier weekly queries update"

Mike
datal-validation-added-2----1.xlsm

The code is written to send emails to each email recipient that has entries matching the criteria.

I have no idea what Data validation you require for X & Y, this is the first time that you have mentioned it.

Additional Requirements

1. I would like to add two new columns to the right of column W (i.e.. X and Y). They will have the column heading names "supplier credit no." and "supplier comments". These additional columns also need to be sent when the email is sent. Presumably the formulas/coding assigned to the current X and Y columns will need to be adjusted accordingly as they will become Z and AA when the two new columns are added.

You can add the data validation into the cells and it will populate automatically as the formulas do.

Can you provide a larger dataset with other emails because it worked perfectly when I added the dummy one and I'll test the email part tonight

Apologies I think I may be over complicating things

The values in columns Z and AA will now be selected via drop downs (data validation).

I have populated column W with three different email addresses (see attached). Below I have listed the rows which should be sent to these emails addresses based on my criteria.

rob.marr@thstools.co.uk

result - one email with one attachment should be sent containing row 6 only from the query log. The attachment should be entitled "supplier weekly queries update".

robmarr789@gmail.com

one email with one attachment should be sent containing rows 9,12 and 15  from the query log. The attachment should be entitled "supplier weekly queries update".

mikes6058@gmail.com

No email or attachments should be sent

Therefore dates in column AB should be updated in the following rows....6,9,12,15

hopefully this makes more sense.

Mike
multiple-email-address-test-sheet.xlsm

Hi Mike

Will you keep the supplier email addresses in the other sheet up to date with new email addresses?

I will use that in the code and simplify things.

Hi Roy,

Yes the sheet of supplier names and email addresses will we updated. Eventually it will have 185 supplier email addresses

Mike

Hi Mike

I'm revising the code and will be in touch later. need to do some more testing

That's great, thanks for the update.

Mike

Hi Roy,

I was just wondering whether you had a revised workbook for me to test?

Mike

Hi Mike

I  have a problem with the code that should stop workbooks being sent if no data is available to send. I'm sure it's just smething simple that I'm missing.

I'm away on holiday now but I'm taking this with me. Hopefully where I'm staying will have reasonable internet access.

That's great, I am available to test over the weekend if you do get a chance to look at it. Enjoy your holiday!

Mike

Hi Mike

Sorry about not getting back to you but there was no Internet there and the mobule internet we had was useless!

Anyway I've completely revised the code and I think it's working as you want. You need to test the code with some proper data, I think the data that I am using isn't right but I'm sure that the process works fine.
datal-validation-added-4---1.xlsm

Hi Roy,

No problem.

Initial observations

1.  By simply downloading your attached file - removing the 'send to send the emails automatically and clicking "send update" I have noticed the following. Based on the parameters you have set the macro should send rows 3,6 and 9 to billbloggs@email.com and 0 emails to any of the other email addresses populated in column W. The macro is sending only rows 3 and 6 but not 9?

2. I then made the following changes to test

- Corrected the data validation errors in columns Z and AA so they link to the values in "sheet1".

- Input and copied down a vlookup formula in colum W which will automatically link the supplier name in column G to the corresponding email address found in sheet "supplier email addresses" - for the purposes of the test I have changed the names in column G so a value will always be returned in column W.

- Used the drop downs in columns Z and AA to  select values that will allow for 3 separate results criteria.

Based on these selections the following should happen

rob.marr@thstools.co.uk

row 2 should be sent only - in one email with one attachment -  new date to be populated in collumn AB for row 2 only

robmarr789@gmail.com

rows 7,8 and 11 should be sent only -  in one email with one attachment - new date to be populated in collumn AB for row 7,8 and 11 only

billbloggs@email.com

No email should be sent - no new dates should be populated

RESULT

When I run the macro I am getting the following results

rob.marr@thstools.co.uk

row 2 is correctly attached and sent however a date is also being populated in column AB for row 3

robmarr789@gmail.com

rows 7 and 8 are being sent but row 11 is not? row 6 is also being wrongly populated with a date

billbloggs@email.com

It appears as if when the macro gets to this stage it is opening up an email window in outlook and leaving the address field empty. I then have to exit the window to ensure an email isn't send.

For this email address  nothing should appear to happen on the screen.

Additional comments

- Although I have removed the ' in front of .send an outlook window is still opening
- The "email sent" command box is appearing twice. meaning I have to click "ok" twice.
- I am unable to re-run the macro again after the first time. When I click "send update" nothing is happening.


Please could you test the macro by running the "send update" macro on the attached file. The worksheet is set up exactly as I have explained above. See if you get the same results as me. Don't worry about changing the email addresses they are only test email addresses.

Thanks Mike
query-test.xlsm

I'm at work now, but will look at this later

The outlook window was showing because you haven't deleted the .Display line.

robmarr789@gmail.com
In your last attachment one of the relevant rows contained a last sent date so only two rows were picked up. It now picks up 3.

Try this and let me know.
datal-validation-added-5---1.xlsm

Ah that explains something. Apologies, this is my fault for not being clear.

Rows containing a date in column AB should still be consolidated and sent. The only thing which should prevent a row from being sent is if columns Z or AA contain RESOLVED, VOID, UNDER REVIEW or INTERNAL QUERY.

The reason I want the macro to populate a date in column AB is to serve as a reference to the user so they know the last time a pending external query was sent. They will still want to resend the same row if it remains pending external. So if a date is already populated in row AB when the macro is run it will simply overwrite the original date.

This would explain why nothing was happening when I tried to run the "send update" for a second time.


Also the column headings from AC onwards on the sheet you attached contain dates. The column headings should be exactly as they are in my previous upload.

Hopefully this is a quick fix. Otherwise everything appears to be working.

Thanks
Mike

Hi Mike

I have been working all along to only include lines that do not have a last sent date entered. I've edited out that line.

Sorry about the headings, I changed them by accident with the code and forgot to change them back

I think this works as you want
datal-validation-added-5---1.xlsm

Sorry for the confusion.

It all seems to be working other than when I check the sent attachments I have noticed some additional column headings have been sent. Only columns in the range from A upto and including Y should be sent.

Mike

That's great. Good work from start to finish again.

I have another couple of shorter queries which I will raise tomorrow.

Mike

I'll not be able to look properly until Saturday. It's been a good project and I've learned a few new tricks on the way

Hi Roy,

Sorry to be difficult but could you explain in steps how I am able to send the supplier update from a chosen outlook account (query@). I know you have included the code into the macro but I can't figure out how to find the relevant account no. for my chosen email address. I've run the code below to find out but I don't know where it is supposed to return the account numbers to choose from??

Sub Which_Account_Number()
'code based on original by Ron de Bruin
'http://www.rondebruin.nl/win/s1/outlook/account.htm

    Dim olNS As Object
    Dim olApp As Object
    Dim olMail As Object
    Dim OutlookWasNotRunning As Boolean
    Dim I As Integer

    ' Check if Outlook is already running
    On Error Resume Next
    Set olApp = GetObject(, "Outlook.Application")


    For I = 1 To olApp.Session.Accounts.Count
        MsgBox olApp.Session.Accounts.Item(I) & " : This is account number " & I
    Next I
End Sub

i Mike

That code should list the available addresses to choose from.

This line then needs adjusting

.SendUsingAccount = olApp.Session.Accounts.Item(1)

Select allOpen in new window

At the moment it is using the first account in the list. Send me  message with the email addresses that the code finds in the order that it lists them. Also, say which you want to use.