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
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
Microsoft ExcelVBA
Last Comment
Roy Cox
ASKER
Great, yes I'm using outlook.
Thank-you
Thank-you
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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
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
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
to
.To = ThisWorkbook.Sheets(1).Cells(lRw, 6).Value
ASKER
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
ASKER
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
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.
ASKER
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
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.
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.
I'm at the day job just now.
I'll take a look later. It should be reasonably simple. Are you using OutLook?