Link to home
Start Free TrialLog in
Avatar of mikes6058
mikes6058

asked on

macro - send email containing specific info

Attached are two excel workbooks "master_query_log" and "query1".

Currently when the "import" button in the master query log is clicked it will allow the user to select the relevant query file e.g. "query1". Once selected the information will automatically be copied and pasted into the bottom row of the the master query log under the appropriate column headings.

I would like to add another step to the macro.

When the "import" button is clicked and the file is selected. I would also like the macro to automatically send an email to the email address is column F of the master query log.

The body of the email will include the following.

Member Name: RS Industrial Services Limited
Supplier Name: Guardsman Ltd
Value on Query: £247.63
Query Ref.: Q327
Supplier Invoice No.: 46/132603
Date THS HQ Logged Query: 14/04/2015

Subject: Invoice Query Acknowledgement

Note this includes information added once the sheet has been pasted into the master query log.
e.g.

UID = Query Ref.:
Date THS HQ Logged Query: = HQ Input Date

If any one is able to add the coding to the sheet and reattach that would be great.

Thanks
Master-Query-Log.xlsm
query1.xlsx
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

Hi Mike

I'm at the day job just now.

I'll take a look later. It should be reasonably simple. Are you using OutLook?
Avatar of mikes6058
mikes6058

ASKER

Great, yes I'm using outlook.

Thank-you
ASKER CERTIFIED SOLUTION
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Roy,

Everything works up until the final stage where the email does not send automatically.

At this stage a command box appears stating "would you like to email the report?"

when I select "yes" the email template then opens. The subject and body text are all correct.

However the email address field is populated with a "0" instead of the appropriate email address from column "F". I was hoping the email would automatically be sent to the appropriate email address without having to send it manually.


Thanks
The notes in the code explain to use .Send to send without checking.

I edited it this morning because I left my email address in by mistake and made a typo. Change

.To =   .To = ThisWorkbook.Sheets(1).Cells(lRw, 6).Value

Open in new window


to

.To = ThisWorkbook.Sheets(1).Cells(lRw, 6).Value

Open in new window

Thanks, that works for the email address but I do still have to click the send button to send the email. Is there a way of sending the email automatically?
In the notes of the macro you will see instructions. Basically replace .Show with .Send
hmmm that's strange, it already says .Send.....

7).Value, "short date")
                '/// show message for checking
        .display
       '/// use next line to simply send without checking
'       .Send
Sorry I didn't realise that you didn't know about this. Notice the text is green and the apostrophe before it. This makes it a comment and will not run. You need to add an apostrophe before .Show and remove the one before .Send.
Spot on! Sorry I really am a novice when it comes to VBA. I'd really like to learn the basics. Do you know of any good resources to use as a start?

Also I've just opened another Q if your'e interested (see below)

https://www.experts-exchange.com/questions/28656182/macro-to-send-certain-range-and-to-filter-depending-on-value.html
Thanks.

There's lots of good sources out there.  I have some stuff on my web site -excel-it.com.

Just keep looking at others code and asking for help if you don't understand. Don't just accept help/code try to understand it.