check name of the Macro

[saravanan1981]

I have Macro. I have given partial code Where I am Changing value manually case (Range("E2").Value)

Sub E_mail_Apollo_GMC_GPA()
    'change cell number to change recepient name/cc name/subject etc
        .To = ThisWorkbook.Sheets("Contacts").Range("E2").Value
        .Subject = ThisWorkbook.Sheets("Contacts").Range("H2").Value
        .HTMLBody = StrBody & RangetoHTML(rng) & StrBody2 & .HTMLBody
        ' email code-In place of the following statement, you can use ".Display" to
        ' email code-display the e-mail message.
        .Display

I want code to change the “E2” in Range("E2").Value automatically.

Example in if Macro Name is Sub E_mail__Apollo_GMC_GPA()

I want E2 be changes automatically as B18 (Because B18 is against Apollo). In short Check Macro Name if it contains text “Apollo” then check Column A in Sheet “Contacts” and put the cell number against that.
In this Example Apollo is in A18 in Sheet “Contacts”. Hence I need B18 .

if Macro Name is Sub E_mail__Chola_GMC_GPA()

I want E2 be changes automatically as B18 (Because B3 is against Chola). In short Check Macro Name if it contains text “Chola” then check Column A in Sheet “Contacts” and put the cell number against that.
In this Example Chola is in A3 in Sheet “Contacts”. Hence I need B3 .

Same applies to Range("H2").Value
It should be changed as H18 in first example and as H4 in second example.


Reason being is The rows in Sheet in “Contacts” will be changing from time to time due to my some other work. Hence Every time I am changing the cell value manually.

[tim201110]

it'be better if you uploaded your file
there is a better decision