amendments to macros

Macro 1: - operated by clicking "import query"

Errors
Macro is not automatically sending the email

Additional requirements

1. The macro currently automatically unprotects the incoming sheet using the password "trinity". The incoming files may also be protected by two other possible passwords. These passwords are "trinity1" and "trinity2". Is it possible for the macro to attempt all three of these passwords when unlocking the sheet?

2. Is is possible for this email to be sent from email address "query@thstools.co.uk"
If this account is not available the macro should default to any readily available outlook email address.

Macro 2

Errors

I have not been able to test the macro with the new filtering method as I am getting a coding error. When I click the "send update" button I am getting the compile error "variable not found".  (see attachment for screenshot)

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.

Any questions just let me know.

Mike
macro-2-error.docx
mikes6058Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
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.
Martin LissOlder than dirtCommented:
Where is the workbook?
Roy CoxGroup Finance ManagerCommented:
It's a follow up to a previous question
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Martin LissOlder than dirtCommented:
The Asker should post the workbook so that everyone can have a chance to answer.
Roy CoxGroup Finance ManagerCommented:
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
Martin LissOlder than dirtCommented:
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.
Roy CoxGroup Finance ManagerCommented:
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
mikes6058Author Commented:
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
Roy CoxGroup Finance ManagerCommented:
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.
mikes6058Author Commented:
yes, I am using your last upload

Mike
Roy CoxGroup Finance ManagerCommented:
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
mikes6058Author Commented:
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
Roy CoxGroup Finance ManagerCommented:
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
mikes6058Author Commented:
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
Roy CoxGroup Finance ManagerCommented:
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.
mikes6058Author Commented:
Hi Roy,

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

Mike
Roy CoxGroup Finance ManagerCommented:
Hi Mike

I'm revising the code and will be in touch later. need to do some more testing
mikes6058Author Commented:
That's great, thanks for the update.

Mike
mikes6058Author Commented:
Hi Roy,

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

Mike
Roy CoxGroup Finance ManagerCommented:
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.
mikes6058Author Commented:
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
Roy CoxGroup Finance ManagerCommented:
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
mikes6058Author Commented:
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
Roy CoxGroup Finance ManagerCommented:
I'm at work now, but will look at this later
Roy CoxGroup Finance ManagerCommented:
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
mikes6058Author Commented:
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
Roy CoxGroup Finance ManagerCommented:
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
mikes6058Author Commented:
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
Roy CoxGroup Finance ManagerCommented:
This should delete the unnecessary columns now
datal-validation-added-5---1.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mikes6058Author Commented:
That's great. Good work from start to finish again.

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

Mike
Roy CoxGroup Finance ManagerCommented:
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
mikes6058Author Commented:
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
Roy CoxGroup Finance ManagerCommented:
i Mike

That code should list the available addresses to choose from.

This line then needs adjusting

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

Open 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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.