Any Visual Basic Guru's?

[monsween]

Alright chaps. Anyone handy with Visual basic for excel?

 

 

I would like to be able to reference cells so that certain information can be put into an email that can then be used to inform the correct person what stock to order.

 

Here is what i've come up with so far:

 

Sub OrderStock()

'Variable declaration

Dim oApp As Object, _

oMail As Object, _

WB As Workbook, _

FileName As String

 

'Turn off screen updating

Application.ScreenUpdating = False

 

'Create the email template

Dim strbody As String

With ActiveSheet

strbody = "Hi," & vbNewLine & vbNewLine & _

"Could you order these items for me" & vbNewLine & vbNewLine & _

"Part Number: " & _

.Range("A34") & vbNewLine & _

"Description: " & _

.Range("B34") & vbNewLine & _

"Quantity: " & _

.Range("N34") & vbNewLine & vbNewLine & vbNewLine & _

"Regards" & vbNewLine & _

"Stores"

End With

 

 

'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 = "[email protected]"

'Uncomment the line below to hard code a subject

.Subject = "Order"

.Body = strbody

.Display

 

End With

 

 

 

End Sub

 

The problem being is that i want one marco that can be used for the whole operation.

 

The values i need are from the collumns A,B and N. and rows 10-200+

 

How do i change it so i can reference one specific set of A's B's and N's?? IE in the example above it references row 34 into the email.

 

I have a button that is linked to the macro then a box next to it to type in the row number that is to be ordered.

 

So if item in row 54 needed to be ordered, type 54 into the box then hit the order button and the correct data would be referenced from A54, B54 and N54. Then subsequently sent out via email.

 

 

Thanks in advance

Kieran

[MrRalphMan]

You can use the following to get the text from a CELL.

 

Cells(1, 1).Text

 

So you could use

.Range("A&Cells(x, y).Text) & vbNewLine & _

 

Where x = row number and y = column number.

 

Even better DIM a variable up the top MyRowNumber

Set this to the value and then put that into the line above.

 

.Range("A&MyRowNumber) & vbNewLine & _

 

 

See if that works..

[monsween]

i'll have a go at that when i get to work tomorrow, anyone else with any other ideas incase that doesnt work?