There are all sorts of things you can find in your e-mail box. In the "destructive" and/or "annoying" category go e-mail attachments that contain:

• Trojan horses
• Worms
• Viruses

In many cases, e-mail viruses are not "true" viruses because they cannot replicate without human interaction. Nonetheless, they have been very effective at shutting down major e-mail systems. See How Computer Viruses Work for details on viruses. A Trojan horse, aptly named after the seemingly harmless tool of destruction in Homer's Iliad, secretly carries often-damaging software in a "plain wrapper." The plain wrapper is normally an e-mail file attachment from someone you may or may not know. The file attachment name itself can also be very misleading. When you run the attachment, it can do all sorts of things, from erasing files to changing your desktop. It then sends itself along to other people in your address book so that it can propagate itself.
Here are two examples to help you understand how e-mail viruses work. According to this page from Symantec:

Worm.ExploreZip is a worm that contains a malicious payload. The worm utilizes Microsoft Outlook, Outlook Express, Exchange to mail itself out by replying to unread messages in your Inbox. The worm will also search the mapped drives and networked machines for Windows installations and copy itself to the Windows directory of the remote machine and modify the WIN.INI accordingly. The payload of the worm will destroy any file with the extension .h, .c, .cpp, asm, .doc, .ppt, or .xls on your hard drives, any mapped drives, and any network machines that are accessible each time it is executed. This continues to occur until the worm is removed. You may receive the worm as an attachment called zipped_files.exe, masquerading itself as the usual self-extracting zip file. But, when run, this executable will copy itself to your Windows System directory with the filename Explore.exe or to your Windows directory with the filename _setup.exe. The worm modifies your WIN.INI or registry such that the file Explore.exe is executed each time you start Windows.

See also this page for details. Symantec offers more technical information and explains what you need to do if you suspect Worm.ExploreZip is in your system.
In certain special cases, e-mail attachments can execute even without your interaction. According to this Symantec Web page:

VBS.BubbleBoy is a worm that works under Windows 98 and Windows 2000. The worm will also work under Windows 95 only if the Windows Scripting Host is installed. The worm will only work with the English and Spanish versions of the operating systems, and not with Windows NT. Microsoft Outlook (or Express) with Internet Explorer 5 must be used in order for the worm to propagate. The worm utilizes a known security hole in Microsoft Outlook/IE5 to insert a script file, UPDATE.HTA, when the e-mail is viewed. It is not necessary to detach and run an attachment. UPDATE.HTA is placed in Program-StartUp of the Start menu. Therefore, the infection routine is not executed until the next time you start your computer. UPDATE.HTA is a script file that uses MS Outlook to send the worm e-mail message to everyone in the MS Outlook address book. By patching the known security hole in Microsoft Outlook/IE5, the worm will no longer propagate.

Microsoft has more information on this worm.
Keep your virus software up-to-date with the latest virus signatures from the software vendor, since the anti-virus software cannot detect new viruses without an update. If you use Norton AntiVirus software, ensure that Auto-Protect is enabled. Current Norton AntiVirus software automatically alerts you when your virus signature files are over 30 days old. Norton's LiveUpdate can also automate updating.
If you think a virus has infected your PC thanks to an e-mail virus that mails itself to people in your address book, call those people and tell them not to open the messages or attachments -- that is the only effective way to stop the spread.