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
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:
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?
0
mikes6058Author Commented:
Great, yes I'm using outlook.

Thank-you
0
Roy CoxGroup Finance ManagerCommented:
Try this. Copy it in place of my previous code

Option Explicit

Dim lRw As Long
Sub ImportData()
    Dim oWb As Workbook

    Dim sFilter As String, sTitle As String, sFile As Variant

    sFilter = "Excel Files (*.xl*),*.xl*"
    sTitle = "Please Select an Excel File"
    sFile = Application.GetOpenFilename(sFilter, , sTitle)

    If sFile = "False" Then
        MsgBox "No file selected", vbCritical, "Cancelled"
        Exit Sub
    End If

    If LCase(Mid(sFile, InStrRev(sFile, "."), 3)) <> ".xl" Then
        MsgBox "Excel File not selected", vbCritical, , "Excel rerquire"
        Exit Sub
    End If

    Workbooks.Open Filename:=sFile
    Set oWb = ActiveWorkbook
    With ThisWorkbook.Sheets(1)
        lRw = .Cells(.Rows.Count, 3).End(xlUp).Row + 1
        .Cells(lRw, 3).Value = .Cells(lRw - 1, 3).Value + 1
        .Cells(lRw, 1).Value = "Q" & .Cells(lRw - 1, 3).Value + 1
        .Cells(lRw, 2).Value = Format(Date, "short date")
        oWb.Sheets(1).Cells(1, 1).CurrentRegion.Offset(1).Copy .Cells(lRw, 4)
    End With
    oWb.Close False
    Select Case MsgBox("Would you like to email the report?", vbYesNo Or vbQuestion Or vbDefaultButton1, "Email Report")

    Case vbYes
        EmailIt
    Case vbNo

    End Select
End Sub
Sub EmailIt()

    Dim AddxCell As Excel.Range
    Dim AttachFile As String
    Dim olApp As Object
    Dim olMail As Object
    Dim olNS As Object
    Dim OutlookWasNotRunning As Boolean
    Dim Rng As Excel.Range
    Dim LastEntry As Excel.Range

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

    If Err.Number <> 0 Then
        Err.Clear
        OutlookWasNotRunning = True
        Set olApp = CreateObject("Outlook.Application")
    Else: Set olApp = GetObject("Outlook.Application")
    End If

    ' Logon to the Messaging Application Program Interface
    ' This is how Oulook communicates with its folders and items
    Set olNS = olApp.GetNamespace("MAPI")
    olNS.Logon

   
    Set olMail = olApp.CreateItem(0)        'olMailItem = 0

    With olMail
        .To =   .To = ThisWorkbook.Sheets(1).Cells(lRw, 6).Value
        .Subject = "Invoice Query Acknowledgement"

        .Body = "Member Name: " & ThisWorkbook.Sheets(1).Cells(lRw, 5).Value & vbNewLine & _
                "Supplier Name: " & ThisWorkbook.Sheets(1).Cells(lRw, 7).Value & vbNewLine & _
                "Value on Query: " & ThisWorkbook.Sheets(1).Cells(lRw, 16).Value & vbNewLine & _
                "Query Ref.: " & ThisWorkbook.Sheets(1).Cells(lRw, 1).Value & vbNewLine & _
                "Supplier Invoice No.: " & ThisWorkbook.Sheets(1).Cells(lRw, 15).Value & vbNewLine & _
                "Date THS HQ Logged Query: " & Format(ThisWorkbook.Sheets(1).Cells(lRw, 7).Value, "short date")
                '/// show message for checking
        .display
       '/// use next line to simply send without checking
'        .Send
    End With

    MsgBox "Report sent"

    ' End session and quit
    olNS.Logoff
    If OutlookWasNotRunning = True Then olApp.Quit

    ' Free memory
    Set olApp = Nothing
    Set olMail = Nothing
    Set olNS = Nothing

End Sub
0

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
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

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

0
mikes6058Author Commented:
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?
0
Roy CoxGroup Finance ManagerCommented:
In the notes of the macro you will see instructions. Basically replace .Show with .Send
0
mikes6058Author Commented:
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
0
Roy CoxGroup Finance ManagerCommented:
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.
0
mikes6058Author Commented:
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)

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28656182.html
0
Roy CoxGroup Finance ManagerCommented:
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.
0
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.