There are a few things you need to do

1) make the OutApp and OutMail objects global and only set them once in the
code.

Public OutApp as variant
Public OutMail as variant

Sub Main()

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

'More of you code

2) Eliminate the .BCC address and make the CC and To address variable


================================
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
..to = SendAddress
..CC = CCAddress
'.BCC = "BlindCarbonCopyAddress"
..Subject = "Your subject here"
..Attachments.Add ActiveWorkbook.FullName
'Attaches active workbook
..Display 'Displays the processed email. You
can change this to .Send
End With
On Error GoTo 0
===============================

3) Next you need a lookup code to get the Validation list converted to the
e-mail addresses. the Data validation list value should be the value of the
selected cell

'Get Department Name from Validation List
DepartmentName = Sheets("Sheet1").Range("E4").value

set HRNames = Sheets("Sheet2").Columns("A")
set ManagersNames = Sheets("Sheet3").Columns("A")

set c = HRNames.Find(what:=DepartmentName,lookin:=xlvalues ,lookat:=xlwhole)
if c is nothing then
msgbox("Could not find Department Name in HR List")
else
'Assume the e-mail address is in Column B
SendAddress = c.offset(rowoffset:=0,columnoffset:=1)
end if

set c = ManagersNames.Find(what:=DepartmentName, _
lookin:=xlvalues,lookat:=xlwhole)
if c is nothing then
msgbox("Could not find Department Name in Manager's List")
else
'Assume the e-mail address is in Column B
CCAddress = c.offset(rowoffset:=0,columnoffset:=1)
end if

"Vinod Lewis" wrote:

Hello All,

I have created a form in excel (leave application). I have a data validation
list, which has departments ie: admin, hr....... and I also have a email
submit button.


I have found one of the code in a previous post. which actually loads the
email envelope with the specified email address in the code.

================================
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.to = "RecipientAddress"
.CC = "CarbonCopyAddress"
.BCC = "BlindCarbonCopyAddress"
.Subject = "Your subject here"
.Attachments.Add ActiveWorkbook.FullName
'Attaches active workbook
.Display 'Displays the processed email. You
can change this to .Send
End With
On Error GoTo 0
===============================

Anybody has any idea how this can be done. Any help is greatly appreciated.

I need a code that when a user clicks the submit button, it should check the
data validation list and send 1 email to the respective manager and 1 to the
HR.

ie: If i select Admin from the drop down validation list. one mail should go
to admin department (whoevers email address is specified) and other one
should go to the HR department, when i click the submit button.

Thanks in advance.