Private Sub CommandButton3_Click()

Public OutApp As Variant (I get an error on this line " Compile error:
Invalid attribute in sub or function)
Public OutMail As Variant

Sub Main()

Dim SendAddress As String
Dim CCAddress As String

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
..to = SendAddress
..CC = CCAddress
..Subject = "Leave application"
..Attachments.Add ActiveWorkbook.FullName
'Attaches active workbook
..send
End With
On Error GoTo 0

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

Set HRNames = Sheets("sheet1").Range("P1:P1")
Set ManagersNames = Sheets("Sheet1").Range("K1:k2")

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:=2)
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:=2)
End If
End Sub

"Joel" wrote:

Q1: The whole code will be in command button click event?

Yes if you want

Q2: How do i declare "TO"and "CC" address as variable.
There are strings (as string)
Dim SendAddress as String
Dim CCAddress as String


Q3:In the below code that range "E4" is the validation list drop down.
Yes . You can change as required

Q4: Should i have Hr names in sheet 2 column "A" and the manager name in
sheet 3 column "A"

They can be any place. I used Columns as an example. It can also be a range

set HRNames = Sheets("Sheet222").Range("A1:A20")
set ManagersNames = Sheets("Sheet333").Range("C20:C50")


Q5: When you mention "assume the email address in column B" does that mean
(what i have understood by Q4) that the corresponding column should have the
email address.

You need a table with one column will be the look up value and the next
being the e-mail address or any fixed number of column away from the lookup
name. You can have 3 columns. The first being the lookup name, the 2nd
being the Manager name and the 3rd being the HR e-mail address

if c is nothing then
msgbox("Could not find Department Name in HR")
else
'Assume the e-mail address is in Column B
SendAddress = c.offset(rowoffset:=0,columnoffset:=1)
CCAddress = c.offset(rowoffset:=0,columnoffset:=2)
end if


"Vinod Lewis" wrote:

Thank you very much joel for helping me. I have few questions and i hope you
answer. I dont know VBA, but i am trying to learn. There are around 6
questions, which i have marked as Q1 to Q6

Q1: The whole code will be in command button click event?

Public OutApp as variant
Public OutMail as variant

Sub Main()

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

Q2: How do i declare "TO"and "CC" address as variable.

Q3:In the below code that range "E4" is the validation list drop down.

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

Q4: Should i have Hr names in sheet 2 column "A" and the manager name in
sheet 3 column "A"

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

Q5: when you mention "assume the email address in column B" does that mean
(what i have understood by Q4) that the corresponding column should have the
email address.

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

Q6: what does "SET C" mean
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


"Joel" wrote:

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.