VBA outlook - retrieve email address from excel as recipient

0
votes

I would like to retrieve the email addresses from excel cells and copy them as recipients on outlook.

However, the "To" and "CC" on outlook are empty.

input and output:

Cell A1 is the email address which I want to "send to".

Cell A2 is the email address which I want to "CC to".

my VBA code: 

Sub Button1_Click()

    Dim OutApp As Object
    Dim OutMail As Object




    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(olMailItem)



    On Error Resume Next
    With OutMail
        .To = Cells("A1")
        .CC = Cells("A2")
        .BCC = ""
        .Subject = "This is the Subject line"

    End With
    On Error GoTo 0


    Set OutMail = Nothing
    Set OutApp = Nothing

End Sub
3
excelvbaoutlook
When you execute a macro, does active sheet is sheet containing data? – Maciej Los

3 Answers

0
votes

You need to add a recipient, not the To, CC or BCC properties. These properties contain the display names only. The Recipients collection should be used to modify this property. For example:

Sub CreateStatusReportToBoss()  
   Dim myItem As Outlook.MailItem  
   Dim myRecipient As Outlook.Recipient 
   Set myItem = Application.CreateItem(olMailItem)  
   Set myRecipient = myItem.Recipients.Add("Dan Wilson")  
   myItem.Subject = "Status Report"  
   myItem.Display  
End Sub

You may find the following articles helpful:

1
votes

If you remove "On Error Resume Next" you can debug. The following are invalid:

.To = Cells("A1")
.CC = Cells("A2")

Try

.To = Range("A1")
.CC = Range("A2")
0
votes

I have had better luck with a Recipient:

'If not defined:
'olBCC=3
'olCC=2
'olTo=1

Set OutMail = Application.CreateItem(olMailItem) 
Set myRecipient = OutMail.Recipients.Add(Range("A1"))
'myRecipient.Type = olTo
'This is default - use for clarity if desired
Set myRecipient = OutMail.Recipients.Add(Range("A2")) 
myRecipient.Type = olCC

If you wish to add multiple recipients, you will have to add them one at a time