Incorporating lookup function in macro

mikelever · October 2nd, 2007, 06:02 PM

Hi,

i have the following sendmail macro. However I need the "To" section to be the result of a lookup in a worksheet. The specific function that i need it to be is: =VLOOKUP(A837,'Tenacity Jobs'!1:65536,5,FALSE)

How can I incorporate this function so it will insert the result of the lookup into the "To" box ?

Code:

Sub SendEmail()

Dim OlApp As New Outlook.Application
Dim myNameSp As Outlook.Namespace
Dim myInbox As Outlook.MAPIFolder
Dim myExplorer As Outlook.Explorer
Dim NewMail As Outlook.MailItem
Dim OutOpen As Boolean

    ' Check to see if there's an explorer window open
    ' If not then open up a new one
    OutOpen = True
    Set myExplorer = OlApp.ActiveExplorer
    If TypeName(myExplorer) = "Nothing" Then
        OutOpen = False
        Set myNameSp = OlApp.GetNamespace("MAPI")
        Set myInbox = myNameSp.GetDefaultFolder(olFolderInbox)
        Set myExplorer = myInbox.GetExplorer
    End If
    'myExplorer.Display ' You don't have to show Outlook to use it

    ' Create a new mail message item.
    Set NewMail = OlApp.CreateItem(olMailItem)
    With NewMail
        '.Display ' You don't have to show the e-mail to send it
        .Subject = "Look at this!"
        .To = "[email protected]"
        .Body = "This is a demonstration"
    End With

    'NewMail.Send
    If Not OutOpen Then OlApp.Quit

    'Release memory.
    Set OlApp = Nothing
    Set myNameSp = Nothing
    Set myInbox = Nothing
    Set myExplorer = Nothing
    Set NewMail = Nothing

End Sub

allenm · October 3rd, 2007, 10:38 AM

The best bet would be to use the Find Method of the Range Collection.
Assuming A837 is on the active worksheet:
-----------------------------------------------
dim oFind As Range
set oFind = Worksheets("Tenacity Jobs").Range("A:A").Find(ActiveSheet.Cells(837,1). Value, LookIn:=xlValues)
If oFind Is Nothing Then
Exit Sub 'Or whatever you wish to do when value is not found in column A
Else
MsgBox "Found: " & Worksheets("Tenacity Jobs").Cells(oFind.Row, 5).Value 'Or whatever you wish to do with it
End If
-----------------------------------------------

Hope this helps.

mikelever · October 3rd, 2007, 10:47 AM

Hey Allan,

thanks for that response.

One questin.. how do i incorporate your script into the macro tried pasting it just after the to bit and got syntax errors ?

Again here is the code i need it to be in :

'Create and show the outlook mail item
    Set oApp = CreateObject("Outlook.Application")
    Set oMail = oApp.CreateItem(0)
    With oMail
         'Uncomment the line below to hard code a recipient
        '.To =
         .Subject = "Job Print"
        .Attachments.Add WB.FullName
        .Display
    End With

allenm · October 4th, 2007, 09:28 AM

Are you emailing from a form or different workbook from where you are trying to do your lookup? Is it the same workbook that your attaching? Remember that if it's a different workbook you're referencing then the one where this code resides that it has to be open before running the code.

Try being explicit with this line:
ActiveSheet.Cells(837,1).Value
Not sure what you have, so replace SheetName with the worksheet you're pulling the value from.
Worksheets("SheetName").Cells(837,1).Value

If you are running this macro from a form or other workbook then you'll have to be explicit with which workbook as well. Try adding the following, including the dot, in front of all 'Worksheets' syntax. Example:
Set oFind = Workbooks("BookName").Worksheets("Tenacity Jobs").Range("A:A").Find(Workbooks("BookName").Wor ksheets("SheetName").Cells(837,1).Value, LookIn:=xlValues)

Feel free to shorten this using With as follows:

With Workbooks("BookName")
Set oFind = .Worksheets("Tenacity Jobs").Range("A:A").Find(.Worksheets("SheetName"). Cells(837,1).Value, LookIn:=xlValues)
End With

Does this help more?

mikelever · October 4th, 2007, 03:50 PM

hey Allen,

thanks again for this info. I think it would be best of we chatted over MSN or the like.

Please msn / mail me @ [email protected]

I would really appreciate it. Remember that being in South Africa I am a bit ahead of you 6 hours I think.

mikelever · October 8th, 2007, 02:41 PM

Hi all,

I have finnaly been able to put in word what I want to do. hope this makes sense. I have the excel document should anyone want to see it.

In worksheet âSummary tableâ, column A is a job number (Txxx), that worksheet is a summary of all the jobs. If you select a job number and run a macro ( lets call it macro 5) it will then give you a detailed / itemized listing of the job in a separate worksheet. I already have created macro5 for the detailed bit. However now I am looking for a macro that will lookup the email address in worksheet âTenacity jobsâ corresponding to that job number and then email the result of macro 5 to the user.

So, for example, I begin in worksheet âSummary tableâ I would like to see the details on job # T456 which has a subtotal of 420,227.05. I click on this and it runs macro5 showing me the detailed breakdown. However once that macro has run I would then like to email this off to; [email protected]; [email protected] as they are the contact people which corresponds to #T456 in worksheet âTenacity jobsâ

Any help in how I can do this would gladly be appreciated !!

allenm · October 10th, 2007, 08:36 AM

I'd like a copy of the spreadsheet to work with. Do you have a site it can be downloaded from?

BrianWren · October 10th, 2007, 03:34 PM

Allen,

Could you please take a look at http://p2p.wrox.com/topic.asp?TOPIC_ID=65270?

Thanks

mikelever · October 11th, 2007, 03:31 AM

Hi Allen,

Please mail me at [email protected]

I will then put the files (32mb) onto our FTP site. I would probably need to talk you through the workbook as specific macros run on specific cells.

Thanks again for your help !

allenm · October 12th, 2007, 05:53 PM

My work email I'm not allowed to give out except on company business and my home email gets auto forwarded to my own domain which is hosted on my Linux server. I don't have MS Office OR Windows at home so I'd not be able to help if I couldn't do so out of memory which I already tried. When I have a moment of time and am not working overtime I'll set up a hotmail account to correspond with you through. Sorry for the delay but my weekend this week is shot already just coming out of the gate.