Using Excel to create the body of an Outlook e-mail

February 24, 2010 9:24 PM

Complex Excel question - need to select a range of cells, based on the selection of a separate cell. But wait, it's more complicated than that!

I'm creating a workbook with a feature that copies a range of cells into the body of an Outlook e-mail message.

I have the VBA to generate an Outlook e-mail from Excel, and I have the VBA to run a macro based on simply selecting a cell in a certain column - all of this falls in line with what I need.

What I don't have is the VBA to, based on the selection a cell, copy a range of cells.

For instance: I select cell "J8", and cells "A8:H12" are copied. Or if I select cell "J20", cells "A20:H24" are copied.

Preference is for this to be accomplished via VBA.

Any help?

posted by doh ray mii to Computers & Internet (1 answer total)

If always you want to select 8 columns (not including the current one) to the right and 5 rows (including the current one) down, you can do it with:

Sub Macro1()
    ActiveCell.Offset(0, -9).Range("A1:I5").Copy
End Sub

If you want to do something more complicated (i.e. you want to use VBA to fiddle around with what selection is made) you can use functions like this:

Sub Macro2()
    If (ActiveCell.Address = "$J$8") Then
        Range("A8:I12").Copy
    End If
End Sub

posted by Mike1024 at 12:24 AM on February 25, 2010