1. change to:
a = Me![MS no]
Set rs = db.OpenRecordset("select [Email id] from [profile details form updated] where [Undergoing MS number] Like '*" & a & "*'", dbOpenSnapshot, dbReadOnly)

2. you google DLookup(), to return a field from different table.

T = DLookup("[fieldToReturn]" , "[otherTableName]", "[Condition]")