I was planning to change manually, for the most part. My initial thought was that I could shut off the regular notifications for sms and leave them for email only. Then I would need to create some type of a trigger for when an SMS arrives to play a sound or vibrate depending on the profile selected. Notification sounds and/or vibrate would then just apply to emails.
Can this be done? If so, I have no idea how to do it!
You can disable notifications for SMS within the messaging app. You'll also want to enable vibration in the messaging app (I think you do, anyway).
Then open Tasker and tap the Tasks button. Create a new task by tapping New (upper right) and giving the task a name (i.e. All, Phone & SMS, etc). Tap the "+" (lower left) to add an action to your task, and select Audio > Silent Mode > select Off > Done. Add another action: + > Audio > Ringer Volume > 7 > Done. And one more: + > Audio > Notification Volume > 7 > Done. On the bottom right you can select an icon for this task by tapping the blue circle with the question mark in it. Then press Done again at the lower left of the task editor.
Repeat the process, creating another task that sets silent mode to vibrate (you can likely ignore the ringer and notification volumes). Repeat again, setting silent mode to on if you want.
Now return to your home screen and long-press on an open area and select Widgets > Task, then select on of the tasks you just created (using the drop down list), and tap Make Widget. Repeat for each task you created above.
You should now have some widgets on your home screen that, when tapped, will change your volume settings.
Now to make the SMS notifications. Open Tasker and at the profiles list screen press the menu key and tap Hide Context Selector. If the button says Show Context Selector, just press the back button. Tap New > Event > Phone > Received SMS > Done. This should bring you to the task editor. Tap + > Alert > Notify Sound. Enter a title (i.e. New SMS received), select an icon for the notification (blue circle w/ question mark), check the check box next to "If", in the left box under "If" type %SILENT, in the right box type "off" (without quotes), tap Done. Tap + again, and repeat the process, changing Notify Sound to Notify Vibrate, and "off" (right box under "If") to "vibrate". Repeat the process one more time, changing "Notify Sound" to "Notify", and changing "vibrate" to "on" under "If". Tap Done > Done > Apply.
Now incoming SMS messages will preform an audio notification if Silent Mode is OFF, vibrate notification if Silent Mode is VIBRATE, and a notification without audio or vibration if Silent Mode is ON.
Once this is working, we'll look at replacing all the widgets you just created with a single widget that pops up a menu, if you're interested.