Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old October 2nd, 2007, 06:02 PM
Registered User
 
Join Date: Oct 2007
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default Incorporating lookup function in macro

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 = "name@wherever.com"
        .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
Reply With Quote
  #2 (permalink)  
Old October 3rd, 2007, 10:38 AM
Friend of Wrox
Points: 513, Level: 8
Points: 513, Level: 8 Points: 513, Level: 8 Points: 513, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Feb 2007
Location: Davenport, IA, USA.
Posts: 163
Thanks: 0
Thanked 2 Times in 2 Posts
Default

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.

Reply With Quote
  #3 (permalink)  
Old October 3rd, 2007, 10:47 AM
Registered User
 
Join Date: Oct 2007
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #4 (permalink)  
Old October 4th, 2007, 09:28 AM
Friend of Wrox
Points: 513, Level: 8
Points: 513, Level: 8 Points: 513, Level: 8 Points: 513, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Feb 2007
Location: Davenport, IA, USA.
Posts: 163
Thanks: 0
Thanked 2 Times in 2 Posts
Default

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?

Reply With Quote
  #5 (permalink)  
Old October 4th, 2007, 03:50 PM
Registered User
 
Join Date: Oct 2007
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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 @ mikethelever10@hotmail.com

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

Reply With Quote
  #6 (permalink)  
Old October 8th, 2007, 02:41 PM
Registered User
 
Join Date: Oct 2007
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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; chantel@velocityfilms.com; laurence@velocityfilms.com 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 !!

Reply With Quote
  #7 (permalink)  
Old October 10th, 2007, 08:36 AM
Friend of Wrox
Points: 513, Level: 8
Points: 513, Level: 8 Points: 513, Level: 8 Points: 513, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Feb 2007
Location: Davenport, IA, USA.
Posts: 163
Thanks: 0
Thanked 2 Times in 2 Posts
Default

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

Reply With Quote
  #8 (permalink)  
Old October 10th, 2007, 03:34 PM
Friend of Wrox
 
Join Date: Nov 2004
Location: Port Orchard, WA, USA.
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

Allen,

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

Thanks
Reply With Quote
  #9 (permalink)  
Old October 11th, 2007, 03:31 AM
Registered User
 
Join Date: Oct 2007
Location: , , .
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Allen,

Please mail me at mikethelever10@hotmail.com

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 !

Reply With Quote
  #10 (permalink)  
Old October 12th, 2007, 05:53 PM
Friend of Wrox
Points: 513, Level: 8
Points: 513, Level: 8 Points: 513, Level: 8 Points: 513, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Feb 2007
Location: Davenport, IA, USA.
Posts: 163
Thanks: 0
Thanked 2 Times in 2 Posts
Default

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.

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
calling to xlam macro from macro inside xlsb SteveB Excel VBA 0 June 30th, 2008 06:43 PM
Chapter 1 exercise questions: incorporating date dcr BOOK: Beginning SQL 0 October 11th, 2007 09:07 AM
createuserwizard / incorporating personalisation.. le_beluet ASP.NET 2.0 Basics 1 February 23rd, 2007 07:00 AM
lookup function Vince_421 Access VBA 14 May 19th, 2006 07:27 AM
retreive function/Line from macro or function? MikoMax J2EE 0 April 1st, 2004 04:42 AM



All times are GMT -4. The time now is 11:04 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.