Correspondance search between sheets

[sindhuja]

Hi All,

I have few buttons in sheet1 with the button names specified. For the corresponding names i have data in sheet2. Depending upon the button i click on sheet1, data in sheet2 should be searched for the same and display the corresponding content (in column 2 of sheet2) in the new outlook message.

Is this possible by macros... if so help me out in this...

-Sindhuja

[Ken Puls]

Hi there,

You will want to start by looking up the Find method to quickly locate your given parameter in the second sheet. Here is just one example of using the Find method from our KB.

After you've done that, you can create your Outlook instance, new mail, and read the values into the message body. I have an example at my site of creating a new email from another application, which also uses ClickYes to avoid security prompts. (ClickYes is a separate download.) The real intent of pointing you there, however, is that I do create a subject and body of the email, so you can follow that part along.

[sindhuja]

Thanks Ken,

Is there a way to give the name of the button am clicking as the search criteria and find the corresponding fields in sheet2.

The posting in the link paritally helps me out.

I have also attached the sheet for the easy understading of my reqirement better...

- Sindhuja

[Ken Puls]

Not as such, no.

What I would do is set up each commandbutton as follows:
[vba]Private Sub CommandButton1_Click()
Call SendMyEmail("One")
End Sub

Private Sub CommandButton2_Click()
Call SendMyEmail("Four")
End Sub

Private Sub CommandButton3_Click()
Call SendMyEmail("Three")
End Sub

Private Sub CommandButton4_Click()
Call SendMyEmail("Two")
End Sub[/vba] These all go in the Sheet1 module. The reason that they are named inconsistently is because your buttons names don't match the text.

Then, in a standard module, place the following code:
[vba]Sub SendMyEmail(sCase As String)
'Author : Ken Puls (www.excelguru.ca)
'Macro Purpose: To send an email through Outlook without worrying about
' security prompts

Dim objOL As Object
Dim objMail As Object
Dim rngEmailAddies As Range

'Set the range to look up data
With Worksheets("Sheet2")
Set rngEmailAddies = .Range("A2:" & .Cells(.Rows.Count, 5).Address)
End With

'Turn on error handling
On Error GoTo Cleanup

'Bind to Outlook
Set objOL = CreateObject("Outlook.Application")

'Create a new email and send it
Set objMail = objOL.CreateItem(0) '0=olmailitem
With objMail
.To = Application.WorksheetFunction.VLookup(sCase, rngEmailAddies, 2)
.Cc = Application.WorksheetFunction.VLookup(sCase, rngEmailAddies, 3)
.Bcc = Application.WorksheetFunction.VLookup(sCase, rngEmailAddies, 4)
.Subject = "Testing Routine To Send Email"
.Body = Application.WorksheetFunction.VLookup(sCase, rngEmailAddies, 5)
.Display
' .Send
End With

Cleanup:
'Release all objects
Set objMail = Nothing
Set objOL = Nothing
On Error GoTo 0
End Sub[/vba]
HTH,

[sindhuja]

Wow.... wat a quick response Ken !!

Let me work this out and let you know in case of any problems...

Thank you once again...!

-Sindhuja

[sindhuja]

Hi,
Am facing some problems in the coding..

I have attached another sample sheet for the reference?
When I click on any button it automatically picks the 1st value on 1st click.

Even if I click on the same button for second time also it picks the second value in the sheet 2.

Actually I need to loop thorough the values in name columns of sheet2..
For ex if I click on Unknown Exception it should check for the value Unknown Exception and fetch the corresponding values..

But this is not actually happening.. Picks up the value in a sequential manner?

Have a look and help me out in this..

-Sindhuja

[Ken Puls]

Hi Sindhuja,

Oops... I hate it when I forget to add a "false" to my vlookups! Get burned every time...

Try this:
[vba]Sub SendMyEmail(sCase As String)

Dim objOL As Object
Dim objMail As Object
Dim objwShell As Object
Dim rngEmailAddies As Range

'Set the range to look up data
With Worksheets("Sheet2")
Set rngEmailAddies = .Range("A2:" & .Cells(.Rows.Count, 5).End(xlUp).Address)
End With

'Turn on error handling
On Error GoTo Cleanup

'Bind to Outlook
Set objOL = CreateObject("Outlook.Application")

'Create a new email and send it
Set objMail = objOL.CreateItem(0) '0=olmailitem
With objMail
.To = Application.WorksheetFunction.VLookup(sCase, rngEmailAddies, 2, False)
.Cc = Application.WorksheetFunction.VLookup(sCase, rngEmailAddies, 3, False)
'.Bcc = Application.WorksheetFunction.VLookup(sCase, rngEmailAddies,4, False)
.Subject = Application.WorksheetFunction.VLookup(sCase, rngEmailAddies, 4, False)
.HTMLBody = Application.WorksheetFunction.VLookup(sCase, rngEmailAddies, 5, False)
.Display
'.Send
End With

