VBA code

[Vinod Lewis]

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.

 

[Joel]

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]

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]

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]

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("P11")
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]

I fixed a few problems. Public is a variable declarattion you would use
outside a subroutine. You had it inside. I originally thought you may have
more than one subroutine where you would of needed the variable shared
between routines. Since you have one routing just declar the variable as DIM.

I commented out the ON Error statement because if you did have an error it
would of masked the real error and made it harder for you to get working.

Private Sub CommandButton3_Click()

Dim OutApp As Variant
Dim OutMail As Variant
Dim SendAddress As String
Dim CCAddress As String

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
'On Error Resume Next - This will mask real errors making it harder to
get working

On Error GoTo 0 'this resume normal error checking

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

Set HRNames = Sheets("sheet1").Range("P11")
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 """ & DepartmentName & """ in HR
List")
Else
'Assume the e-mail address is in Column B
SendAddress = c.Offset(rowoffset:=0, columnoffset:=2)
MsgBox ("Send Address : " & SendAddress)
End If

Set c = ManagersNames.Find(what:=DepartmentName, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox ("Could not find Department Name """ & DepartmentName & """ in
Manager's List")
Else
'Assume the e-mail address is in Column B
CCAddress = c.Offset(rowoffset:=0, columnoffset:=2)
MsgBox ("CC Address : " & CCAddress)
End If

With OutMail
..to = SendAddress
..CC = CCAddress
..Subject = "Leave application"
..Attachments.Add ActiveWorkbook.FullName
'Attaches active workbook
..send
End With
End Sub

 

[Vinod Lewis]

Perfect, you are a genius.

It worked perfectly on test email addresses. I will be testing this with
corporate email addresses.

Thank you very much for your prompt assistance.

one last question: I am still wondering what is "Set c" in that code??

 

[Joel]

SET C is the return cell from the find method. If the item is not found
Nothing willbe the value of C.

 

[Vinod Lewis]

Thank you very much for all your assistance. You have been a great help.

Keep helping and keep rocking