Outlook - emails changed based on cell value

[Rbveiga]

Hello,

Could you please help me?

I would like to delete sheet where it contain all the emails which should be use in my VBA.
To do it, I wrote all emails inside of VBA.

Example:

Sub email ()
Dim OutApp As Object
Dim OutMail As Object




To_Austria = "xxxx@gmail.com";"yyyyy@gmail.com;"
CC_Austria= "xxxx@gmail.com";"yyyyy@gmail.com;"
To_Belgium = "xxxx@gmail.com";"yyyyy@gmail.com;"
CC_Belgium= "xxxx@gmail.com";"yyyyy@gmail.com;"


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

On Error Resume Next
With OutMail


.To = "To_" & Sheet8.Cells(4, 3)
.CC = "CC_" & Sheet8.Cells(4, 3)
.BCC = ""
.Subject =""
.Body =

.Attachments.Add ActiveWorkbook.FullName
.Display 'or use .Display

End With
On Error GoTo 0


Set OutMail = Nothing
Set OutApp = Nothing
End Sub



The sheet Sheet8.Cells(4, 3) is the country which appear in the file and it can be changed manually.
When I run the macro, outlook show the text "To_Austria" and "CC_Austria" instead of emails. How could I make the VBA to take email?

regards,

 

[mrshl9898]

Here's an option. Though i'd just put the emails in those cells instead of "To_Austria" etc.

Sub email()
Dim OutApp As Object
Dim OutMail As Object
Dim mycountry As String
Dim myto as string
Dim mycc as string

mycountry = Sheet8.Cells(4, 3)


If mycountry = "To_Austria" Then
myto = "xxxx@gmail.com;yyyyy@gmail.com;"
mycc = "xxxx@gmail.com;yyyyy@gmail.com;"
ElseIf mycountry = "To_Belgium" Then
myto = "xxxx@gmail.com;yyyyy@gmail.com;"
mycc = "xxxx@gmail.com;yyyyy@gmail.com;"
End If


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


On Error Resume Next
With OutMail




.To = myto
.CC = mycc
.BCC = ""
.Subject = ""
.Body =


.Attachments.Add ActiveWorkbook.FullName
.Display 'or use .Display


End With
On Error GoTo 0




Set OutMail = Nothing
Set OutApp = Nothing
End Sub