Email selection from Excel using Outlook

Question

In Excel I have a contact list, e.g.:

  A                   B
1 Bob Marley          [email protected]
2 Michael Jackson     [email protected]
3 Freddie Mercury     [email protected]

Is there some VBA code that will copy the email addresses (B1:B3) and open a new email item in Outlook and paste the email addresses into the 'To' section in the email?

I've looked at the following web references but they don't seem to work:

http://www.slipstick.com/developer/create-a-new-message-using-vba/

Open new message in Outlook by Excel VBA

VBA Outlook Mail .display, recording when/if sent manually

tea_pea tea_pea · Accepted Answer · 2015-07-09T13:06:05

That first reference very nearly does it for you. Just adjust the hard coded 'to' value as the cell value. Put the thing in a loop if you want all three addresses.

Dim objMsg As MailItem

Set objMsg = Application.CreateItem(olMailItem)

 With objMsg
  .To = Range("B2").Value
  .Subject = "This is the subject"

  .Display
End With

Set objMsg = Nothing
End Sub

EDIT

This is tested on my comp

Sub practisemail()

    Dim objOutlook As Object
    Dim objMail As Object

    Set objOutlook = CreateObject("Outlook.Application")
    Set objMail = objOutlook.CreateItem(0)

     With objMail
      .To = Range("B2").Value
      .Subject = "This is the subject"
      .Display
    End With

    Set objOutlook = Nothing
    Set objMail = Nothing

End Sub

Email selection from Excel using Outlook

1 Answers