Solved: 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 :banghead: 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