Open new message and populate To: Box in outlook from excel

[grichey]

I'm sure there is a thread that has this but my searching hasn't turned up what I'm looking for. I'm not trying to mail the work book or any other files. I just want to copy all the emails out of a column and paste them into the To: blank on a new outlook message.

Thanks

[geekgirlau]

Needs error checking to be added, but essentially this will do what you're asking

 
Sub ShowEmail()
    Dim OL As Outlook.Application ' Need a reference to the Outlook object library
    Dim OLMail As Outlook.MailItem
    Dim rng As Range
    Dim strTo As String
    Set OL = CreateObject("Outlook.Application")
    ' fill the list of addressees from a range
    ' there will be a limit at which Outlook is going to view this as spam!!!!
    For Each rng In shLookup.Range("lkpEmailList")
        ' separate by semi-colon
        strTo = strTo & ";" & rng.Formula
    Next rng
    ' remove trailing semi-colon
    strTo = Left(strTo, Len(strTo) - 1)
    Set OLMail = OL.CreateItem(olMailItem)
    With OLMail
        .To = strTo
        .Display
    End With
    Set OLMail = Nothing
    Set OL = Nothing
End Sub