In SQL Server 2005, how can I bulk transfer comparable data to two tables?

It's not quite clear what exactly and how exactly you're trying to store the data from your Excel import into these two tables, and where that groupID comes from....

Basically - you're right - you cannot bulk insert into multiple tables. So my take would be:

1) Do the bulk insert from Excel into a Staging table as today

2) Then insert those pieces of information you need to store in Table1 and output the necessary info that you need to "connect" the bits for Table1 to the bits for Table2 - something along the lines of:

DECLARE @Connection TABLE (GroupID INT, PersonID UNIQUEIDENTIFIER, EMail VARCHAR(500))

INSERT INTO dbo.Table1 (list of columns here)
    OUTPUT Inserted.GroupID, Inserted.PersonID, Inserted.EMail 
        INTO @Connection(GroupID, PersonID, EMail)
    SELECT
       (list of columns here)
    FROM
       dbo.Staging
    WHERE 
       (possibly a condition here???)

This would insert the rows from Staging into your Table1, and while doing so, it will write out some information for each row inserted - the GroupID, PersonID and EMail.

With this information, you should be able to also insert your values into Table2 :

INSERT INTO dbo.Table2 (GroupID, PersonID, EMail, list of other columns here)
    SELECT
       c.GroupID, c.PersonID, c.EMail,
       (list of other columns from Staging table here)
    FROM @Connection c
    INNER JOIN dbo.Staging s ON c.EMail = s.EMail   -- or use whatever you can use to 
                                                    -- connect the two sets of data
    WHERE (condition) ......