Composing an email to Outlook from Excel (incl information from different cells)

[Jake Berry]

Hi there, I'm looking for some help with drafting an email to Outlook from excel.
The Image below shows a typical sheet information. Each column has different data which is needed in one of the email fields.
I would like to be able to press a button and have a draft email appear to the 'contact' , with a pre determined subject, cc list and body.
I believe the subject and body can be drafted as text within the code, as it is not at present within a cell.

Any help would be much appreciated.

 

[portews]

See if this works for you
Export an excel range into Outlook

 

[Jake Berry]

Hi Portews, thanks for the helping hand! The comment you directed me to helped massively.
I am however still struggling to understand how i can reference the cell i need in the VBA.

For example, in the right most column, i want a button on each row to allow me to compose an email to a recipient in that same row.

I have been trying to use the .TopLeftCell command to reference the correct row.

Screenshot better shows what i mean.

 

[portews]

I know it's not what you asked for, but I would suggest you select the recipient cell (remove hyperlinks from the email addresses to make selection easier) and push the one button that runs the email macro on whatever recipients are selected rather than a button for each row. It's a lot easier to implement and a less work to maintain as your list of recipients grow. Replace the:

.Recipients.Add "Add your 1st Recipient@x.com"

row in the code on the linked article with:

        If Not Selection.Value = "" Then
            .Recipients.Add Selection.Value
        Else
            x = MsgBox("Select Recipient", vbCritical, "Program Error!"): End
        End If