0

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

  A                   B
1 Bob Marley          bob.marley@hotmail.com
2 Michael Jackson     michael.jackson@outlook.com
3 Freddie Mercury     freddie.mercury@gmail.com

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

Community
  • 1
  • 1
Ben Smith
  • 809
  • 5
  • 21
  • 46

1 Answers1

0

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
tea_pea
  • 1,482
  • 14
  • 19
  • Thanks for answering, I've tried your code and I get the error: "Array Lower Bound must be Zero", and it says the problem is the line '.To = Range("B2").Value` Can you please help? – Ben Smith Jul 09 '15 at 13:13
  • It's also worth noting that I want to send an email to all of the email addresses and not just one of them – Ben Smith Jul 09 '15 at 13:31
  • get one working then we can worry about sending it to all. – tea_pea Jul 09 '15 at 13:35
  • Yes that works! That's got one recipient in the 'To' section, how would I go about getting the others in there? Thank you for your help! – Ben Smith Jul 09 '15 at 14:06
  • do you want them in the same email or different emails? @Grade Lol! – tea_pea Jul 09 '15 at 14:07
  • All in the same email, so say one email that contains the three recipients – Ben Smith Jul 09 '15 at 14:10
  • Try `.To = Range("B1").Value & ";" & Range("B2").Value & ";" & Range("B3").Value` – tea_pea Jul 09 '15 at 14:14
  • I don't always know what the cell range would be though, i.e. someone might add an email address onto the line, would `Range("B1:B45")` work? – Ben Smith Jul 09 '15 at 16:02