Cleanup:
'Release all objects
Set objMail = Nothing
Set objOL = Nothing
On Error GoTo 0
End Sub[/vba]

Also, I noticed in your sample that "Unknown Exception" also appears as "UnknownException". Be careful with that, as the vlookup (now) requires an exact match.

Cheers,

[sindhuja]

Thanks Ken

It works perfectly for me...

And one more doubt... is it possible to add any image to the buttons
Instead of command name i want to use some images....

If this is possible, then there will be slight modifications in my requirement.
let me know your thoughts !!

-Sindhuja

[Ken Puls]

... is it possible to add any image to the buttons
Instead of command name i want to use some images...

Sure... you're using ActiveX controls, so put the workbook in design mode, right click your button and choose properties. On the list, you'll have an item called "Picture", so click it, then click the ... that show up at right. Broswe for your image.

Once you're done, take the workbook back out of design mode, and you should be good to go.

HTH,

[sindhuja]

Thanks for the info Ken,

If I use the same picture for all the button I use, then how will I loop for the values I am looking for…

As of now, with the button name I am looping through for the values in sheet 2 and produce the output.

Am not sure how to proceed further in this…
Format will be like this:
Sample
Text 1 (followed by button with image)
Text 2 (followed by button with image)
Sample 2
Text 3 (followed by button with image)
Text 4 (followed by button with image)

Text 1 , 2, 3 ,4 are the values in Sheet 2 am looking for..

-Sindhuja

[sindhuja]

Hi Ken,

Any clue on my reuirement....

-Sindhuja

[Ken Puls]

Sorry, I missed that you'd followed up on this. Just an FYI, I'm in a conference for the next two days, so I may not be able to reply.

I'm curious now though... what change do you see an image playing in this? It's just a picture on the button. If the code already works, then the picture shouldn't make any difference.... or did I miss something?

Can you upload a new sample workbook and explain a little more what you're expecting to see?

[sindhuja]

Not a problem Ken...

Below is my requirement?

I have placed a button in column B just left to that is an e-mail image. I want tat image to be in the button.

Previously we have assigned a name to the button. On the click of the button, it will search for the text in button in Mail template sheet and display the corresponding values.

Now, all the button will be of the same image. So we need to pick the values of the cell before the button....For eg, if I click on the button in cell C3, the value B3 should be considered as the search field. Search for the "High Amount" in Mail template sheet and outlook message to be opened in correspondance to the value.

Immediate help will be highly appreciated?

-Sindhuja

[Ken Puls]

Hi Sindhuja,

Sorry, I'm just poking in here quickly... immediate help is going to be an issue as I'm writing an article for a magazine for this weekend, and then have some other jobs to take care of. I'll try to get back to this shortly, but hopefully somone can come along and pitch in if I'm delinquent for a day or two...

[sindhuja]

Not a problem Ken...

[sindhuja]

Any update on this plz....

-Sindhuja

[Ken Puls]

Sorry Sindhuja, I've been very busy.

I just took a look at your file, and I don't really understand why you're being held up here. If you activate the Design Mode (the triangle on the Visual Basic toolbar), you can right click your buttons and choose "View Code". This will take you right into the VBE for that button, and you can simply copy the email code out of the existing routines into the correct one.

So, for example, for the button in Cell C3, enter the following code into the VBE:
[vba]Call SendMyEmail("High amount")[/vba]

[sindhuja]

Hi Ken,

I want the e-mail picture to be on the button. When the button is clicked it should pick up the corresponding value left to the button. then it should search for the same in Mail Template sheet and correspondence should be displayed...

Hope this will be clear....

-Sindhuja

[Ken Puls]

Sinhuja,

I'll help you do this, but I'm not going to do it for you.

To get the picture on your button:
-Go into design mode, right click the button, and choose Properties
-Look down the list, you'll find one that says Picture (see the attached pic)
-Click in the right hand field of the picture line
-Click the ... button
-Browse and locate your picture on your computer (jpg will work)
-Say Open, and it will be linked to your button

Now, with regards to linking the left cell to the button, I'm not sure that I would do this any way but manually. I feel that you're going to need to put in a ton of work to try and get this to work dynamically, and I wouldn't bother unless you had 100 rows or more to do.

To do it manually:
-Enter Design Mode
-Right click the button
-Choose View Code. You'll be dropped into the VBE inside the routine for that button
-Enter the following code in the routine (assuming you are working on the button in C3):

[vba]Call SendMyEmail(Worksheets("Retail").Range("B3").Value[/vba]

The C4 button would reference B4, etc...

[sindhuja]

Thanks a lot Ken ! Its perfect...